Magic Tables in SQL Server

Magic Table in SQL Server:
Magic tables are used to put all the deleted and updated rows. We can retrieve the
column values from the deleted rows using the keyword "deleted"

To project the deleted data into the deleted table we will use "output" clause
Below is the sample code to retrieve the deleted data.

Code Snippet

DECLARE @DeletedTable TABLE(DeletedTableID INT, DeletedData VARCHAR(20)) DELETE VENKATOutput OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3 SELECT * FROM @DeletedTable

Similarly, we can retrieve the updated data and old data too using the keyword "Inserted"

Provide me your valuable feedback regarding this.
Regards,
Venkatesan Prabu . J

Retrieve the number of Stored procedures in the Database

To find the total number of stored procedures:
Sysobjects table will store all the system objects and their properties. We can identify the system objects using the column XTYPE.
Below is the query used to retrieve the total number of stored procedures available
in the database.

Code Snippet

Use databasename Select Count(*) from Sysobjects where xtype='P'

The above query will fetch all system related and user related stored procedures in
a database. If we need only user related stored procedures please proceed with the below query.

Code Snippet

Select * from Sysobjects where xtype='P' AND ID>1

Similarly to retrieve the triggers in the database, we can use the below query.

Code Snippet

Select * from Sysobjects where xtype='TR'

Happy Learning!!!

Regards,

Venkatesan Prabu . J

Retrieving Database records with day difference

As a sql developer, i faced situations like finding all the records for that specified date irrespective of time in the datetime column.

Digging sql server concepts, i found datediff function to implement this functionality. Below is the code to retrieve the records for the specific date irrespective of time.

Code Snippet

CREATE TABLE [dbo].[DateTimeTable]( [id] [int] , [Date] [datetime] ) INSERT INTO [DateTimeTable] VALUES(1,'2/3/2008 1:00:00 AM') INSERT INTO [DateTimeTable] VALUES(2,'2/3/2008 1:00:00 AM') INSERT INTO [DateTimeTable] VALUES(3,'2/5/2008 1:00:00 AM') INSERT INTO [DateTimeTable] VALUES(4,'2/4/2008 1:00:00 AM') INSERT INTO [DateTimeTable] VALUES(5,'2/3/2008 6:00:00 AM') INSERT INTO [DateTimeTable] VALUES(6,'2/3/2008 5:00:00 AM') INSERT INTO [DateTimeTable] VALUES(6,GETDATE()) INSERT INTO [DateTimeTable] VALUES(7,GETDATE()) INSERT INTO [DateTimeTable] VALUES(8,GETDATE()) INSERT INTO [DateTimeTable] VALUES(9,GETDATE()) SELECT * FROM [DateTimeTable] SELECT * FROM [DateTimeTable] WHERE DATEDIFF(DAY,0,DATE) = DATEDIFF(DAY,0,GETDATE())

Happy Learning!!!

Regards,

Venkatesan Prabu . J

To list all the tables in the database

I got a query from dotnet spider user to list all the tables in a particular database. Wish to write some information related to this, in my blog.
We can achieve it by two methods either by using,
1. Information schema tables or
2. Sysobjects table.
Below is the sample code to acheive this,

Code Snippet

Use DatabaseName SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

Code Snippet

select * from sysobjects where xtype='u'

Regards,

Venkatesan Prabu . J

Error Handling in SQL Stored Procedure

SQL Server 2005 have introduced "Try catch" loop for catching exception.
The exception can be caught and an error message can be displayed similar to other programming languages. The message can contain the following:

• Exception message
• Exception Error Number
• Error severity
• Error state
• Procedure which returned the error
• Line number where the occured

Pre-defined functions are available to retrieve the above specified contents of the error. These functions can be used in catch block to retrieve values as follows:

Code Snippet

CREATE PROCEDURE SAMPLEPROC BEGIN BEGIN TRY -- Statements END TRY BEGIN CATCH DECLARE @ErrMsg NVARCHAR(MAX) DECLARE @ErrProc NVARCHAR(MAX) SELECT @ErrMsg = ERROR_MESSAGE(), SELECT @ErrProc = ERROR_LINE() END CATCH END

Regards,
Venkatesan Prabu . J

Triggers inside Stored procedure

Got a query from users like, “Is it possible to use trigger inside stored procedure. If not why? if yes how?” Its quite interesting question,

