A community of more than 1,500,000 database professionals and growing

Stairway to T-SQL DML Level 8: Using the ROLLUP, CUBE and GROUPING SET operator in a GROUP BY Clause

Gregory Larsen

In this article I will be expanding on my discussion of the GROUP BY clause by exploring the ROLLUP, CUBE and GROUPING SETS operators. These additional GROUP BY operators make it is easy to have SQL Server create subtotals, grand totals, a superset of subtotals, as well as multiple aggregate groupings in a single SELECT statement. More »

Row Number Transformation

Additional Articles from sqlis.com

The Row Number Transformation calculates a row number for each row, and adds this as a new output column to the data flow. The column number is a sequential number, based on a seed value. Each row receives the next number in the sequence, based on the defined increment value. More »

SQLSaturday #160 - Kalamazoo

Press Release from SQLServerCentral.com

SQL Saturday comes back to Michigan. Come see Jeff Moden and others talk SQL Server on Sept 22, 2012. More »

From the SQLServerCentral Blogs - Running in Central Park at SQL in the City New York

Steve Jones from SQLServerCentral.com

I just booked my tickets for New York City. I’m coming into the city on Sept 27, at 2pm, with... More »

I was reading in detail about the Apple / Amazon hack that targeted a Gizmodo writer.  A hacker used a few techniques to get from his Amazon account to his Apple account, his GMail, Twitter, and more. His iPhone, iPad, and Macbook were remotely wiped, and he ended up losing quite a bit of data that wasn't backed up. That's a horrible situation, and I know every time I've lost a picture or document at home, I regret not making another backup.

The situation caused some discussion and comments on Twitter about the various things you should do to prevent this type of issue. Updating your security, choosing better passwords, and other ideas are great, but the number one thing you need to do is make a backup of your data. No matter what happens to your security or even hardware, if you have a second copy of your data somewhere else, you can recover from the situation.

While listening to a Brent Ozar, PLF webinar recently, I heard this interview question: what is the first thing you do on a new server you've never worked with? The answer is ensure it's being backed up. Not run a backup, since you can cause problems, but make sure there is a backup plan in place, or get one started as soon as possible. Why? Because if you have a backup, you can recover.

I think this is the number one priority for any data professional, and perhaps for anyone using a computer. I know I have backups of my home machines run to a Windows Home Server. I take a backup of my Macbook regularly, and leave a copy at home when I'm traveling. I also carry backups of my VMs on separate disks, in a separate suitcase when I travel. I know that if I have one of those copies of my data, I can recover from almost anything.

What's the saddest part of this story? There wasn't any intent (allegedly) to target the writer's work or his data. The hacked just wanted to use his cool Twitter account, @mat.

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 - 22.4MB WMV
• Watch the iPod Video Podcast - 20.6MB MP4
• Listen to the MP3 Audio Podcast - 4.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:

How can you check the SPN registration on the server?

By Naseer Ahmad

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

This question is worth 1 point in this category: Administration. 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.

Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports

This is the industry’s most comprehensive and useful guide to SQL Server 2008 and 2008 R2. It presents start-to-finish coverage of SQL Server’s core database server and management capabilities, plus complete introductions to Integration, Reporting, and Analysis Services, application development, and much more. Grab your copy today from Amazon!

What will be the query result?

select '130'+'120'+1

By Yousaf Khan

Answer: 130121

Explanation: In this query the first plus sign between two strings indicates the concatenation and the second plus sign between second string and digit 1 indicates the addition the string will converted to digit at the time of addition.

\ Ref: + (concatenation) - http://msdn.microsoft.com/en-us/library/ms177561
+ (add) - http://msdn.microsoft.com/en-us/library/ms178565

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.

Calculate Easter Date

DASUN from SQLServerCentral.com

A function to calculate the date for Easter Sunday for a given year More »

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

SQL Server 2005 : Administering

How to stop other application accessing SQL port 1433 - We are planning to do some maintanance on SS 2008 and we DO NOT want any users / application accessing the...

Upgrading SQL Cluster from 208 SP2 to SP3 ( ACt/passive ) - Hello , Can someody point me to the document for upgrading to SS 2008 SP2 to SP3 on a Active Passive...

Transaction replication with mirroring - Hello friends I need some help and I am sure ...i will find some here . We have got SQL 2K8...

.mdf" failed with the operating system error 2 - Hi, I'm having a problem when creating a new database to a new file system location. The original script that...

SSIS FTP writes big temp files to profile folder on C - My post below was not getting any bites in the SSIS forum so I'll try it here. Thanks for any...

OS 64 Bit, SQL 32 Bit - Hi All, i have a scenario where in the Operating System is Windows 2003 R2 Datacenter X64 bit edition service pack...

