Script To Reseed Identity Columns In SQL Server

At times we need to reset seed value of identity column of SQL Server tables. This is mainly required when we need to clean test data from identity columns tables and start inserting fresh data from the beginning.

You can use DBCC CHECKIDENT command to reseed or reset seed value of a identity column. For example, the following command resets seed of customer table 0.

DBCC CHECKIDENT (Customer, RESEED, 0)

After reseed of the identity column, the following INSERT statements uses reseed value + 1. If you INSERT a record in to table Customer after executing the above reseed statement, the newly inserted record gets 1 as identity value.

How To Display Current Session SET Options? [ SQL Server ]

SQL Server allows users to configure many options of a session using SET command. The options configured through SET command affect the outcome of how queries are executed and the underlying behaviour of database engine.

At time we would like to know the current session SET options. But  there is no built statement/function which displays this information.

SQL Server only offers a metadata function @@OPTIONS which returns an integer that allows us to get the values that are set for the current session.

As the integer value returned by @@OPTIONS does not make much sense for most of the SQL Server users, we have written a simple bit wise operation SQL script which shows you all the SET options along with their status.

DECLARE @v_options INT

SELECT @v_options = @@OPTIONS

--DISABLE_DEF_CNST_CHK

IF ( (1 & @v_options) = 1 )

    PRINT 'DISABLE_DEF_CNST_CHK: ON'

ELSE

    PRINT 'DISABLE_DEF_CNST_CHK: OFF'

--IMPLICIT_TRANSACTIONS

IF ( (2 & @v_options) = 2 )

    PRINT 'IMPLICIT_TRANSACTIONS: ON'

ELSE

    PRINT 'IMPLICIT_TRANSACTIONS: OFF'

--CURSOR_CLOSE_ON_COMMIT

IF ( (4 & @v_options) = 4 )

    PRINT 'CURSOR_CLOSE_ON_COMMIT: ON'

ELSE

    PRINT 'CURSOR_CLOSE_ON_COMMIT: OFF'

--ANSI_WARNINGS

IF ( (8 & @v_options) = 8 )

    PRINT 'ANSI_WARNINGS: ON'

ELSE

    PRINT 'ANSI_WARNINGS: OFF'

--ANSI_PADDING

IF ( (16 & @v_options) = 16 )

    PRINT 'ANSI_PADDING:ON'

ELSE

    PRINT 'ANSI_PADDING: OFF'

--ANSI_NULLS

IF ( (32 & @v_options) = 32 )

    PRINT 'ANSI_NULLS: ON'

ELSE

    PRINT 'ANSI_NULLS: OFF'

--ARITHABORT

IF ( (64 & @v_options) = 64 )

    PRINT 'ARITHABORT: ON'

ELSE

    PRINT 'ARITHABORT: OFF'

--ARITHIGNORE

IF ( (128 & @v_options) = 128 )

    PRINT 'ARITHIGNORE: ON'

ELSE

    PRINT 'ARITHIGNORE: OFF'

--QUOTED_IDENTIFIER

IF ( (256 & @v_options) = 256 )

    PRINT 'QUOTED_IDENTIFIER: ON'

ELSE

    PRINT 'QUOTED_IDENTIFIER: OFF'

--NOCOUNT

IF ( (512 & @v_options) = 512 )

    PRINT 'NOCOUNT: ON'

ELSE

    PRINT 'NOCOUNT: OFF'

--ANSI_NULL_DFLT_ON

IF ( (1024 & @v_options) = 1024 )

    PRINT 'ANSI_NULL_DFLT_ON: ON'

ELSE

    PRINT 'ANSI_NULL_DFLT_ON: OFF'

--ANSI_NULL_DFLT_OFF

IF ( (2048 & @v_options) = 2048 )

    PRINT 'ANSI_NULL_DFLT_OFF: ON'

ELSE

    PRINT 'ANSI_NULL_DFLT_OFF: OFF'

--CONCAT_NULL_YIELDS_NULL

IF ( (4096 & @v_options) = 4096 )

    PRINT 'CONCAT_NULL_YIELDS_NULL: ON'

ELSE

    PRINT 'CONCAT_NULL_YIELDS_NULL: OFF'

--NUMERIC_ROUNDABORT

IF ( (8192 & @v_options) = 8192 )

    PRINT 'NUMERIC_ROUNDABORT: ON'

ELSE

    PRINT 'NUMERIC_ROUNDABORT: OFF'

--XACT_ABORT

IF ( (16384 & @v_options) = 16384 )

    PRINT 'XACT_ABORT: ON'

ELSE

    PRINT 'XACT_ABORT: OFF'

Here is the output when the above code is executed for my session

DISABLE_DEF_CNST_CHK: OFF
IMPLICIT_TRANSACTIONS: OFF
CURSOR_CLOSE_ON_COMMIT: OFF
ANSI_WARNINGS: ON
ANSI_PADDING:ON
ANSI_NULLS: ON
ARITHABORT: ON
ARITHIGNORE: OFF
QUOTED_IDENTIFIER: ON
NOCOUNT: OFF
ANSI_NULL_DFLT_ON: ON
ANSI_NULL_DFLT_OFF: OFF
CONCAT_NULL_YIELDS_NULL: ON
NUMERIC_ROUNDABORT: OFF
XACT_ABORT: OFF

 

How To Decide Whether to Rebuild or Reorganize Table Indexes [SQL Server]

Database Administrators sometimes get doubt whether to rebuild the indexes or reorganize the indexes. Before deciding the right option lets try to understand the difference between rebuilding and reorganizing.

When an Index is rebuilt, the existing index is dropped and a new one is created. This operation takes long time and utilizes lot of SQL Server resources like CPU cycles and disk space.

When an index is reorganized, the index is not dropped and recreated but the leaf level pages of the index are defragmented by physically reordering to match the logical order.

As rebuilding indexes is an expensive operation, DBA should carefully choose when to rebuild over reorganising the indexes by analysing the degree of fragmentation in the indexes. The general guidelines given in MSDN says

• Reorganise an index when the degree of fragmentation is between 5 and 30%
• Rebuild an index when the degree of fragmentation is over 30%

These are guideline values and the actual degree of fragmentation  varies case to case depending on many parameters of the database server environment. It is always advised to perform multiple tests to identify the exact degree of fragmentation which decides whether to rebuild or reorganize.

Example Script to Rebuild and Reorganize an Index

-- SQL Server Script to rebuild the index

-- 'PK_Employee_EmployeeID' available on

-- 'Employee' table

ALTER INDEX PK_Employee_EmployeeID

ON Employee

REBUILD;

-- SQL Server Script to reorganize the index

-- 'PK_Employee_EmployeeID' available on

-- 'Employee' table

ALTER INDEX PK_Employee_EmployeeID

ON Employee

REORGANIZE ;

Disabling a Clustered Index Automatically Disables Access To Underlying Table [ SQL Server ]

I learnt an interesting fact of SQL Server today. Disabling a clustered index of a table disables access to the data of underlying table. We will not be allowed to execute any of the statements SELECT, UPDATE, DELETE, INSERT on the underlying table.

If we try to execute any of those statements, SQL Server displays the following error message

Msg 8655, Level 16, State 1, Line 1

The query processor is unable to produce a plan because the index '<name of the index>' on table or view '<name of the table>'' is disabled.

So next time when you see this error message, all you need to do to get rid of the problem is to enable the clustered index created on the table by rebuilding it.

It is also noted that disabling clustered indexes automatically disables all non-clustered indexes and XML indexes associated with the table.

You can read the MSDN article Guidelines for Disabling Indexes for more interesting facts

Retrieving a Random Record using SELECT Statement[SQL Server]

When we were developing a small client server application using C# and SQL Server, we had a requirement to select a record randomly from a database table and show it to the user.

First we tried bringing a set a qualifying record to front end and randomly pick one using C# code. But this is proven as a very inefficient way as unnecessarily records are being fetched from database and discarded after selecting one random record.

We started looking for an efficient way through which a random record can be picked using SQL script and fetch that record alone out of database.

We succeeded to pick the random record using a simple SELECT statement trick. All we need to do to randomly pick a record is, SELECT TOP 1 record from the table and order it using NEWID() function. Here is the sample code

SELECT TOP 1 Id, FirstName, LastName, Age, DateOfBirth

FROM Employee

ORDER BY NEWID()

 

In order to test how good this method is going to randomise the data, a small test is performed on a table containing 429 records.  The results are pretty impressive and the data is properly randomised in the 100 test runs as shown in the following chart.

SQL Server: Check Whether All Characters In a String Are in Uppercase or Not

When I was developing a small application, I had to write a Microsoft SQL Server script to check whether all the characters in a given string are uppercase alphabets or not.

Here is the Microsoft SQL Server function code which performs the check. This code is compatible with Microsoft SQL Server 2000 and Microsoft SQL Server 2005.

---

/****************************************************************

 * Purpose: Check whether all characters

 *         in a string are capital alphabets or or not

 * Parameter: input string

 * Output: 0 - on success; 1 on failure

 ****************************************************************/

CREATE FUNCTION fnChkAllCaps(@P_String VARCHAR(500))

RETURNS BIT

AS

BEGIN

 

DECLARE @V_RetValue BIT

DECLARE @V_Position INT

 

SET @V_Position = 1

SET @V_RetValue = 0   

 

--Loop through all the characters

WHILE @V_Position <= DATALENGTH(@P_String)

           AND @V_RetValue = 0

BEGIN

 

     --Check if ascii value of the character is between 65 & 90

     --Note: Ascii value of A is 65 and Z is 90

     IF ASCII(SUBSTRING(@P_String, @V_Position, 1))

            BETWEEN 65 AND 90

        SELECT @V_RetValue = 0

    ELSE

       SELECT @V_RetValue = 1      

   --Move to next character       

   SET @V_Position = @V_Position + 1

END

 

--Return the value

RETURN @V_RetValue

 

END

---

Sample code to test the function

---

SELECT dbo.fnChkAllCaps('TECHTHOUGHTS') -- Returns 0

GO

SELECT dbo.fnChkAllCaps('TechThoughts') -- Returns 1

GO

---

The above function iterates through all the characters of a given input string and checks whether ASCII value of the characters is between 65 and 90 to verify it is an uppercase alphabet or not.

You might be wondering why is ASCII values check is between 65 and 90. It is because the ASCII value of uppercase A is 65 and uppercase Z is 90.

Soon I'll rewrite the same code using SQL Server 2005 CLR functions and post it. I believe Microsoft SQL Server 2005 CLR functions perform this check very efficiently.

Code Names of All Version Of Microsoft SQL Server

When I was browsing through Microsoft SQL Server articles I came across a Wikipedia page which contains information about all the releases of  Microsoft SQL Server like year of release, version numbers and code names.

Sl.No.	Version	Release Year	Code Name
1	SQL Server 4.21	1993	--
2	SQL Server 6.0	1995	SQL95
3	SQL Server 6.5	1996	Hydra
4	SQL Server 7.0	1999	Sphinx
5	SQL Server 7.0 OLAP	1999	Plato
6	SQL Server 2000 32 bit	2000	Shilol
7	SQL Server 2000 64 bit	2000	Liberty
8	SQL Server 2005	2005	Yukon
9	SQL Server 2008	Expected in 2008	Katmai

 

Here is the full article of Wikipedia which gives more information on Microsoft SQL Server.

Microsoft To Launch Windows Server 2008, Visual Studio 2008 and SQL Server 2008 on Feb 27, 2008

At the World Partner Conference Microsoft announced that they will launch Windows Server^® 2008, Visual Studio^® 2008 and Microsoft SQL Server™ 2008 products on Feb 27, 2008  in Los Angeles . Microsoft is describing this launch event as "the most important launch in company history" and trying to pull out all the stops to ensure a very high-profile launch with massive amounts of marketing and technological evangelism for the three products being launched. 

Read one of my last month's post New Exciting Features of SQL Server 2008  which highlights SQL Server 2008 features and read Worldwide Partner Conference document for more information about the event.

New Exciting Features of SQL Server 2008

Today Microsoft released SQL Server 2008 CTP. It is almost 19 months since the previous version of SQL Server 2005  is released (Nov 2005). SQL Server 2008 is promised to have many new features for developers and database administrators. When I went through the books online of the new CTP, I've found the following features which are in developers interest

Table Value Parameters

It is quite often required to pass arbitrary  set of values to a store procedure/function for processing. As there is no data type is available for holding a set of values as a parameter, developers use to pass them as delimiter ( generally coma )  separated string to the stored procedure/function. Now SQL Server 2008 supports passing a Table Value parameters which provide an easier way to pass a set of table structured values(rows) to a stored procedure for function.

 

Language Integrated Query

Language Integrated Query , which is abbreviated as LINQ is going to change the way in which developers interact with SQL Server. LINQ enables developers to issue queries against data in terms of their programming language instead of SQL. It enables seamless, strongly typed, set-oriented queries in Microsoft Visual C#® or Microsoft Visual Basic® against the connected ADO.Net stack (SqlClient), ADO.NET DataSet, and the Entity Data Service Mapping provider.

 

ADO.Net Object Services

The Object services layer of the ADO.NET stack enables materialization, change tracking, and the persistence of data as CLR objects. Developers using the ADO.NET Framework can program against a database using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development and supports entities.

 

C# Support for SSIS Scripting Task

Are you a C# developer who is struggling to develop a Script Task/Component in SQL Server Integration Services(SSIS) using VB.NET?  Your worries are going to end soon. SQL Server 2008 is supporting C# based script task/component along with Visual Basic.NET.

 

Spatial and Location data

SQL Server 2008 includes support for vector-based data and location based data. This enables developers to build location-aware applications that capture geographical information from within the organization and integrate the data into applications.

For further details read white paper on SQL Server 2008 by Microsoft Corporation

Few facts about INSERTED & DELETED tables of SQL Server

The logical tables INSERTED and DELETED of SQL Server are accessible only inside triggers. The schema of the logical tables is similar to the schema of table to which triggers are attached. These tables hold data of new and old states of rows modified using DML statements like UPDATE, DELETE and INSERT. For example if a record is updated using an UPDATE statement, the INSERTED table holds the newly updated row data and DELETED tables contains previous version of updated data rows. The following are few interesting facts about these tables

1. In SQL Server 2000, these logical tables internally refer to database transaction log to provide data when user queries them.
2. In SQL Server 2005, these logical tables are maintained in tempdb and they are maintained using the new technology Row versioning.
3. Accessing of logical tables is much faster in SQL Server 2005 when compared to SQL Server 2000 as the load is removed from transaction log and moved to tempdb.
4. Logical tables are never indexed. So, if you are going to loop through each and every record available in these tables, then consider copying data of logical tables to temporary tables and index them before looping through.

Renaming a SQL Server database

There are three ways to rename a database in SQL Server. Two among the three ways are very simple and straight forward and the last one is a bit complex. Lets see the all the three of renaming a database

Using the stored procedure sp_renamedb is the simplest way of to rename a database. To rename a database, open a query analyzer (SQL Server 2000) or SQL Server Management Studio (SQL Server 2005) application and connect to the database server. Then change the current database to master. After changing the current database to master, execute the command ‘EXEC sp_renamedb ‘old_database_name’, ‘new_database_name’.

If you don’t know how to execute command SQL Server Management Studio you can make use of graphical user interface to rename a database. In the object browser displayed in Management Studio select the database which you want to rename and right click. In the list of menu option provided, select ‘rename’ and type the required new name of the database.

The final and complex way of renaming a database is to detach and attach a database. Detaching and attaching of a database can be done using a set of SQL commands of graphical user interface. I’ll explain how to do this in my next article.