1. My answer is “No” we can’t use trigger inside stored procedure because trigger is an object which is binded with table objects.
2. The inherent property of the trigger is to fire automatically if any condition breaks and its associated with DML operations. But we are trying to create and initiate it from the stored procedure.
3. It will loose its property as a System object.

a. My answer is “Yes” oops confusing. To provide an alternative way, Microsoft provides us a new concept named CLR integration in sql server 2005.
b. We can create Managed trigger and wrap it in the stored procedure.

Regards,
Venkatesan Prabu . J

RowNumber in sql server 2000

In sql server 2000, we don’t have an inbuilt option to retrieve the data based on the rownumber. In sql server 2005, Rownumber() is used to implement such functionality.

In sql server 2000, we have to insert into a new table with identity column and after wards we have retrieve the result set based on the rownumber we want.
In the below code, I am inserting the rows into the table variable “@OutputTable” based on the result set order by address.

Code Snippet

DECLARE @VenkatTable TABLE (ID int, NAME varchar(50), ADDRESS varchar(50)) INSERT INTO @VENKATTABLE VALUES (1, 'Hari', 'Chennai') INSERT INTO @VENKATTABLE VALUES (2, 'Ram', 'Hyderabad') INSERT INTO @VENKATTABLE VALUES (3, 'Raj', 'Bombay') INSERT INTO @VENKATTABLE VALUES (52, 'Arun', 'Delhi') INSERT INTO @VENKATTABLE VALUES (53, 'Santhi', 'Dharmapuri') DECLARE @OutputTable TABLE ( RowID int identity primary key, Name varchar(50), Address varchar(50) ) DECLARE @RowID int INSERT INTO @ OutputTable (Name, Address) SELECT Name, Address from @VenkatTable Order by Address SELECT @RowID = RowID FROM @ OutputTable WHERE Name = 'Arun'; SELECT * FROM @ OutputTable WHERE RowID = @RowID

Happy learning……

Regards,
Venkatesan Prabu . J

RowNumber in Sql Server 2005

Wish to share some useful information related to data retrieval.
I got a recent query in dotnetspider site like, how to retrieve the data using rownumber concept. The user is not interested to fetch based on the data stored in the table but he wants to fetch based on the result set.
Problem scenario:
Irrespective of order by clause on any column, the user needs to retrieve the 50th row values.
Solution:
We have to map the row number for the entire result set and identify the 50th row values.

In the below code snippet, I’ve tried to create common table expression with rownumber() function[New in sql server 2005] order by address column. From the CTE, I’ve fetched the rownumber for the person "Arun" and display appropriate information for that particular row number.

Code Snippet

DECLARE @VenkatTable TABLE (ID int, NAME varchar(50), ADDRESS varchar(50)) INSERT INTO @VENKATTABLE VALUES (1, 'Hari', 'Chennai') INSERT INTO @VENKATTABLE VALUES (2, 'Ram', 'Hyderabad') INSERT INTO @VENKATTABLE VALUES (3, 'Raj', 'Bombay') INSERT INTO @VENKATTABLE VALUES (52, 'Arun', 'Delhi') INSERT INTO @VENKATTABLE VALUES (53, 'Santhi', 'Dharmapuri') select * from @VenkatTable DECLARE @RowID int ;WITH VenkatTableCTE AS (SELECT ROW_NUMBER() OVER(ORDER BY Address) AS RowID, Name, Address FROM @VenkatTable) SELECT @RowID = RowID FROM VenkatTableCTE WHERE Name = 'Arun'; SELECT * from VenkatTableCTE WHERE RowID = @RowID

Provide me your valuable feedback regarding this.
Regards,
Venkatesan Prabu . J

Deadlocks in SQL Server

Deadlock occurs if multiple users tried to access the resources holding some resources with them to finish the task. Dead lock can be avoided by using Lock methods in sql server.Locks can be classified in various types like

1. Rowlevel locking,
2. Page level locking,
3. Table level locking.

Row level locking: A specific row in the table will be locked by the user and meantime no one can access those rows.
Page level locking: In this case, we used to lock a particular page which hold 8 KB of data.
Table level lock: Here, entire table will be locked.

Conceptually there are various types of locks like,

Shared lock : The lock is shared by the users. One person can update the data meantime other persons can select the data.
Exclusive lock: No one can access the data until the concerned person releases the lock.

Regards,
Venkatesan Prabu. J

SQL Server MSN group

My sql server MSN group,

http://groups.msn.com/SqlMSUG/messages.msnw

Please Join this group to share your knowledge with us.

Regards,
Venkatesan Prabu. J
Moderator

My articles in MSDN