Have to enter the port to connect via management studio - A strange problem has cropped up in our enviornment that I'm kind of scratching my head about. Why all of...

No blocking, queries running slowly, what to check next? - Background: Database developer trying to fill in for an actual dba on a contract. Environment: Web app with very poor data...

DTS password problem - Hi, everyone We have a problem with one of our DTS package. It was created some years ago, and it developer had...

SQL Server 2005 : Backups

Insane sized tran log backups are filling up the disk. - I have a particular DB on a SS2005 Enterprise. It is scheduled to be backed up in FULL every day...

backup file size - Can anybody help in this matter Original database size [b]100 MB[/b] Backup files created, in scheduled time but size was [b]357...

SQL Server 2005 : Business Intelligence

Cube Processing - Hi, I am new to SSAS.i have 1 lakhs record in cube but next day i am getting another 10k...

SQL Server 2005 : CLR Integration and Programming.

Problem migrating CLR Assembly from 32-bit to 64-bit environment SQL 2005/OLEDB/VFP - Hi, I am currently in the process of migrating our database server (which is Windows Server 2003 32-Bit, SQL2005) over to...

SQL Server 2005 : Development

How to get a row number which is failing during conversion - I have about 20000 records in the SELECT clause and one of them is failing during conversion spitting out the...

SQL Server 2005 : Working with Oracle

Refresh from Oracle - Hi friends, We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We...

SQL Server 2005 : SQL Server 2005 General Discussion

sql server 2005 setup related error - i uninstalled sql server 2005 while reinstalling getting this error TITLE: Microsoft SQL Server Setup ------------------------------ None of the selected features can...

SQL Server 2005 Agent will not start - The agent service is configured to use a service account, as is SQL server itself. SQL starts just fine so...

query between 2 servers - Hi everyone! I'm self-taught in SQL, so my knowledge is fragmented, but I'm not a complete novice. I'm migrating an Access db...

SQL Server Tools - Recomendations On Stress Testing and I/O Testing On New SQL Server - Need to test/stress a new SQL Server box to see what she can do. I went to get a copy...

how to change source of an ssis package - hi der, i have a ssis package which imports data from a mdb file to SQL Table. Is der any way to...

SQL Server 2005 : SQL Server 2005 Security

Scripting out grants for 'sa', 'securityadmin' type rights - I am in the process of moving a database from SQL 2005 to 2008 and it has over 1,000 userids....

SQL Server 2005 : SS2K5 Replication

Best one-way replication option going forward - I'm looking for some insight into implementing replication with a SQL 2005 Standard database based on our needs as well...

Error trying to alter procedure on replication publisher - I have two SQL Server 2005 Standard Edition servers in my development environment that are configured for both Merge and...

Replication Error - This is making me nuts! Here's an error I get when trying to synchronize that I can't seem to get...

SQL Server 2005 : SQL Server Express

SP4 on Express - Hello Friends, I want to update sp4 on SQLExpress 2005 edition. Do I need to reinstall the SQL server Express...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Materialized Views - One of my query is using 4-5 tables using Inner or Left Outer joins. These tables are the main tables of...

SQL Server 2005 : SQL Server 2005 Integration Services

Single config file for multiple SSIS packages - Hi, is there a way we can use a single configuration file and deploy several SSIS packages. I have a...

SSIS using SQl cmd???? - Hi, For creating a SSIS package I am using “SQL Server Business Intelligence Development Studio” that provides me a facility to...

SQL Server 2005 : T-SQL (SS2K5)

String concatenation - Hi I have the following table [code="sql"] CREATE TABLE #Test (ID int NOT NULL IDENTITY(1,1) Primary key, TranID int NOT NULL, OriginCode varchar(5) NOT...

Convert DateDiff into Hours, Minutes, Seconds, Milliseconds - I have the following T-SQL code to change the difference between datetime columns into hours, minutes, and seconds CONVERT(varchar(6), DATEDIFF(second, b.DateTimeStamp,...

SQL Server 2005 : SQL Server Newbies

Generate a checkletter (code) - I'm trying to translate a stored procedure written in SQL to do the same in VBA-Access. Code is attached. Although...

SQL Server 7,2000 : Administration

Configure Mail in sql server 2000 - Hi, We are using Sql server 2000 in our production server, There so many job are scheduled in that,I want mail of...

Not able to Uninstall - Hi, I am trying to uninstall MS SQL SERVER 2000 on windows 2000 machine, but its not allowing to uninstall and...

Find expensive Queries in sql server 2000 - Hi, I am working as jr dba. i want to find out the expensive queries with execution time in sql server...

How to find initial size of the database file and log file in sql server? - For SQL Server 2000, i want to find the initial size of the database file and the transaction log file....

SQL Server 7,2000 : General

Restore backup to earlier SQL2K build - Is there any reason I could not take a backup from a SQL2K 8.0.2065 server and restore it to an...

SQL Server 7,2000 : SQL Server Newbies

How to run.bat file via SSIS package - I like to run the .bat file every 1/2 hr via SSIS package. Can you please help me how to...

SQL Server 2008 : SQL Server 2008 - General

BulkImport Problem - I have Unicode Data file which is Fixed Length Following Table Structure with Create Statement. sample Data file and format file i...

Suspect Mode in Sql Server 2008 - What is the cause of database in suspect mode and how to overcome from it?

backup timings - i need to gather info about the backup schedule timings which are scheduled on sunday from the last 3 month...

Perform a count on a unique ID and sum two columns - Hi, Here is some sample Data [code] CREATE TABLE #TESTING (acc_no varchar(20), number INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) ) INSERT INTO #TESTING...

SQL Procedure Performance in different networks - Hi, In my project there are two servers local and central. My central server is in Network1 and local server are...

Connection has been closed by peer. - Hi, I used linked server in a procedure which was working fine for two months, Now i am getting the below...

How to Best store this data - Hello Everyone I am working on one of my own .NET websites that stores data in SQL 2008 database. I would like...

University grad - Dear viewers, As the title suggests, I am a recent graduate with a Masters degree in Business system integrations. I have...

Backup size - Hello, Before I run backup,can I check how big it will be? Thank you

SSIS loop through file names defined in database table and apply File System task to each - Hello, My objective using SSIS is to loop through a SQL Server 2008 database table I created called Files in a...

CONVERT DATA COLUMN TO ROW WTIH COMMA - HI ALL, HOW TO COVERT COLUMN DATA IN TO ROW WITH COMMA SEPARATOR DATA LIKE THAT :- TABLE NAME : - TEST COLUMN NAME : - COL1 ORIGINAL...

Multi Lingual SSRS Reports - Hi All Looking for a bit of guidance on a issue with SSRS. I know SSRS will not natively support translating tablix,...

Determine if a database is read-only when the log is not marked as read-only so DBCC SHRINKFILE may be skipped? - I know the database in question should not be setup this way - one of the Finance guys copied a DB...

Position count excluding Vacant positions in hierarchical data - Hi, I have following table structure, [quote] PositionId ReportToId EmpId IsVacant LevelNo 1 0 123 0 0 2 1 124 0 1 3 1 125 0 1 4 2 126 0 2 5 2 Null 1 2 6 2 128 0 2 7 5 129 0 3 8 5 130 0 3 [/quote] From this I am able to calculate position count meaning number of positions under one position. I...

Clustered Key. (Making it unique). - Dear reader, I remember (from 2000 version): If the clustered key is not unique. A artificial field is added to make it...

Strange Management Studio 2008 R2 (10.50.1600 and 10.50.1617) behaviour - Using management studio (10.50.1600 or 10.50.1617.0) Connecting to named sql server instance (lets call it [DEV-SQL\SqlKing]) version 10.0.4279 --> Cumulative update package...

log for database is not availabe - Hi, I am getting the below error error message. The log for database 'Commun' is not available. Check the event log for...

Trigger to rollback if no PK assigned - I've been tasked to create a trigger that will deny the creation of a table if no PK (Primary Key)...

SQL Server 2008 : T-SQL (SS2K8)

Annoying join problem & frozen brain. - create table A (a int, b int) create table D (a int) insert into A values (1,1) insert into A values (2,1) insert into A...

Will indexed view help this query? - Hi I have a fact table with around 100 million rows. Below is my query to pull top 10 records. ( i...

Cannot get Left JOIN to work correctly - I have 2 tables in my database and am RIGHT JOINING the tables and it is acting like an inner...

Improving query performance to detect first duplicate - I need to query some tables looking for All Sales that are active at the same time and have at...

evaluating and combining values from two rows - I am running into a bit of trouble with the query below, would be great If you could offer some...

Bulk insert slower after upgrade in SQL2008 R2 - We did an inplace upgradeof Production database from sql server 2005 to SQL 2008 R2 with same configured Windows server....

Convert Text to Date DataType - Hello Everyone, I have an sql table which stores dates from some other system as text. Eg.: DD/MM/YYYY ,31/12/2012. I have made...

0 is equal to zero length string. Can someone explain how this can be? - --I know this has to do with an implicit conversion of the varchar to int, --but why does the zero...

SQL Server 2008 : Working with Oracle

Simple pass thru query - Hello Oracle Forum: I've got my first linked server established from SQL Server 2008 SSMS to Oracle. What is the proper syntax...

SQL Server 2008 : SQL Server Newbies

Script doesn't seem to function - Hey guys...The script I have...works If I also have to make a login. When my login on the server already...

Backup failed - Hello Masters, My daily differential backup job failed for user databases. all the user database are in simple recovery model. Below...

Differential backup - Hello Masters, Can I perform Differential backup if my recovery model is Simple ?

The Best SQL Server Tutorials - I want to curate some of the best SQL Server online tutorials as part of my skill inventory. I'm particularly...

SQL query performance question - I am working on a small research team that has been given access to a set of SQL Server 2008...

Calling a Procedure from a Trigger - I have 2 tables (1 parent and a child table) that are updated using one form. The child table is...

SQL Server Training Kits...hardware/software WTF! - I've been working with SQL Server 2005/2008 at a small software company for about six years now, having trained myself...

Need help with this query - I got this table LicenseId ClassificationCode GroupCode HasMultiple 10537 GA02 GA 0 10537 GA03 GA 0 10537 GB98 GB 1 10537 GF08 GF 0 10537 GF09 GF 0 Result set should return me...

SQL Server 2008 : Security (SS2K8)

Sending remote attachments using sp_send_dbmail - Hi, I am using Windows Authentication to send email using sp_send_dbmail. Sending email works if I do not try to add a...

user mapped as dbo - hi, we are trying to drop a login that happens to be mapped/aliased as 'dbo'. it is not the owner of the...

SQL Server 2008 : SQL Server 2008 High Availability

choosing HA SQL 2008 R2 64bit? - Hi, Last week successfully upgraded to SQL 2008 R2 from large size SQL 2000 database (size 648 GB), application working fine...

How do i connect cluster instance?? - Hi All, I am configured SQL Cluster 2008 in Win server 2008, below are the details.. NODE1 IP :- 192.168.10.51 NODE2 IP :- 192.168.10.52 SQL...

object level permissions - I am new learner of sql server dba How to give the object level permissions?

Log shipping throw errors - I have log shipping set up on my database running on SQL Server 2012. It is scheduled to run Monday...

Clustering with Database Mirroring Question...? - I'm looking to setup high availability with Clustering and Database Mirroring on my SQL 2008 R2 Server with log shipping...

Replication OS error 5 - Dear all, I am doing some experiments with SQL server replication. Basically I have the following setup with two sql servers Server1:...

SQL Server 2008 : SQL Server 2008 Administration

This differential backup cannot be restored because the database has not been restored to the correct earlier state. - I restore a Database from a Complete Backups: [code="sql"] RESTORE DATABASE BASSISControlCYP FROM DISK = 'H:\Backups\Complete\BASSISControlCYP_backup_201208170809.bak' WITH REPLACE, NORECOVERY, MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCYP.mdf', MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCYP.ldf' [/code] Processed 640...

Db Snapshots question - Hi, I have a question on database snapshots. I have created a table and inserted 2 rows. create table tab7...

Career : Certification

Number of MCSE Data Platform certified people in the world ? - I am curious to know the number of MCSE Data Platform certified people in the world as of today. There...

Career : Resumes and Job Hunters

Dallas, TX - Senior SQL BI Developer/Architect needed - Full time position with a leading Financial Services firm. Experience in SQL BI tools: SSIS, SSAS and SSRS and some...

Programming : General

Field headers with BCP - I have been using BCP to get an extract from our data for a vendor. They now would like field...

Programming : Powershell

Get-WMIObject Win32_Volume fails on proxy account from Agent Job - It may just be a case of me missing something, but I get unexpected behaviour when running a job as...

SQLServerCentral.com : Anything that is NOT about SQL!

SSD storage performance increase - So, I've updated all my storage for my ESXi server to SSD storage. I fitted it saturday afternoon and moved...

tablet ipad or nexus 7 or kindle fire ? - Hi, I think the ipad has that cool fad factor, but still overpriced. I am interested in buying one but...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

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

dynamic reports at the time of prview reports in ssrs - hi friends i have small doubt in ssrs plz tell me answer i developed one report in ssrs. in that report...

How to Handle a column with Length more than 8000 characters - Hi, I have a report which consists of 40+ columns. one of the column's length is more than 8000 characters. Report is working...

SSRS to multiple PDF based on input parameters - hello, i need to generate multiple pdf reports based on input parameters using ssrs. suppose there are 1000 branches then it...

Data Warehousing : Integration Services

Execute Process Task Arguments - Hi all, I currently am downloading zipped csv files from our source. As part of the process I am to have...

Data Warehousing : Strategies and Ideas

Slow Changing Dimension Question - I think I already know the answer to this question, but I want to double check. We have a Suggested Retail...