Stairway to XML: Level 1 - Introduction to XML

Rob Sheldon from SQLServerCentral.com

In this level, Rob Sheldon explains what XML is, and describes the components of an XML document, Elements and Attributes. He explains the basics of tags, entity references, enclosed text, comments and declarations More »

SQL Server Rounding Functions - Round, Ceiling and Floor

Additional Articles from MSSQLTips.com

I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options. More »

From the SQLServerCentral Blogs - A Rickety Stairway to SQL Server Data Mining, Algorithm 1: Not-So-Naïve Bayes

SQLServerSteve from SQLServerCentral.com

            Thankfully, the simplest of SQL Server’s algorithms is not quite as naïve about data mining as I am.             As mentioned... More »

There's a lot of hype around "big data", a term being thrown around so much in the media that I'm not really sure what it means anymore. Is 1TB "big data"? Is 10,000 transactions/sec big data? Or does it mean that you have more data than your systems can handle, causing queries and reports to run slow?

I almost hope it's the latter. I hope that our managers start to think that when our systems run slower that we're dealing with big data, and we need more resources. The whole big data phenomenon could be a way for data professionals to start a new hardware renaissance, where hardware budgets grow and we begin to replace our current systems with bigger, faster servers.

Or perhaps it's a way to offload some of the system administration for individual servers and move to cloud services. I don't think that's necessarily a bad move for many DBAs as it would allow them to focus on data management, and information extraction rather than dealing with storage and hardware management. Those infrastructure jobs will not be the ones you want in the future.

Big Data is in the news, and it's being used by vendors to sell new products and services. From Hadoop to new SANs to BI interfaces, there's no shortage of places where the term "Big Data" might be used to try and shorten the sales cycle. As a data professional, it's important that you understand what your needs really are, and if the term is being in a way that actually provides some value to your company for the money spent. If it is, then use it to upgrade your systems. If it's not, point that out to your boss.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast - 16.2MB WMV
• Watch the iPod Video Podcast - 15.1MB MP4
• Listen to the MP3 Audio Podcast - 3.1MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

We created three tables and some values for the columns

CREATE TABLE TT1 
( ID INT
, Name1 VARCHAR(20)
, Name2 VARCHAR(20)
, Name3 VARCHAR(20)
)
CREATE TABLE TT2 
( ID INT
, Name2 VARCHAR(20)
, Name3 VARCHAR(20)
)
CREATE TABLE TT3 
( ID INT
, Name3 VARCHAR(20)
)

INSERT TT1
VALUES ( 1, 'A', 'B', 'C'),
( 3, 'AA', 'BB', 'CC'),
( 4, 'AAA', 'BBB', 'CCC'),
( 6, 'AAAA', 'BBBB', 'CCCC'),
( 7, 'AAAAA', 'BBBBB', 'CCCCC')
INSERT TT2
VALUES ( 1, 'B','C' ),
( 2, 'BB' ,'CC'),
( 3, 'BBB' ,'CCC'),
( 6, 'BBBB' ,'CCCC'),
( 8, 'BBBBB' ,'CCCCC')
INSERT TT3
VALUES ( 1, 'C' ),
( 3, 'CC'),
( 5, 'CCC'),
( 6, 'CCCC'),
( 8, 'CCCCC')

Then we use following SQL query for the results,

SELECT TT2.ID AS ID1
     , Name1+TT2.Name2 AS N1
     , t1.Name2 AS N2
     , t3.Name3 AS N3
     , COUNT(DISTINCT TT2.ID)
 FROM TT3 t3
  LEFT JOIN TT2 
    ON t3.ID = TT2.ID
  cross JOIN TT1 t1  
 WHERE TT2.ID > 7
 GROUP BY TT2.ID
     , Name1 + TT2.Name2
     , t1.Name2, t3.Name3
 ORDER BY Name1 + TT2.Name2

Does the query run successfully?

By Gary Chinn

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: T-SQL. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Here are some declarations:

set nocount on
declare @i int, @n numeric(10,4), @d datetime, @v varchar(12);