As a part of msdn community, i used to post articles and feedbacks on MSDN content.

http://msdn2.microsoft.com/en-us/user-venkatesan%20prabu.aspx

New In SQL Server 2008 - File Stream Datatype

SQL server 2008 formally called Katmai aims at Data management, Self orgnaizing and self maintaining capabilities. Microsoft aims at high availability (towards zero downtime availability). It supports for structured and unstructured data. Structured data is nothing but your table or any other formatted one where as unstructured data includes file streams and video images.

SQL Server 2008 allows us to store XML, emails, files and spatial information. FileStream is a new datatype introduced in Katmai, its used to stored the data in the file system.

Details about the file like file type, file size, file name will be considered as structured data and its stored in the sql server database whereas the contents of the file is considered as an unstructured data and gets stored in the file system. The contents can be accessed either by Win32 API or by using T-SQL statements.

Steps to be followed in implementing File Stream Data type:
1. First, we have to configure the file stream property.

Belows is the syntax to configure the file stream property,

Code Snippet

sp_filestream_configure [ [ @enable_level = ] level ] [ [ , @share_name = ] 'share_name' ]

The enable level should have a value from 0 to 3 indicating the authorization level like whether T-SQL access is allowed or not, Win32 API is required or not etc..,
2. Once executing the above command just check it out in the command prompt using the command NET SHARE.
3. The primary requirement to implement file stream property is to create the database with atleast one filegroup contains filestream property.
4. Create the table with column type as filestream. Unique constraint will be enforced on the table which has filestream type.
5. Insert the data into file stream datatype column and it gets stored in the file system.
Please provide me your valuable feedback. I will post the exact code in my next postings.

Regards,
Venkatesan Prabu .J

Primary Key Vs Unique Key

Unique Key constraints:
Unique key constraint will provide you a constraint like the column values should retain uniqueness. It will allow null value in the column. It will create non-clustered index by default Any number of unique constraints can be added to a table.

CREATE TABLE EMPLOYEETABLE (EMPID INT , IDVAL INT NOT NULL, FIRSTNAME VARCHAR(30) , LASTNAME VARCHAR(30) , CITY VARCHAR(30), JOININGDATE DATETIME)

I have created a table EmployeeTable and i am trying add a unique constriant on the column IDVAL.

ALTER TABLE EMPLOYEETABLE ADD CONSTRAINT UNIQUE_CONSTRAINT UNIQUE (IDVAL)

Primary Key: Primary key will create column data uniqueness in the table. Primary key will create clustered index by default Only one Primay key can be created for a table Multiple columns can be consolidated to form a single primary key It wont allow null values.

ALTER TABLE EMPLOYEETABLE ADD CONSTRAINT KEY_CONSTRAINT PRIMARY KEY (IDVAL)

Regards,

Venkatesan Prabu . J

sp_spaceused in sql server

Its a system stored procedure to check the free space in the database.
It provides details about the db size, free space, index_size, unused index_size, data size and reserved memory capacity.

Code Snippet

use master sp_spaceused

The above code snippet will provide you two result sets
1. General information like dbname,dbsize and unallocated space
2. Second result set will provide you info about index related information and reserved data.

Below query will provide the above information as a single result set.

Code Snippet

sp_spaceused 'DatabaseName'

Regards,
Venkatesan Prabu. J

Merging relative rows in sql server

Suppose i want to merge two tables based on certain relativeness we can opt self join operation.
Self join operation is nothing but the usage of same table in different instances.

Actual Table

TextData        ObjectID        SPID        StartTime          EndTime ---------------------------------------------------------------------------------------------------- Select a       123       50       2007-03-06 11:19:51.250          NULL Select a       123       50       NULL           2007-03-06 11:19:51.250

Now i want to merge these two rows as a single one. Below is the required output.

Resultant Table

TextData        ObjectID        SPID        StartTime          EndTime ---------------------------------------------------------------------------------------------------- Select a       123       50       2007-03-06 11:19:51.250          2007-03-06 11:19:51.250

Query to achieve the desired output is,

Code Snippet

DECLARE @VenkatTable TABLE(TextData nVARCHAR(10), ObjectID INT, SpID INT, StartTime DATETIME, Endtime DATETIME) INSERT INTO @VenkatTable (TextData, ObjectID, SpID, StartTime, EndTime) SELECT 'Select a', 123, 50,'2008-03-06 11:19:51.250',NULL UNION ALL SELECT 'Select a', 123, 50, NULL,'2008-03-06 11:19:51.250' select * from @VenkatTable select a.TextData,a.ObjectID, a.SpID, a.StartTime, b.EndTime from @VenkatTable a inner join @VenkatTable b on a.TextData = b.TextData and a.ObjectID = b.ObjectID and a.SpID = b.SpID where a.StartTime is not null and b.EndTime is not null

New SequentialID in sql server

NewSequentialId() is a new function introduced in sql server 2005. It works similar to Newid() but produces consecutive id's.

Unique identifier generated were rely on time and not on space so it will take another millions of years to generate a duplicate id.

Code Snippet

DECLARE @VenkatTable table ( RowID int IDENTITY, VenkatData varchar(20), VenkatGUID uniqueidentifier DEFAULT newsequentialid() ) INSERT INTO @VenkatTable( VenkatData ) VALUES ('Venkatesan Data' ) SELECT * FROM @VenkatTable

Updating table in different database in sql server

Problem Statement:
I want to update DatabaseA.TableA.Col1 with reference to Database2.TableB.Col2
with condition TableA.ColA = TableB.colB

Code Snippet

Update A set A.Col1 = B.Col2 from DatabaseA.dbo.TableA inner join Database2.dbo.TableB on A.ColA = B.ColB

Unique Identifier in sql server

Unique identifier is used to create an unique 32 bit column value in the table. NewID() is a system function used to generate unique identifier value. It will take millions of years to generate a duplicate Unique identifier value
I am using NewID() to insert the data,

Code Snippet

DECLARE @VenkatTableVar table ( RowGUID uniqueidentifier PRIMARY KEY,VenkatName sysname,Name varchar(20)) INSERT INTO @VenkatTableVar1 VALUES (newid(),'Venkat','VenkatName') SELECT * FROM @VenkatTableVar

Tried with manual data into the unique identifier column,

Code Snippet

DECLARE @VenkatTableVar table ( RowGUID uniqueidentifier PRIMARY KEY,VenkatName sysname,Name varchar(20)) INSERT INTO @VenkatTableVar VALUES ( cast( 'A8FB1E48-F129-44RC-82AE-001EAD5A8ADE' AS binary ), 'Venkat','VenkatName') SELECT * FROM @VenkatTableVar

Collation in sql server

Its used to define and instruct compilers to recognize the query keywords.

Code Snippet

Create database VenkatDatabase collate Latin1_General_CS_AS_KS_WS

In the above code, i am trying to create a database with supporting language as latin.
It should support,
CS - Case sensitiveness
AS - Accent sensitiveness
KS - Kana sensitiveness
WS - Width sensitiveness
We can specify the insensitiveness using the CI,AI,DI,WI
Ex:
CI - Case insensitiveness

Replication types in sql server

Three types of replication available,
1. Snapshot replication : You have to take entire database at a stretch and update it in the subscription database
2. Transactional replication : Replication can be done for each transaction
3. Merge replication: Updation can be done on both sides and happened if the databases are connected.
Other way of segregating the replication technology is,
1. Push subscription - The publisher will initiate the process to push the data to subscriber
2. Pull subscription - The subscriber will request for the data from the publisher.

Table partitioning in sql server

Table partitioning is the concept introduced in sql server 2005. Its used to enhance faster database accessibility. It may be either Horizontal partitioning or Vertical partitioning.
Horizontal Partitioning:
In this type, the rows will be segregated as two tables (Old table and new table). Its a logical seperation placed in different file groups.
If i want to access most recent data frequently when compared to old data then horizontal partitioning paves the way for this. It's highly advisable to implement this concept in multi processor environment.
Schema remains the same in horizontal partitioning.
Vertical partitioning:
Its nothing but segregating the columns of the table, part of the column in one table and other part of the column in other table(in case of partitioning into two tables).

Truncate Vs Delete Statement

After deleting all the rows in the table, both will retain the table structure
* Truncate will delete all rows at a stretch where as delete statement will delete individual rows at a time.
* Truncate is comparatively faster than delete statement.
* Truncate is not a logged activity where as delete statement will create a log record in the LDF file.
* Truncate statement will remove the reference where as delete statement will delete the data itself.
* We cannot provide where condition in truncate statement where as its possible in delete statement.

My T-SQL works

Hi Friends,
I got a peculiar problem from MSDN users and try to resolve the same.
OOPS its takes more than 15 minutes to arrive the solution. I came with three solutions but two solutions were previously given by others. This is my solution for the problem.

Problem Faced

