Partition Data to Find the Most Recent Record in SQL Server 2005/2008

A user had a requirement where he wanted to find out the most recent OrderDate in a table, grouped by Customers. The data contained information like OrderDate, Price and ProductId purchased by Customers. In order to find the most recently placed order for each Customer, you can partition the data as shown below:

Sample Data


DECLARE @TT table(CustID int, ProductID int, OrderDate datetime, Spending decimal(10,3))


INSERT @TT 


SELECT 1133, 100 , '04/28/2009', 5.03 UNION ALL


SELECT 1431, 103 , '04/28/2009', 19.02 UNION ALL


SELECT 1431, 105 , '04/28/2009', 15.00 UNION ALL


SELECT 1133, 100 , '04/29/2009', 13.40 UNION ALL


SELECT 1142, 105 , '04/29/2008', 14.60 UNION ALL


SELECT 1142, 103 , '04/29/2008', 11.70 UNION ALL


SELECT 1133, 100 , '04/29/2008', 18.60 




Query


-- Find Most Recent Order for each Customer


SELECT Custid, Spending, OrderDate FROM


(


SELECT CustID, Spending, OrderDate, ROW_NUMBER() OVER


(PARTITION by CustID ORDER BY OrderDate) AS custGrp


FROM @TT


) AS Tot


WHERE custGrp = 1




Results


Custid    Spending    OrderDate


1133    18.600    2008-04-29 00:00:00.000


1142    14.600    2008-04-29 00:00:00.000


1431    19.020    2009-04-28 00:00:00.000


How to View Transaction Log in SQL Server

As a DBA, you may need to view the Transaction Log in SQL Server. This information is not typically available to common db users but can be obtained using a simple undocumented command

DBCC log ( dbname, 0|1|2|3|4 )

where

0: minimum information (Default)
1: Returns info available using 0 + flags, tags and the log record length.
2: Returns info available using 1 + object, index, page ID and slot ID.
3: Maximum information about each operation.
4: Maximum information about each operation + hexadecimal dump of the current transaction log row

In order to run this command against a database called 'PictureAlbum' :-


DBCC LOG(PictureAlbum, 3)





OUTPUT:

Create a Maintenance Plan In SQL Server 2008

A Maintenance plan is useful to perform routine database administration tasks like checking database integrity, performing index maintenance, updating database statistics, performing database backup etc.

In order to create a maintenance plan in SQL Server 2008, open SQL Server Management Studio > Object Explorer > Expand Server > Expand Management > Right-click Maintenance Plans and select Maintenance Plan Wizard > In the Maintenance Plan Wizard dialog box, click Next > type a name for the plan in the next window. You can also click on the 'Change' button to set the Job Schedule properties.

Click Next and select the Maintenance tasks that you want to perform



I have selected 'Clean Up History' and 'Back Up Database(Full)' . Click Next. You can set the maintenance task order here. The next screen depends on the maintenance tasks you have selected and are self explanatory. The last screen gives you an option for saving or distributing a report of the maintenance plan actions. The report contains details of the steps executed by the maintenance plan including any error information.

Finally, use the Complete the Wizard page to review the tasks included in the maintenance plan and to create the task.

Check File Size, Space Used and Free Space of a Database using T-SQL

I was looking around for a way to find out the Free Space in a Database using T-SQL. Luckily, I bumped into this very cool script that gives you important info like File Size, Space Used and Free Space of a Database using T-SQL. This script was written by Michael Valentine Jones and I am just sharing it with you (since it's way cool!)


USE Northwind


SELECT


    a.FILEID,


    CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,


    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],


    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],


    a.name as [DATABASENAME],


    a.FILENAME as [FILENAME]


FROM


    dbo.sysfiles a




OUTPUT

When did my SQL Server Service Restart?

If you like to keep a track of when your SQL Server Service Restarted or when the TempDB table was created again, here's these queries for you

SQL Server Service Last Restart Time


SELECT MIN(login_time) FROM master..sysprocesses




When was TempDB last recreated?


SELECT CrDate as TempDBRecreated


FROM sys.sysdatabases


WHERE name = 'tempdb'


Add a Total of All Columns in a Table using SQL Server 2005/2008

Here's a scenario where you have a table with numeric columns. While displaying all the rows, you also want to display the total of each column at the end. How do you do summarize the data? Use WITH ROLLUP