These declarations are followed by a single select statement in the same batch; the declarations work, but the select statement fails.  Which of the following statements could be the failing select statement? (choose 3)

By Tom Thomson

Answer:

• select coalesce(NULL,@d,'34');
• select coalesce(@v,'37.1',@i);
• select coalesce(@d,'37.3',@v);

Explanation: Coalesce always delivers a result with the type which has the highest precedence (ie the lowest precedence number) out of all of its arguments, so it has to convert its first non-null argument (if any) to that type. The order of preference for the types involved in this question is datetime, numeric, int, string and this determines what type each statement will try to convert to.

The string value '34' can't be converted to a datetime, and neither can the string value '37.3', so the third and ninth statements of those listed fail, and so does the seventh since the string value '37.1' can't be converted to int. All the others work, because the required conversions are possible: for example the numeric value 37.2 can be converted to an int (37), and the numeric value 37.4 can be converted to a datetime (24 minutes and 3 milliseconds to 10 on 27th Feb 1900).

References: Coalesce (Transact-SQL)
Data Type Precedence (Transact-SQL)

» Discuss this question and answer on the forums

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

Defragmenting and rebuilding indexes

Cool from SQLServerCentral.com

Following code snippet will selectively rebuild indexes that have been thrashed after a big indert More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Log size is Big - i am new in DBA. If in a database MDF file Size is 10 GB and LDF file size is 50...

1 sp out of 4 not executing even though user has execute permissions on all 4 - We have permissions set up so users have permissions only on stored procs. I have a role set up with...