GID Value ------------------------------------ 1 True 1 True 2 False 2 True 3 True If any GID with false should return the result set as False. GID with only true should return "True"

Code Snippet

DECLARE @t TABLE (GID int, Value varchar(10)) INSERT @t (GID, Value) VALUES (1, 'True') INSERT @t (GID, Value) VALUES (1, 'True') INSERT @t (GID, Value) VALUES (1, 'True') INSERT @t (GID, Value) VALUES (2, 'False') INSERT @t (GID, Value) VALUES (2, 'True') INSERT @t (GID, Value) VALUES (3, 'True') INSERT @t (GID, Value) VALUES (4, 'False') INSERT @t (GID, Value) VALUES (5, 'True') (select distinct t1.GID,t1.Value from @t as t1 inner join @t as t2 on t1.GID=t2.GID where t1.gid not in (select t3.gid from @t as t3 where t3.value ='false')) union (select distinct t3.GID,t3.Value from @t as t3 where t3.value ='false') select * from @t

Listing the users in sql server

sp_who is used to list all the users connected to sql server
It will list out the current process id, login user name, process through which the user gets connected, connected time, duration, Details database accessed by the user, login user role.

Difference between sp_who and sp_who2:
Both will perform the same operation, sp_who2 will give additional performance related information like
CPU time
Disk I/O
SPID

There is an alternative way to retrieve the result set. We can achieve it by accessing the system tables too,
select* from master.dbo.syslogins
Regards,
Venkatesan Prabu . J

To find Index of white spaces in SQL Server

Charindex is used to check a particular character index in a word. I tried to pick the index of the white spaces and insert those details in a temporary table. Below is the code,

Code Snippet

declare @qry varchar(100), @StartLoc int select @StartLoc = 1 select @qry = '1 df546 156 008 10/00 1203301985 BEY 112354 PaaaP 6785.00 012300-0110 011011011080 B O ' create table #VenkatTable(val int) while @StartLoc <= len(@Qry) begin set @StartLoc = charindex(' ',@qry,@StartLoc) insert into #VenkatTable values (@StartLoc) set @StartLoc = @StartLoc+1 end select * from #VenkatTable drop table #VenkatTable

Regards,
Venkatesan Prabu .J

To retrieve the query execution time

CURRENT_TIMESTAMP is used to retrieve the current time. By using this system function we can compare the time before and after the query execution.

Code Snippet

declare @qry varchar(100), @StartLoc int DECLARE @StartTime datetime, @EndTime datetime SET @StartTime = CURRENT_TIMESTAMP; -- **********QUERY TO BE CHECKED ************* SELECT * FROM [Venkat] -- ***************************************** SET @EndTime = CURRENT_TIMESTAMP SELECT DATEDIFF(ms, @StartTime, @EndTime) GO

Row_Number in sql server 2005

ROW_NUMBER function is to find the row number for the column which is specified in Order by clause.Its considered as one of the ranking function.

Code Snippet

declare @qry varchar(100), @StartLoc int create table venkitable(id int, names varchar(30),RELATIVENESS INT) insert into venkitable values(101,'dfVenkat',1) insert into venkitable values(23,'sdfVenkat2',1) insert into venkitable values(323,'sdfVenkat3',1) insert into venkitable values(4111,'sdfVenkat4',1) insert into venkitable values(5345,'rtVenkat5',1) -- ROW_NUMBER FUNCTION IS TO FIND THE ROW NUMBER OF A PARTICULAR-- COLUMN IF ITS SPECIFIED IN ORDER BY CLAUSE SELECT ROW_NUMBER () OVER (ORDER BY names) AS venkatid, names ,id FROM venkitable -- SHOULD DISPLAY AN ERROR INDICATING THAT ROW_NUMBER MUST HAVE AN ORDER BY CALUSE SELECT ROW_NUMBER () OVER() AS venkatid, names ,id FROM venkitable -- WILL INDICATE AN ERROR BECAUSE THE SUBQUERY WILL GIVE MORE THAN ONE VALUES SELECT ROW_NUMBER () OVER (ORDER BY (SELECT ID FROM venkitable)) AS ID, NAMES FROM venkitable -- THIS ONE WILL WORK EXACTLY BUT REFRAIN THE SORT OPERATION-- SUPPRESS THE ROW_NUMBER() FUNCTIONALITY SELECT ROW_NUMBER () OVER (ORDER BY (SELECT ID FROM venkitable WHERE ID=1)) AS NAMES, NAMES FROM venkitable