SAMPLE DATA


DECLARE @Numbers TABLE


(


    ID varchar(10), Marks1 smallint, Marks2 smallint, Marks3 smallint, Marks4 smallint


)


 


INSERT @Numbers


SELECT 'S1', 6, 3, 6, 8 UNION ALL


SELECT 'S2', 4, 8, 1, 9 UNION ALL


SELECT 'S3', 2, 6, 3, 5 UNION ALL


SELECT 'S4', 5, 8, 2, 9 UNION ALL


SELECT 'S5', 5, 5, 3, 5




QUERY


SELECT SUM(Marks1) M1, SUM(Marks2) M2, SUM(Marks3) M3, SUM(Marks4) M4


FROM @Numbers


GROUP BY ID


WITH ROLLUP




RESULT


M1    M2    M3    M4


6    3    6    8


4    8    1    9


2    6    3    5


5    8    2    9


5    5    3    5


22   30   15  36


Identifying and Replacing Duplicate Items across rows using SQL Server 2005/2008

A client of mine recently showed me a table that contained a users Registration and Zone information for 3 zones. There was a pattern in the data.

The sample data has been shown below:


-- Create Sample Table


DECLARE @TT table


(


ID int,


RegID int,


ZoneA varchar(5),


ZoneB varchar(5),


ZoneC varchar(5)


)


 


-- Create Sample Data


INSERT INTO @TT VALUES ( 1, 1,   'A0001','B0001','C0001');


INSERT INTO @TT VALUES ( 2, 2,   'A0002','B0002','C0002');


INSERT INTO @TT VALUES ( 3, 3,   'A0003','B0003','C0003');


INSERT INTO @TT VALUES ( 4, 4,   'A0001','B0001','C0001');


INSERT INTO @TT VALUES ( 5, 5,   'A0001','B0001','C0001');


INSERT INTO @TT VALUES ( 6, 6,   'A0006','B0006','C0006');


INSERT INTO @TT VALUES ( 7, 7,   'A0007','B0007','C0007');


INSERT INTO @TT VALUES ( 8, 8,   'A0002','B0002','C0002');


INSERT INTO @TT VALUES ( 9, 9,   'A0009','B0009','C0009');


INSERT INTO @TT VALUES ( 10, 10, 'A0010','B0010','C0010');


INSERT INTO @TT VALUES ( 11, 11, 'A0011','B0011','C0011');


INSERT INTO @TT VALUES ( 12, 12, 'A0001','B0001','C0001');


INSERT INTO @TT VALUES ( 13, 13, 'A0013','B0013','C0013');




Users with Registration ID 1, 2 and 3 had Zone data similar to the following:


1    1    A0001    B0001    C0001


2    2    A0002    B0002    C0002


3    3    A0003    B0003    C0003




So going by the pattern, a user with Registration ID 4 should have Zone data


4    4    A0004    B0004    C0004




However that was not the case, as seen in the sample data. There were a few other rows where data of a different registration id had been entered. The requirement however was not to link the data with the pattern, but to find duplicates of existing rows (zones) and mark them with a special character.

Here's how I identified the duplicate rows using CTE(Common Table Expression) and marked them with a '---'

Query


;WITH Dups


AS


(


SELECT *, ROW_NUMBER() OVER (PARTITION BY ZoneA,ZoneB,ZoneC ORDER BY ID) as ZoneData


        FROM @TT


)


SELECT ID, RegID,


        CASE WHEN ZoneData <> 1 then '--' ELSE ZoneA END AS ZoneA,


        CASE WHEN ZoneData <> 1 then '--' ELSE ZoneB END AS ZoneB,


        CASE WHEN ZoneData <> 1 then '--' ELSE ZoneC END AS ZoneC   


FROM Dups


ORDER BY ID




Output


ID    RegID    ZoneA    ZoneB    ZoneC


1    1    A0001    B0001    C0001


2    2    A0002    B0002    C0002


3    3    A0003    B0003    C0003


4    4    --    --    --


5    5    --    --    --


6    6    A0006    B0006    C0006


7    7    A0007    B0007    C0007


8    8    --    --    --


9    9    A0009    B0009    C0009


10    10    A0010    B0010    C0010


11    11    A0011    B0011    C0011


12    12    --    --    --


13    13    A0013    B0013    C0013


SQL Server 2008 SP1 Released