create report with date suffix - I need to create a report & run weekly to output to file on server with filename_date×uffix.txt. The original report (which is...

Error 26073 - Hello, We are also getting similar error on our SQL server in cluster environment Error 26073 TCP connection closed but a child process...

My MSDB Database is over 25 GB on production server - The following tables are consuming alot of space in the msdb database. LakeSideLockLogger._LakeSide_DbTools_LockLog-------------------------14.5 GIG LakeSideLockLogger._LakeSide_DbTools_LockExecStack-------------------7.5 GIG LakeSideLockLogger._LakeSide_DbTools_DeadlockLog---------------------0.4 GIG LakeSideWaitsLogger._LakeSide_DbTools_WaitsLogger_WaitsDBLog--------0.3 GIG Please hel

Need some help with an odd issue - I've tried everything i could find so far same thing access denied.. anyone else have this issue before? Error:Cannot connect to...

Error using sp_send_dbmail - Hi everybody. I have a problem I am connecting to Database with user 'rpp' and when execute this code: EXEC...

sql auditing triggers - I have to write DDL trigger in sql 2005, using CLR feature. Can anyone provides help in this regard

SQL Server 2005 : Business Intelligence

HOW run ssis package on table update - Hi i have created ssis package to move data from one table to other in different instances when i update...

Difference between ODS and Datawarehouse - Hi All, Can any one tell the difference between ODS(Operational Data Store) and Datawarehouse? When do we go for ODS and when...

SQL Server 2005 : SQL Server 2005 General Discussion

Retrieving data from other tables, using row data as fields - I have a temp table that I created that has the field name and table name that I want to...

Copy tables from one database to another - Anyone know how I can easily copy selected tables from one database to another? (different servers). In Enterprise Manager it was...

Failed to notify ''operator name'' via email? - Following command sends email fine: EXECUTE msdb.dbo.sp_notify_operator @name=N'operator name',@body=N'test message' However, SQL Job with a notification to above operator doesn't work. Job history...

SQL Server 2005 : SQL Server Express

Database restore - Hello All, I hope and I'm sure to find an answer with all the experts on this forum. Well this is the...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Problem with "If Exists (Select ...) Or Exists (Select ...)" - Does anybody know why the following [code="sql"] If Exists (Select * From Inserted I Join dbo.T_PaymentItemGroup PIG On PIG.PaymentItemGroupID = I.PaymentItemGroupID Join ...) Or Exists (Select * From...

sql server memory - Hi All, I am not sure if this is a fair ask or no but this is something i want to...

When to Index - I have both reporting and operational systems.. So looking at each system.. what becomes a good point for indexes? (see query at...

How to check when was the last Time re-indexing was performed on a DB - hello All, Can someone suggest me a scrpit or a command that can provide me information about last time re-indexing performed...

SQL Server 2005 : SQL Server 2005 Integration Services

ADO.NET Destination Custom Propertiees? - Hi I tried to loading xml into Oracle table using SSIS, OLEDB destination wont support, so I use ADO.Net Destination, ADO.NET Destination...

How to make group of continuous tasks to execute based on single condition - Hello All, Could you please help me out here, i am totally stuck here, i have total 20 tasks in my ssis...

How to know which DTSX file was deployed in a server. - Hi all!!! I inherited an interface process (Oracle text file to SQL Server table) which uses a SSIS 2005 package...

SSIS & Environment Variables - I've been struggling w/ some quirky issues lately regarding use of environment variables with package configurations in my SSIS packages. When...

SQL Server 2005 : SQL Server Newbies

atomic value update? - I have an application that needs to make an atomic update to an item in a table. It has to...

Initialisation file - Hi, I'm totally new to SQL Server *.sql command files. I'd like to create a SQL file that runs the initialisation...

SQL Server 7,2000 : Administration

Anyway to write VB/C#/C++ scripts in SQL 2000? Need financial calculations (IRR, NPV, etc) - I'm working on a SQL 2000 database server (I know .. but it is what it is unfortunately), and I need...

SQL Server 2008 : SQL Server 2008 - General

Interesting query - Hi All First schema: create table tab1 (forumId int Primary key,ParentId int,HitNumber int) insert into tab1 select 3, 9, 119 UNION ALL select 4, NULL, 88 UNION ALL select 5, NULL, 72...

Why in recovery for so long if database is in SIMPLE mode - Hi. I have a database thats just undergone a very large insert into a table. The instance was restarted and now...

TempDB question - Hi all, In failover cluster environment, If automatic failover happened, whether sql server will create fresh tempdb or not?

SSMS is hanging - we have a requirement to configure Linked server by using oracle provider . when we tried to configure linked server by...

SQL Trace - How to interpret the Reads column - Hi, I'm playing around with SQL traces, and trying to understand what exactly it's returning. For now, I'm looking at the Reads...

How to display column_names based on condition - Hi Sql Server Team, HAPPY NEW YEAR. -- Am having below table [b]Table1[/b] Col_1 Col2 Col3 ---------------------------------------- 100 Prod Ext 101 Sales Exd 102 Mark Tet 103 Purc Ket i want to query to display the column_names Eg_1: if table1.col_1 contains 100...

sql query writing12 - hi frieds i have small doubt in sql server plese tell me how to solve this table data contains like id...

Trigger Help - Hi, I want to create trigger when i fore below code it should keep track of it.. SP_CONFIGURE 'SHOW ADVANCED OPTION',1 RECONFIGURE go SP_CONFIGURE...

Serach for a particular string in different columns in a table - Hi, is there a simpler way to search for a partcular string in different columns in a table. currently I have...

How to extract the paricular values from a string - Hi, We have the below file names File Name1 = 'XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.TXT' File Name2 = 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT [b]Query:[/b] Need to extract the file names as below File Name1 = XX_YYYYY_AA_BBB_SampleTransaction File...

understanding client statistics information in sql 2008 - I just want to know how exactly client statistics works and as DBA for query performance what parameter has to...

SqlDateTime overflow - I receive this error when inserting in the database. I know my dates are good. SqlDateTime overflow. Must be between...

MCTS Certificate for SQL Server 2008 - Hi all, I have a plan to get MCTS certificate for SQL Server 2008. As I knew, I must pass 3...

Odd table sizes - I have a table, I'll call it "X", in a database which is replicated to another server via Merge replication....

=IIF(Fields!urgency_level.Value = 2 and Fields!DateDifference.Value <=2, Fields!NeedUrgentDates, 1) Errors, but does not give me an error code - =IIF(Fields!urgency_level.Value = 2 and Fields!DateDifference.Value <=2, Fields!NeedUrgentDates, 1) gives me "Error" on output, but there are no error codes. The syntax checks...

Adjacency list / linked list / hierarchical data SELECT - I'm working on a SELECT statement to get all employees that are under the CEO Al Knowing. Ultimately these tables...

Date Comparisons - Other than issues with sorting and MAX, are there any potential issues with using something like CONVERT([varchar](10), datetimefield, 101) which...

Index usage and RID lookup. - Hello All, An index with three fields, A,B,C ( A selection with SELECT * FROM A_TABLE WHERE A = 6 AND C =7 performance question: [b]Does this...

Generate Months from unique records with different date ranges - Hello I have a database that records contracts and their amounts (each contract has a start and end date); each contract...

NULL VALUES - My client "My 1st" has a sql database with many, many null values in each table. Its because they do...

I NEED HELP WITH SQL EXPRESS 2008 R2 - I am a newbie just been doing this for about 3 mo's. I installed sql and all went well. During setup...

Restoring a database a Server using a Remote Server as the Service call - My coworker are brainstorming some ideas because our network is divided geographically. One question we are pondering is if you...

Table Variable Parameters - Odd design choices or am I just not finding the answer - So, I'm currently looking over some 2k5 to 2k8 upgrades we're doing (yeah, I know) and I'm reviewing using table...

weird memory usage on sql server - Hi Folks, I got this weird issue on sql server. I am running window 2008 r2 enterprise 64 bit with 8 gb...

SQL Server 2008R2 'Web Edition' License Query - Hi All I have what seemingly is a pretty basic query however dont seem to beable to get a definative answer...

SQL Server Collation (ASCII Table sort and Case Insensitive/Accent Insensitive) - Hi everybody :-), I have an issue about SQL Server Collations. Our owned programmed database use Case-Insensitive/Accent Insensitive data ordered like...

SQL Server 2008 : T-SQL (SS2K8)

Why no Index Scan? - Hi All! Hoping someone can help us understand this. Given; [code="sql"] USE tempdb GO IF OBJECT_ID('tempdb.dbo.Covered') IS NOT NULL DROP TABLE Covered IF OBJECT_ID('tempdb.dbo.Included') IS NOT NULL...

Need to find out whether date is of which data type - Suppose I have a variable which contains date value in a string format. eg: declare @value1 varchar(50) = '2013-01-08 15:44:12.2081606 +05:30' Now my...

Deadlock In SQL - Hi Guys, Need urget help Please. I am running SP on SSMS everytime i run that SP getting Error "Msg...

Tally Calendars and 'Week 1' - Hey guys, I know there's been a few discussions on here about tally calendars and their use, I've got one on...

help me remove key lookups from the query below - Hi, I have a stupid written query. I saw that 99% of the cost is going into key look ups... i...

top 10 product in each month each year - hi i have two tables table A main_table id fill_date 1 09/04/2003 2 12/31/2005 3 01/05/1985 product id name 1 oxygen 2 detox 3 carbo what i want is top 10 product...

Puzzle: vertex covers in SQL - I have to do a book chapter on graph database. I already did “The Kevin Bacon” problem in SQL for...

Using Results from one column to calculate another column - Doing this in SQL 2008. Is it possible to use the results of one column to calculate another column? Example: Select .. VERY...

return zero for months where no data exsits - Hi, I have a table (Users) with rows containing data about registered user, Table Columns: UserId, RegisteredDate I want to know how many...

Connecting to multiple sql servers - Hi, I'd like to connect to multiple databases on different servers all from the same query. So, is it possible to...

SQL Server 2008 : Working with Oracle

Oracle 9i to sql server 2008 migration - I want to migrate from Oracle 9i to Sql server 2008 using SSMA. Please let me know what steps will...

SQL transactional replication to Oracle 11g server taking slower than normal - Hi, I have a push transactional replication set up that pushes to a Oracle 11g db. What used to take...

SQL Server 2008 : SQL Server Newbies

Update statics - Hello Masters, What exactly "Update static" is ? Is it just removing fragmentation from database that is created by DML queries? Is...

DBCC UPDATEUSAGE - DBCC CheckDB job failed last night. This is SQL Server 2005 Enterprise Edition 64 bit environment. The message / solution indicated...

Decimal separator missed when pasting into excel from sql query - I have an sql query which returns some numeric values that sql server is representing with a decimal point. Since...

Cluster on SQL Server 2008 R2 - Hello Masters, I have clusters on sql server 2008R2, I am learner for clustering on sql. So can you pls guide...

Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34...

SQL Server 2008 : Security (SS2K8)

Users, Groups, and Schemas - Hi guys, I've got the following problem that I need help with: Our team can be roughly divided into 2 groups: Group 1:...

SQL Server 2008 : SQL Server 2008 High Availability

LOG SHIPPING by two different systemt architecture? - Hi, Can we able to configure for LOG SHIPPING by two different systemt architecture? source server ------------- System type – windows 2008 R2 enterprise edition...

Changing SSAS instance from stand-alone to cluster - Hello, I have a 2 node Active/Passive cluster mounted in Windows Server 2008 R2. Currently on the active node there's...

Vertical Partitioning with transactional replication - I have a publisher and a subscriber database under transactional replication. The schemas are identical. One of the tables has...

SQL Server 2008 : SQL Server 2008 Administration

Reasonable Max Memory Usage? - Hey Everyone :-), I wanted to try setting the max memory usage on my test environment as currently sqlservr.exe is using...

VL Read Only DB - Hi experts, We have a 4.3 TB database ,can i restore it to another server which is having only 4.3 TB...

SQL Server service is not stopping - There was a bottleneck in a server. In order to avoid that I have changed some configuration changes in the Server...

Login with SA acct failed - Hi All, Here is a scenario that I am going through and need your opinion on this. One of the app guy...

SQL Agent Job - T-SQL Job Step Question - I am sure I read somewhere that when executing other jobs from the steps of one job, that the main...

Full Backup stopped prior to completion - Hello -- The scheduled Full Backup of one of our databases failed due to a stop job request. The log file...

SSRS 2008 - How to handle subscriptions on 2 instances of SSRS on a cluster? - Hi all, I ran into an interesting question from a developer on how to handle subscriptions on 2 instances of...

Big DB with lots of free space. Can I shrink? - I'm a new hire (developer....there are no DBAs) on a project whose database occupies 370GB on a drive with 1GB...

BackupExec SQL Agent or SQL Server Native backups???? - I'm looking after a whole bunch of SQL Servers, both 2005 and 2008, some full and some simple recovery databases...

adding space to tempdb - In order to avoid any failure please add some space to tempDB database on xyz server. I am a new...

Partition including refernce tables and multiple keys - Hi All, We are planning to partition our table which have many reference tables. Is it possible to have multiple keys...

SQL Server 2008 R2 TEMPDB massive autogrowth suddenly. - Today while i was at lunch, TEMPDB had massive autogrows until it filled the disk it sits on. When i...

Database Mail e-mail notifications not being sent on completion of backup jobs - Hello -- I configured our backup jobs to send notifications to the administrators on their respective completion. However, the notifications have...

Programming : General

Big Data - I am hearing this term "big term" very loud these days. Everybody is talking about it. Does any one know...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Need help in giving the expression for background color for a column - Hi All, I have a column 'RunTime' with following format of data in it. I need to give an expression...

SQL Report with multiple matrix - I have a matrix reporting data by week by location. The matrix works great for this. The challenge, however, is...

Help needed to trobleshoot opening Report manager Url - hi All, Some one deleted the internal folders of the 'C:\Users\' and I'm facing issues opening the Url. I'm getting the...

Reporting Services : Reporting Services 2005 Administration

access to report server url - Using SQL SERVER 2008r2 Reporting Services. I accessed my report server url using my admin credentials. I used New Role...

Database Design : Design Ideas and Questions

SEQUENCE Problems - Hello. I've started using a SEQUENCE in a table instead of an identity. I seem to be experiencing problems of the sequence...

Data Warehousing : Integration Services

How to get the data in destination database immediately when source table updates? - We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination...