Archive

Archive for the ‘SQL Tips’ Category

Check and ReSeed IDENTITY column value in a table

July 8, 2011 3 comments

In my [previous post] I discussed about IDENTITY property with a [demo video].

Here in this post we will see how you can Re-Seed a particular IDENTITY Column value.
 

There are times when you want to Re-Seed or reset the value of an identity column in a table.

When you delete a lot of records from a table and then try to insert more records. You would expect the identity values to start after the max value present after delete. But it preserves the max value ever present in the table and continues from there.

Or when you delete all records form a table and want to reseed the identity column value to start from afresh 1.
 

All you need is following statement, syntax:
DBCC CHECKIDENT (TableNameWithSingleQuotes, reSeed, NewseedValue);

-- Example:
DBCC CHECKIDENT ('Person.Contact', reseed, 100);

 

This will start assigning new values starting from 101. But make sure that there are no records that have value greater than 100, otherwise you might duplicates.

If all records are deleted from the table and you want to reseed it to 0 then you can also TRUNCATE the table again. This will reset the IDENTITY column as per the DDL of that table.
 

–> You can check the same demo here:


 

–> Sample SQL Code:

-- Check current IDENTITY value, and re-seeds the value with largest value of the column:
DBCC CHECKIDENT ('dbo.Employee'); -- do not use it.
GO

-- Re-seed IDENTITY column value:
DBCC CHECKIDENT ('dbo.Employee', reseed, 102);
GO

-- Check current IDENTITY value (without Re-seeding):
DBCC CHECKIDENT ('dbo.Employee', noreseed);
GO

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Deepak B')

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Manish A')
GO

SELECT * FROM [dbo].[Employee]
GO

 

Categories: SQL Tips Tags: , , ,

SQL Query for calculating Running Totals

July 4, 2011 4 comments

A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total.

Let’s see how to get these Running totals by creating a simple SQL queries below:

USE [AdventureWorks]
GO

-- Method 1: (Query Cost 9%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  a.row, a.SalesPersonID, a.FirstName, a.LastName, a.OrderDate, a.TotalDue, sum(b.TotalDue) as RunTotal
from RunTot a
join RunTot b on a.SalesPersonID = b.SalesPersonID and a.row >= b.row
group by a.row, a.SalesPersonID, a.FirstName, a.LastName, a.TotalDue, a.OrderDate
order by a.SalesPersonID, a.row

-- Method 2: (Query Cost 91%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  row, SalesPersonID, FirstName, LastName, OrderDate, TotalDue,
(select SUM(TotalDue) from RunTot b where b.SalesPersonID=a.SalesPersonID and b.row<=a.row) as RunTotal
from RunTot a

-- Output Listed below:

Categories: SQL Tips Tags:

CTE Recursion | Sequence, Dates, Factorial, Fibonacci series

May 23, 2011 14 comments

CTE, Common Table Expressions

According to MS BOL CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

More info  on CTE can be found here: http://msdn.microsoft.com/en-us/library/ms190766.aspx

Some examples of CTE Recursion or Recursive CTE can be found here:

-- Number Sequence: 1 to 10
;with num_seq as (
	select 1 as num
	union all
	select num+1
	from num_seq
	where num<100)
select num
from num_seq
where num < 10

-- Date Sequence: May 2011
;with dt_seq as (
	select cast('5/1/2011' as datetime) as dt, 1 as num
	union all
	select dt+1, num+1
	from dt_seq
	where num<31)
select dt
from dt_seq

-- Factorial
;with fact as (
	select 1 as fac, 1 as num
	union all
	select fac*(num+1), num+1
	from fact
	where num<12)
select fac
from fact
where num=5

-- Fibonacci Series
;with fibo as (
	select 0 as fibA, 0 as fibB, 1 as seed, 1 as  num
	union all
	select seed+fibA, fibA+fibB, fibA, num+1
	from fibo
	where num<12)
select fibA
from fibo

More on Recursive CTE: http://msdn.microsoft.com/en-us/library/ms186243.aspx

Categories: SQL Tips Tags: , ,

Database Schema diff

May 16, 2011 1 comment

Are you also looking for a Database diff tool that can provide you difference in 2 similar but different databases?

Few days back I was also searching for the same and stumbled over hell lot of tools. Found some good tools but they were trial-ware, limited to 14 or 30 days, but not worth to buy. Among them I liked SQL Delta, packed with lot of features.

SQL Delta provides you difference between 2 different database’s schemas and you can also compare data within those schemas. But the kind of html report it provides is not detailed and user friendly. I was looking for output on excel reports, thus thought to write my own logic to query the metadata of both the databases and get the difference, and here’s that:

 with cte as (
 select isnull(at.TABLE_CATALOG,'Database_A') as Database_A, at.TABLE_NAME as TBL_NME_A, ac.COLUMN_NAME as COL_NME_A,
 isnull(bt.TABLE_CATALOG,'Database_B') as Database_B, bt.TABLE_NAME as TBL_NME_B, bc.COLUMN_NAME as COL_NME_B
 from Database_A.INFORMATION_SCHEMA.TABLES at
 join Database_A.INFORMATION_SCHEMA.COLUMNS ac on ac.TABLE_NAME = at.TABLE_NAME
 left join Database_B.INFORMATION_SCHEMA.TABLES bt on bt.TABLE_NAME = at.TABLE_NAME
 left join Database_B.INFORMATION_SCHEMA.COLUMNS bc on bc.TABLE_NAME = bt.TABLE_NAME and bc.COLUMN_NAME = ac.COLUMN_NAME
 UNION
 select isnull(at.TABLE_CATALOG,'Database_A') as Database_A, at.TABLE_NAME as TBL_NME_A, ac.COLUMN_NAME as COL_NME_A,
 isnull(bt.TABLE_CATALOG,'Database_B') as Database_B, bt.TABLE_NAME as TBL_NME_B, bc.COLUMN_NAME as COL_NME_B
 from Database_B.INFORMATION_SCHEMA.TABLES bt
 join Database_B.INFORMATION_SCHEMA.COLUMNS bc on bc.TABLE_NAME = bt.TABLE_NAME
 left join Database_A.INFORMATION_SCHEMA.TABLES at on at.TABLE_NAME = bt.TABLE_NAME
 left join Database_A.INFORMATION_SCHEMA.COLUMNS ac on ac.TABLE_NAME = at.TABLE_NAME and ac.COLUMN_NAME = bc.COLUMN_NAME)
 select * from cte
 order by isnull(TBL_NME_A, TBL_NME_B), isnull(COL_NME_A, COL_NME_B)

The output of the above query gives you total 6 columns, 3 columns from first database & same 3 columns from second database. The columns listed are “Database Name”, “Table Name” & “Column Name”.

– If only “Column Name” values is absent then the tables exist but the column does not.

– If both “Table Name” & “Column Name” values are absent then the table does not exist.

The INFORMATION_SCHEMA.TABLES & INFORMATION_SCHEMA.COLUMNS views provides you information on underlying tables & columns of those tables. They contain metadata of the database, generally they internally use the sys views to get information, like sys.tables & sys.columns.

For more information on metadata & system catalog tables/views check my following post: https://sqlwithmanoj.wordpress.com/2010/12/06/querying-sql-server-metadata/

Categories: SQL Tips Tags: , , ,

more on – ORDER BY with CASE

April 9, 2011 1 comment

One of my previous post mentions about using CASE construct with ORDER BY clause to tweak the ordering of the result set. That example was a simple one, this post provides more insight on this tip.

Most of us must have experienced this problem at least once when you want to order some of your records in a specific way. Or your boss or client asks this weird request to see some specific records on top. And you are puzzelled, how to do this.

The following example shows how we can get this:

USE [AdventureWorks]
GO

--Simple sort by ORDER BY
SELECT FirstName, LastName
FROM Person.Contact
ORDER BY FirstName

-- ORDER BY with CASE
SELECT FirstName, LastName
FROM Person.Contact
ORDER BY CASE LastName
			WHEN 'Wright'  THEN '0'
			WHEN 'Jenkins' THEN '1'
			WHEN 'Torres'  THEN '2'
			WHEN 'Sanchez' THEN '3'
			ELSE LastName
	END

-- Multiple column sort with ORDER BY with CASE
SELECT FirstName, LastName
FROM Person.Contact
ORDER BY CASE LastName
			WHEN 'Wright'  THEN '0'
			WHEN 'Jenkins' THEN '1'
			WHEN 'Torres'  THEN '2'
			WHEN 'Sanchez' THEN '3'
			ELSE LastName
	END, FirstName
Categories: SQL Tips Tags: ,