The Service Pack 1 for SQL Server 2008 is now available for people to download from here. This Service Pack release aims at stabilising the SQL Server product.

For more information about SQL Server 2008 Service Pack 1, please review the Release Notes.

For those having SQL Server 2008 Express Edition - The Service Pack 1 for SQL Server 2008 Express Edition can be downloaded from here

8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable

In my previous post, I had explained 6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb . In this article, we will see some common uses of the
undocumented procedure sp_MSforeachtable of the Master database

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure

Display the size of all tables in a database


USE NORTHWIND


EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"




Display Number of Rows in all Tables in a database


USE YOURDBNAME


EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'




Rebuild all indexes of all tables in a database


USE YOURDBNAME


GO


EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"


GO




Note: DBCC DBREINDEX has been deprecated in SQL 2005. Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead."

Disable all constraints of all tables in a database


USE YOURDBNAME


EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"




Disable all Triggers of all tables in a database


USE YOURDBNAME


EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'




Delete all data from all tables in your database


-- disable referential integrity


EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'


GO


 


EXEC sp_MSForEachTable '


 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1


  DELETE FROM ?


 else


  TRUNCATE TABLE ?


'


GO


 


-- enable referential integrity again


EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'


GO




To RESEED all table to 0, use this script


EXEC sp_MSForEachTable '


IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1


DBCC CHECKIDENT (''?'', RESEED, 0)


'


GO




The two tips shown above have been taken from http://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx and http://www.sqljunkies.com/WebLog/roman/archive/2006/03/08/18620.aspx

Reclaim space from dropped variable-length columns in tables or indexed views


USE YOURDBNAME


EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';




Update Statistics of all Tables in a database


USE YOURDBNAME


EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'


6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

sp_MSforeachdb iterates through each database in a SQL Server instance. Instead of using a cursor, this Undocumented Stored Procedure is of immense help when I need to run a command against all the databases in my local server. Here are some scenarios where the sp_MSforeachdb can be practically used for your day to day tasks:

Print all the database names in a SQL Server Instance


EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'




Print all the tables in all the databases of a SQL Server Instance


EXEC sp_MSforeachdb 'USE ? SELECT DB_NAME() + ''.'' + OBJECT_NAME(object_Id) FROM sys.tables'




The example shown above prints the tables for master, model, msdb and tempdb. If you want to eliminate these databases in the query use, do the following:


EXEC sp_MSforeachdb 'USE ? SELECT OBJECT_NAME(object_Id) FROM sys.tables where DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')'




Display the size of all databases in a SQL Server instance


EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'




Determine all the physical names and attributes(size,growth,usage) of all databases in a SQL Server instance


EXEC sp_MSforeachdb 'USE ? EXEC sp_helpfile;'




Change Owner of all databases to 'sa'


EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa'''




Check the Logical and Physical integrity of all objects in the database


sp_MSforeachdb 'DBCC CHECKDB(?)'




Similarly you can backup all databases at one go or do a CHECKSUM using this useful procedure. There are many more ways to use it. Got a useful tip on sp_MSforeachdb? Share it using the comments below.

How to List the Stored Procedures and Views where your Table is being used

It's quite simple to retrieve the dependencies of your StoredProcedures or Views. Just use the sp_depends stored procedure.

However how do you determine in which Stored Procedure or Views is your table being used. For eg: I wanted to find out which were the Stored Procedures or Views in the Northwind database that used the table 'Employees'.

Here's a quick and dirty way of doing so in SQL Server 2005/2008. It may not be a very reliable query, but it makes your job a lot easier.


SELECT object_name(id) as 'Name', text as 'definition', type_desc


FROM syscomments sc


Inner Join sys.objects so


ON sc.id = so.object_id


WHERE text LIKE '%Employees%'


and so.type IN('P', 'V')




OUTPUT



I mentioned the word 'quick and dirty' since I could not find any other way to solve this requirement. But I am sure there is one! So be nice and share it over here.

Retrieve all Tables in a Database as DatabaseName.Schema.TableName

The general syntax of a CREATE TABLE command in SQL Server 2005/2008 is :


CREATE TABLE


    [ database_name . [ schema_name ] . | schema_name . ] table_name


        ( { <column_definition> | <computed_column_definition> }


        [ <table_constraint> ] [ ,...n ] )


    [ ON { partition_scheme_name ( partition_column_name ) | filegroup


        | "default" } ]


    [ { TEXTIMAGE_ON { filegroup | "default" } ]


[ ; ]




Observe the database_name.[ schema_name ].| schema_name.]table_name syntax. Let us say, if you now want to retrieve all the tables in your SQL Server 2005/2008 database in a similar format, then here's how to do so:


USE Northwind


GO


SELECT DB_NAME() + '.' + OBJECT_NAME(object_Id)


+ '.' + SCHEMA_NAME(schema_id) as 'Fully Qualified Name'


FROM sys.tables




OUTPUT

Select the Highest and Second Highest value in a Group/Category using SQL Server 2005/2008

In one of my previous posts Select the Highest value in a Group/Category using SQL Server 2005/2008, a user asked me if it was possible to find the Highest and the Second Highest Value in a Group/Category. He also wanted to use a Common Table Expression to do so.

Here's the modified query to find the Maximum and Second Maximum value in a Group/Category using a CTE in SQL Server 2005/2008

Sample Data


DECLARE @Student TABLE


(


    StudentId int, SubjectId int, Marks float


)


 


INSERT @Student


SELECT 1,1,8.0 UNION ALL


SELECT 2,1,5.0 UNION ALL


SELECT 3,1,7.0 UNION ALL


SELECT 4,1,9.5 UNION ALL


SELECT 1,2,9.0 UNION ALL


SELECT 2,2,7.0 UNION ALL


SELECT 3,2,4.0 UNION ALL


SELECT 4,2,7.5




Query to fetch the highest and second highest marks in each Subject


;With CTE


AS


(Select Row_number() Over(Partition By SubjectId Order By Marks Desc) as Topp,* From @Student)


Select SubjectId,


  Max(Case When Topp=1 Then Marks End) as '1st Rank',


  Max(Case When Topp=2 Then Marks End) as '2nd Rank'


From CTE


Group By SubjectId




Results


SubjectId    1st Rank    2nd Rank


1              9.5          8


2              9            7.5


RANK() VS DENSE_RANK() With an Example using SQL Server 2005/2008

The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.

The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.

Let us understand this difference with an example and then observe the results while using these two functions:

We will run two queries, one using RANK() and the other using DENSE_RANK() and observe the difference in the results. We will be using the ORDERS table of the NORTHWIND database to demonstrate the difference. The query will fetch the list of Customers ordered by the highest number of orders each has placed.

Using the RANK() function


SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt


FROM (SELECT CustomerID, COUNT(*) AS TotCnt


FROM Orders Group BY CustomerID) AS Cust




OUTPUT



As shown in the results above, while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. For example, Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 6 instead of 5, because there are two rows that are ranked higher at 4.

Using the DENSE_RANK() function


SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt


FROM (SELECT CustomerID, COUNT(*) AS TotCnt


FROM Orders Group BY CustomerID) AS Cust




OUTPUT



As shown in the results above, while using the DENSE_RANK() function, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties. Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 5. This is not the same as the RANK() function where the Customer with the next highest number of orders were ranked number 6.

Well I hope after seeing these example, you will understand the difference between the RANK() and DENSE_RANK() and will know where to use what.

Select the Highest value in a Group/Category using SQL Server 2005/2008

In one of my previous posts, I had explained how to SELECT TOP N Rows Per Group/Category

In this query, I will show you how to fetch the highest value in a Group or Category. We will be creating a sample table called Student - StudentId, SubjectId and Marks. We have to find out that in each Subject(group), which Student scored the highest marks.

Sample Data


DECLARE @Student TABLE


(


    StudentId int, SubjectId int, Marks float


)


 


INSERT @Student


SELECT 1,1,8.0 UNION ALL


SELECT 2,1,5.0 UNION ALL


SELECT 3,1,7.0 UNION ALL


SELECT 4,1,9.5 UNION ALL


SELECT 1,2,9.0 UNION ALL


SELECT 2,2,7.0 UNION ALL


SELECT 3,2,4.0 UNION ALL


SELECT 4,2,7.5




Query to fetch Student with highest marks in each Subject


SELECT SubjectId,StudentId,Marks


FROM


(SELECT ROW_NUMBER() OVER(PARTITION BY SubjectId Order by Marks desc) as Topp,* from @Student) Stu


WHERE Stu.Topp =1




Results


SubjectId    StudentId    Marks


1             4            9.5


2             1            9