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.