Tuesday, November 11, 2008

How To search entire database?

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.

That's when I really felt the need for such a script and came up with this stored procedure "SearchAllTables". It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

check out these link


http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

http://it.toolbox.com/wiki/index.php/Get_Records_from_ANY_table_in_a_Database_Matching_Specified_Value

Sunday, August 3, 2008

With the inclusion of .NET within SQL Server 2005, many people thought that T-SQL would become semi-redundant, and no further updates would be applied

With the inclusion of .NET within SQL Server 2005, many people thought that T-SQL would become semi-redundant, and no further updates would be applied. The functionality that was missing could perhaps be completed through the use of .NET. However, T-SQL code is still the heart and lungs of SQL Server. Set-based processing, in which SQL Server can build query plans, is by far the best option for many queries. With SQL Server 2005, T-SQL has gained some functionality that was previously missing, making it a good choice over its competitors. T-SQL has expanded its horizons with capabilities for pivoting data and ranking rows of data based on ranking criteria, as well as other new functionality required to work with the new XML data type. In this article, you'll see how each of these enhancements works.for complete article check out this link

Monday, July 21, 2008

Turning Tables into Delimited Text

The world isn’t relational. It’s not divided into rows and columns. Sometimes
when there’s a list associated with a row, it’s better to show the list
as comma-separated text rather than try to preserve a relational presentation.
I use this technique on reports and sometimes on online grids. The
UDFs that implement this technique are not multistatement but scalar.
CREATE FUNCTION udf_Titles_AuthorList (
@title_id char(6) -- title ID from pubs database
) RETURNS varchar(255) -- List of authors
-- No SCHEMABINDING reads data from another DB
/*
* Returns a comma-separated list of the last name of all
* authors for a title.
*
* Example:
Select Title, dbo.udf_Titles_AuthorList(title_id) as [Authors]
FROM pubs..titles ORDER by Title
****************************************************************/
AS BEGIN
DECLARE @lname varchar(40) -- one last name.
, @sList varchar(255) -- working list
SET @sList = ''
DECLARE BookAuthors CURSOR FAST_FORWARD FOR
SELECT au_lname
FROM pubs..Authors A
INNER JOIN pubs..titleAuthor ta
ON A.au_id = ta.au_id
WHERE ta.title_ID = @Title_ID
ORDER BY au_lname
OPEN BookAuthors
FETCH BookAuthors INTO @lname
WHILE @@Fetch_status = 0 BEGIN
SET @sList = CASE WHEN LEN(@sList) > 0
THEN @sList + ', ' + @lname
ELSE @lname
END
FETCH BookAuthors INTO @lname
END
CLOSE BookAuthors
DEALLOCATE BookAuthors
RETURN @sList
END

Thursday, July 17, 2008

CAST and CONVERT (T-SQL)

How to use cast and convert check out this link

Renaming a Stored Procedure


Syntax:
sp_rename 'procedure_name1', 'procedure_name2'
procedure_name1
The current name of the stored procedure
procedure_name2
The new name of the stored procedure.

A stored procedure can be renamed. The new name should follow the rules for identifiers.

Examples

Code:
EXEC sp_rename 'spGetAvgGrade', 'spNewAvgGrade';
Output:
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to 'spNewAvgGrade'.
Explanation:

In the above example we change the name of the stored procedure spGetAvgGrade to spNewAvgGrade.

Tuesday, July 15, 2008

Implementing Custom Paging in ASP.NET with SQL Server 2005

Why Custom Paging?
Custom paging allows you to get limited number records from a large database table that saves processing time of your database server as well as your application server and makes your application scalable, efficient and fast.


In this article, I am going to explain how to create a stored procedure in SQL Server 2005 that allows you to pass startRowIndex and pageSize as a parameter and return you the number of records starting from that row index to the page size specified. It was possible in the SQL Server 2000 too but it was not as easy as in SQL Server 2005 is.


-- EXEC LoadPagedArticles 10, 5

CREATE PROCEDURE LoadPagedArticles

-- Add the parameters for the stored procedure here

@startRowIndex int,

@pageSize int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- increase the startRowIndex by 1 to avoid returning the last record again

SET @startRowIndex = @startRowIndex + 1

BEGIN

SELECT * FROM (

Select *,

ROW_NUMBER() OVER (ORDER BY AutoID ASC) as RowNum

FROM Articles

) as ArticleList

WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1

ORDER BY AutoID ASC

END

END

GO

Tuesday, July 8, 2008

Execute Dynamic SQL commands in SQL Server




In some applications having hard coded SQL statements is not appealing,
because of the dynamic nature of the queries being issued against the
database server. Because of this sometimes there is a need to dynamically
create
a SQL statement on the fly and then run that command. This can be
done quite simply from the application perspective where the statement is
built on the fly whether you are using ASP.NET, ColdFusion or any other programming
language
. But how do you do this from within a SQL Server stored procedure?

SQL Server offers a few ways of running a dynamically built SQL statement.
These ways are:
1. Writing a query with parameters
2. Using EXEC
3. Using sp_executesql
Writing a query with parameters
This first approach is pretty straight forward if you only need to pass parameters
into
your WHERE clause of your SQL statement. Let's say we need to find all records
from the customers table where City = '
London'. This can be done easily such as
the following example shows.
DECLARE @city varchar(75)
SET @city = '
London'
SELECT * FROM customers WHERE City = @city
2. Using EXEC
With this approach you are building the SQL statement on the fly and can pretty
much do whatever you need to in order to construct the statement. Let'
s say we
want to be able to pass in the column list along with the city.
For this example we want to get columns CustomerID, ContactName and City where
City = 'London'.
As you can see from this example handling the @city value is not at straight
forward, because you also need to define the extra quotes in order to pass a
character value into the query. These extra quotes could also be done within the
statement
, but either way you need to specify the extra single quotes in order for
the query to be built correctly and therefore run.
DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)
3. sp_exectesql
With this approach you have the ability to still dynamically build the query,
but you are also able to still use parameters as you could in example 1.
This saves the need to have to deal with the extra quotes to get the query to
build correctly. In addition, with using this approach you can ensure that the
data values being passed into the query are the correct datatypes.
DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Tips and Trics About Case

suppose We need a stored procedure that can be called by an application but the user wants to be able sort by either first name or last name. One would be tempted to use dynamic SQL to solve this problem, but we can use CASE to create a dynamic SQL equivalent
CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4)
AS
SET nocount ON

SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
FROM dbo.Customer
ORDER BY
CASE @sortdirection
WHEN 'asc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
ASC,
CASE @sortdirection
WHEN 'desc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
DESC
GO

EXEC dbo.getCustomerData 'lastname', 'desc'




A final requirement has crossed our desk. We need to modify the stored procedure to search customers by a specific state. If the state is omitted, we should return customers for all states.
ALTER PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4), @statecode VARCHAR(2) = NULL
AS
SET nocount ON

SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
FROM dbo.Customer
WHERE statecode = CASE WHEN @statecode IS NOT NULL THEN @statecode
ELSE statecode
END
ORDER BY
CASE @sortdirection
WHEN 'asc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
ASC,
CASE @sortdirection
WHEN 'desc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
DESC
GO

EXEC dbo.getCustomerData 'lastname', 'desc', 'MA'

Monday, July 7, 2008

Using Coalesce to Pivot

Using Coalesce to Pivot
If you run the following statement against the AdventureWorks database
SELECT Name
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

you will come up with a standard result

If you want to pivot the data you could run the following command.
DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ','
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

and get the result set with comma separated in a single column.

SQL Server Function to Determine a Leap Year

The following scalar function takes in a year and returns a bit flag indicating whether the passed in year is a leap year or not.

create function dbo.fn_IsLeapYear (@year int)
returns bit
as
begin
return(select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))
when 2 then 1
else 0
end)
end
go

That's all there is to it! The function takes in the year, appends '0228' to it (for February 28th) and adds a day. If the month of the next day is a 2 (as extracted by the DATEPART function), then we're still in February so it must be a leap year! If not, it is not a leap year.
Here are a few examples:

select dbo.fn_IsLeapYear(1900) as 'IsLeapYear?'
select dbo.fn_IsLeapYear(2000) as 'IsLeapYear?'
select dbo.fn_IsLeapYear(2007) as 'IsLeapYear?'
select dbo.fn_IsLeapYear(2008) as 'IsLeapYear?'

Thursday, May 15, 2008

Comma

Raw Data:

id name services
-----------------------------------
1 Joe AA
1 Joe AB
1 Joe AC
2 Judy GH
2 Judy GC
3 Kevin AA
3 Kevin GH

Result Set:

id name services
-----------------------------------
1 Joe AA, AB, AC
2 Judy GH, GC
3 Kevin AA, GH








If you have MSSQL2K, then the most performant (and arguably most elegant) way woud be to use a user-defined function:


Your data:

create table t1(id int, name varchar(10), services varchar(10))

insert into t1 values(1,'Joe' ,'AA')
insert into t1 values(1,'Joe' ,'AB')
insert into t1 values(1,'Joe' ,'AC')
insert into t1 values(2,'Judy' ,'GH')
insert into t1 values(2,'Judy' ,'GC')
insert into t1 values(3,'Kevin','AA')
insert into t1 values(3,'Kevin','GH')

.. the function:

create function my_comma_sep ( @id int) returns varchar(4000) as
begin
declare @result varchar(4000)
set @result = ''
select @result = @result
+ case when len(@result)>0 then ',' else '' end
+ services
from t1
where id = @id
return @result
end



Now you can use the function like this:

select id,name, dbo.my_comma_sep(id)
from (select distinct id,name from t1) x

..or like this:

select id, max(name), dbo.my_comma_sep(id)
from t1 group by id

... or like this:

select id, name, dbo.my_comma_sep(id)
from t1 group by id,name

... and the result:

1 Joe AA,AB,AC
2 Judy GH,GC
3 Kevin AA,GH

Tuesday, May 13, 2008

Comma Seprated Input

=======================
CREATE PROCEDURE dbo.sp1
@list as varchar(200)
AS

exec ( 'SELECT field1, field2, field3 FROM Table1 WHERE UPPER(RIGHT(RTRIM(field1),3)) IN ( ' + @list + ' ) ' )
=========================



and you need to call this proc as below

exec sp1 @list = '''DFG'',''ABC'',''ASD'',''FGH'''

Wednesday, April 23, 2008

Easy SQL “If Record Exists, Update It. If Not, Insert It.”

CREATE PROCEDURE dbo.spAddUserName
(
@UserID AS int,
@FirstName AS varchar(50),
@LastName AS varchar(50)
)
AS
BEGIN
DECLARE @rc int

UPDATE [Users]
SET FirstName = @FirstName, LastName = @LastName
WHERE UserID = @UserID

/* how many rows were affected? */
SELECT @rc = @@ROWCOUNT

IF @rc = 0
BEGIN
INSERT INTO [Users]
(FirstName, LastName)
VALUES (@FirstName, LastName)
END

END

Tuesday, April 22, 2008

What is Best Way TO Count Number Of Rows

select rows from sysindexes
where id = OBJECT_ID(@table_name) and
indid < 2

dbcc checkident (TableName, reseed, 0)

Wednesday, April 16, 2008

Row To Column

Suppose you have a table structure like

create table tmpStocks
(
[StockSymbol] [char] (8),
[ExchMM] [varchar] (10)
)
go
create table tmpExchanges
(
[Name] [char] (10),
[ExchSymbo] [char] (1)
)
go


2. Insert some values:

insert into tmpStocks (stocksymbol,exchmm) values ('KS','IP')
insert into tmpStocks (stocksymbol,exchmm) values ('PK6','IB')
insert into tmpStocks (stocksymbol,exchmm) values ('LHJ','I')
insert into tmpStocks (stocksymbol,exchmm) values ('JHL','P')
insert into tmpExchanges (name,ExchSymbo) values ('ISE','I')
insert into tmpExchanges (name,ExchSymbo) values ('BOX','B')
insert into tmpExchanges (name,ExchSymbo) values ('PCost','P')


and you want result like

how to get the results as following:

[StockSymbol] [ExchMM] [ISE] [BOX] [PCost]
ks IP 1 0 1
PK6 IB 1 1 0
LHJ I 1 0 0
JHL P 0 0 1


Solution1


select s.stocksymbol, s.exchmm,
ise = sum( case e.exchsymbo when 'I' then 1 else 0 end ),
box = sum( case e.exchsymbo when 'B' then 1 else 0 end ),
pcost = sum( case e.exchsymbo when 'P' then 1 else 0 end )
from tmpStocks s
left outer join tmpExchanges e
on charindex( e.exchsymbo, s.exchmm ) > 0
group by s.stocksymbol, s.exchmm



Solution2

declare @tname char(10), @tsym char(1)
declare @selectSQL varchar(1000)

select @selectSQL = ''

declare cur cursor for
select distinct [name], exchsymbo
from tmpExchanges
order by [name]

open cur
fetch cur into @tname, @tsym

while @@fetch_status = 0
begin

select @selectSQL = @selectSQL + ', ' + rtrim(@tname) + '= sum( case e.exchsymbo when ''' + @tsym + ''' then 1 else 0 end ) '
fetch next from cur into @tname, @tsym

end

select @selectSQL = 'select s.stocksymbol, s.exchmm ' + @selectSQL + ' from tmpStocks s '
+ 'left outer join tmpExchanges e '
+ 'on charindex( e.exchsymbo, s.exchmm ) > 0 '
+ 'group by s.stocksymbol, s.exchmm '

exec(@selectSQL)

close cur
deallocate cur

Covert Row To Column In SQL Server 2005

Let us suppose that you have a table like below

col1 | col2 | col3

--------*---------*----------

Value 1 | Value 2 | Value 3

And change it to one that looks like this:

Name | Value

-----*---------

col1 | Value 1

-----*---------

col2 | Value 2

-----*---------

col3 | Value 3





DECLARE @Table Table

(col1 varchar(10),

col2 varchar(10),

col3 varchar(10))

INSERT INTO @TABLE VALUES ('Value 1', 'Value 2', 'Value 3')

INSERT INTO @TABLE VALUES ('Value 4', 'Value 5', 'Value 6')

INSERT INTO @TABLE VALUES ('Value 7', 'Value 8', 'Value 9')



SELECT col, colval

FROM

(SELECT col1, col2, col3

FROM @TABLE) p

UNPIVOT

(ColVal FOR Col IN

(col1, col2, col3)

)AS unpvt

Tuesday, April 15, 2008

Sorting IP Addresses

IP addresses are represented in dotted decimal notation i.e. four numbers, each ranging from 0 to 255 and separated by dots. Each range from 0 to 255 can be represented by 8 bits and is thus called an octet. Some first octet values like 127 have special meaning - 127 represents the local computer. Octets 0 and 255 are not acceptable values in some situations. 0 can however be used as the second and third octet.

So, as you can imagine that unless we are storing the data in a sortable friendly way, sorting of this data would require some string manipulation. Let’s follow this up with an example:

CREATE TABLE IP_ADDR (COL1 NVARCHAR(30));
INSERT INTO IP_ADDR VALUES ( ‘30.33.33.30′ );
INSERT INTO IP_ADDR VALUES ( ‘256.10.1.2′ );
INSERT INTO IP_ADDR VALUES ( ‘256.255.10.2′ );
INSERT INTO IP_ADDR VALUES ( ‘127.0.0.1′ );
INSERT INTO IP_ADDR VALUES ( ‘132.22.33.44′ );
INSERT INTO IP_ADDR VALUES ( ‘132.10.30.1′ );
INSERT INTO IP_ADDR VALUES ( ‘132.1.1.132′ );
INSERT INTO IP_ADDR VALUES ( ‘10.20.30.10′ );
Now, if we order by COL1, then we will get:

SELECT * FROM IP_ADDR ORDER BY COL1;

COL1
——————————
10.20.30.10
127.0.0.1
132.1.1.132
132.10.30.1
132.22.33.44
256.10.1.2
256.255.10.2
30.33.33.30

As you can see from above, 30.33.33.30 comes last though we should expect it after 10.20.30.10. And likewise, in some other cases as well. Now, if we use the SUBSTRING (SQL Server) - SUBSTR in Oracle function and make use of the CHARINDEX (SQL Server) or INSTR (Oracle) or LOCATE (DB2 LUW), we can easily do these manipulations. Let us take SQL Server as an example - the same methodology would apply to Oracle and DB2 as well but do keep in mind the differences between CHARINDEX(), INSTR() and LOCATE() - we had discussed these before in some of our blog posts.

SELECT
SUBSTRING(COL1, 1, CHARINDEX('.', COL1) - 1) AS FIRST_OCTET,
CAST(SUBSTRING(COL1, CHARINDEX('.', COL1)+1, CHARINDEX('.', SUBSTRING(COL1, CHARINDEX('.', COL1)+1, LEN(COL1))) - 1) AS INT) AS SECOND_OCTET,
*
FROM IP_ADDR
ORDER BY
CAST(SUBSTRING(COL1, 1, CHARINDEX('.', COL1) - 1) AS INT),
CAST(SUBSTRING(COL1, CHARINDEX('.', COL1)+1, CHARINDEX('.', SUBSTRING(COL1, CHARINDEX('.', COL1)+1, LEN(COL1))) - 1) AS INT)

The SQL above is used to show how to use the CHARINDEX() and the SUBSTRING() function to separate out the first and the second octets…you can do the same with the third and the fourth one as well and then order by on those in the same order i.e. the first_octet first (after converting it to an integer), then the second_octet and so on. Here are the results from the execution from above:

FIRST_OCTET                    SECOND_OCTET COL1
------------------------------ ------------ ------------------------------
10 20 10.20.30.10
30 33 30.33.33.30
127 0 127.0.0.1
132 1 132.1.1.132
132 10 132.10.30.1
132 22 132.22.33.44
256 10 256.10.1.2
256 255 256.255.10.2

How To Sort DateField(where datafield is stored as varchar)

9101,,3/28/2008,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,1,400 9102,,3/7/2008,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,1,400
9103,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER proc [dbo].[usp_getsevent]

@userid int

as
create table #tempdat (scheduleeventid int, eventdate varchar(100),opponents varchar(100),locationname varchar(100),eventtime varchar(100),officials varchar(100))
create table #tempdat1 (scheduleeventid int, eventdate varchar(100),opponents varchar(100),locationname varchar(100),eventtime varchar(100),officials varchar(100))
create table #tempdat2 (scheduleeventid int, eventdate varchar(100),opponents varchar(100),locationname varchar (100),eventtime varchar(100),officials varchar(100))
declare @scheduleeventid int, @eventdate varchar(100),@opponents varchar(100),@locationname varchar(100),@eventtime varchar(100),@officials varchar(100),@INDEX varchar(10),@tdate varchar(100),@tempdate varchar(100),@tempdate1 varchar(100),@tempdate2 varchar(100)


BEGIN
DECLARE date_Cursor CURSOR FOR
SELECT scheduleEventid,eventdate,opponents,locationname,eventtime,officials FROM scheduledevent where eventdate NOT like '%~%' AND userid = @userid

OPEN date_Cursor

FETCH NEXT FROM date_Cursor
INTO @scheduleeventid,@eventdate,@opponents,@locationname,@eventtime,@officials

WHILE @@FETCH_STATUS = 0
BEGIN
SET @INDEX = CHARINDEX('-', @eventdate)
IF @INDEX >0
BEGIN
SET @tempdate = RIGHT(@eventdate, LEN(@tdate)-@INDEX)
insert into #tempdat (scheduleeventid,eventdate,opponents,locationname,eventtime,officials) values (@scheduleeventid,@eventdate,@opponents,@locationname,@eventtime,@officials)
END
ELSE
BEGIN
insert into #tempdat (scheduleeventid,eventdate,opponents,locationname,eventtime,officials) values (@scheduleeventid,@eventdate,@opponents,@locationname,@eventtime,@officials)
END


FETCH NEXT FROM date_Cursor
INTO @scheduleeventid,@eventdate,@opponents,@locationname,@eventtime,@officials
END

CLOSE date_Cursor
DEALLOCATE date_Cursor

insert into #tempdat1 select * from #tempdat order by cast(convert(varchar(10),eventdate,101)AS datetime) desc

insert into #tempdat2 SELECT scheduleEventid,eventdate,opponents,locationname,eventtime,officials FROM scheduledevent where eventdate like '%~%' And userid = @userid

insert into #tempdat1 SELECT * FROM #tempdat2

SELECT * from #tempdat1



drop table #tempdat
drop table #tempdat1
drop table #tempdat2

END

Setting the execution order of Triggers in SQL Server

Using sp_Settriggerorder stored procedure, we can define the execution order of the trigger. Here is the syntax for SQL Server 2005, taken from BOL. For complete explanation of syntax, please look at BOL.

sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername’
, [ @order = ] ‘value’
, [ @stmttype = ] ’statement_type’
[ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]

We are interested in the second parameter: “order”. It can take three values which means that it can take into account up-to three triggers.

  1. First – Trigger is fired first
  2. Last - Trigger is fired last
  3. None – Trigger is fired in random order.

The same procedure is available in SQLServer 2000 also but without namespace parameter because it does not support DDL triggers. Since SQL Server 2005, supports DDL trigger, namespace parameter defines the scope of the DDL trigger whether at Database level or at Server level. If value is NULL, trigger is a DML trigger.

We will use the same example as shown in yesterday’s blog. Connect to database using Management Studio. Create following table..

CREATE TABLE TEST
(
COL1 INT IDENTITY (1,1),
COL2 INT,
COL3 INT,
COL4 DATETIME
)
GO

Now we will create two DML triggers on the table.

CREATE TRIGGER dbo.TRI_TEST_2 ON dbo.TEST
FOR INSERT
AS
DECLARE @ID INT
SELECT @ID = COL1 FROM INSERTED

UPDATE dbo.TEST
SET COL3 = 5 + COL2
WHERE COL1 = @ID
GO

CREATE TRIGGER dbo.TRI_TEST_1 ON dbo.TEST
FOR INSERT
AS
DECLARE @ID INT
SELECT @ID = COL1 FROM INSERTED

UPDATE dbo.TEST
SET COL2 = 5 + @ID
WHERE COL1 = @ID
GO

Now let us insert the record in the table and examine the result.

INSERT INTO TEST(COL4) VALUES(GETDATE());

Following is the result.

SELECT * FROM TEST;

COL1 COL2 COL3 COL4
———– ———– ———– ———————–
1 6 NULL 2008-02-27 23:28:08.500

It is apparent from the result that TRI_TEST_2 got executed first at which point value of COL2 was null and as a end result, COL3 contains null value.

Let us now define the trigger order so that TRI_TEST_1 executes first and TRI_TEST_2 executes after TRI_TEST_1.

sp_Settriggerorder ‘TRI_TEST_1′,’FIRST’,'INSERT’
GO
sp_Settriggerorder ‘TRI_TEST_2′,’LAST’,'INSERT’
GO

After executing above mentioned command, insert new record and check the result. We are displaying both the records for comparison.

COL1 COL2 COL3 COL4
———– ———– ———– ———————–
1 6 NULL 2008-02-27 23:37:03.640
2 7 12 2008-02-27 23:45:25.357

With the pre-defined execution order of the trigger, all the columns are populated correctly. Similarly we can set the firing order for the DDL triggers as well.

Restrictions:
• For a single table, we can define only one first and last trigger for each statement.
• If trigger is modified using ALTER TRIGGER statement, priority of the trigger is set to None so it is very important to re-execute the sp_settriggerorder stored procedure to reset the correct execution order.
• If ‘FIRST’ or ‘LAST’ trigger already exists for the statement Type, reassigining new trigger to be the ‘FIRST’ one will result into an error.

How do we know whether any ordering is defined for the trigger or not? We can use objectproperty() function to retreive this information. Here is the query and the result.

SELECT OBJECT_NAME(PARENT_OBJ) TABLE_NAME,
NAME AS TRIGGER_NAME,
CASE OBJECTPROPERTY(ID, ‘ExecIsFirstInsertTrigger’)
WHEN 0 THEN ‘YES’
ELSE ‘NO’
END AS First
FROM SYSOBJECTS
WHERE XTYPE = ‘TR’
GO

TABLE_NAME TRIGGER_NAME First
———- ———— —–
TEST TRI_TEST_2 YES
TEST TRI_TEST_1 NO

Similarly we can check for ExecIsLastInsertTrigger property to see whether last trigger is defined for insert statement or not.

Update data in one table with data from another table

how to update more than one column in a table with values from columns in another table and explains how to do it in the three RDBMS that we support.

Table Structures and values:

TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:
I. TableA
a b c d
1 x y z
2 a b c
3 t x z

II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.


SQL Server:

UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO


Results after the update:

a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z

Functional difference between “NOT IN” vs “NOT EXISTS” clauses

“NOT IN” and “NOT EXISTS” clauses are not the same functionally or performance wise and, therefore, should be used appropriately. This blog post outlines how these commands are executed and discusses when it is appropriate to use them.

Sample data:
/*******************************************************************************************
Create a dummy EMP_MASTER table populate it with some records for illustration. This is Oracle Syntax. There are ten employees that have been created and 9 out of those 10 report to their manager: Dennis who is at the head of the chain and does not have a manager to report to.
********************************************************************************************/
CREATE TABLE EMP_MASTER
(
EMP_NBR NUMBER(10) NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR2(20 CHAR),
MGR_NBR NUMBER(10) NULL
)
/

INSERT INTO EMP_MASTER VALUES (1, ‘DON’, 5);
INSERT INTO EMP_MASTER VALUES (2, ‘HARI’, 5);
INSERT INTO EMP_MASTER VALUES (3, ‘RAMESH’, 5);
INSERT INTO EMP_MASTER VALUES (4, ‘JOE’, 5);
INSERT INTO EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO EMP_MASTER VALUES (6, ‘NIMISH’, 5);
INSERT INTO EMP_MASTER VALUES (7, ‘JESSIE’, 5);
INSERT INTO EMP_MASTER VALUES (8, ‘KEN’, 5);
INSERT INTO EMP_MASTER VALUES (9, ‘AMBER’, 5);
INSERT INTO EMP_MASTER VALUES (10, ‘JIM’, 5);
COMMIT
/

Resulting in:

EMP_NBR

EMP_NAME MGR_NBR
1 DON 5
2 HARI 5
3 RAMESH 5
4 JOE 5
5 DENNIS NULL
6 NIMISH 5
7 JESSIE 5
8 KEN 5
9 AMBER 5
10 JIM 5

Now, the aim is to find all those employees who are not managers. Let’s see how we can achieve that by using the “NOT IN” vs the “NOT EXISTS” clause.

NOT IN

SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );
COUNT(*)
———-
0

This means that everyone is a manager…hmmm, I wonder whether anything ever gets done in that case :-)

NOT EXISTS

SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );

COUNT(*)
———-
9

Now there are 9 people who are not managers. So, you can clearly see the difference that NULL values make and since NULL != NULL in SQL, the NOT IN clause does not return any records back. (in MS SQL Server, depending upon the ANSI NULLS setting, the behavior can be altered but this post only talks about the behavior that is same in Oracle, DB2 LUW and MS SQL Server).

Performance implications:

When using “NOT IN”, the query performs nested full table scans, whereas for “NOT EXISTS”, query can use an index within the sub-query.

Another Optional Method

Another way of doing this is to use an outer join and check for NULL values in the other table:

SELECT COUNT(*)
FROM EMP_MASTER T1
LEFT OUTER JOIN EMP_MASTER T2
ON T1.EMP_NBR = T2.MGR_NBR
WHERE T2.MGR_NBR IS NULL
/

Of course, there should be other selection criteria as well (possibly a range search criteria, an equality SARG (searchable argument) criteria etc.) to help improve the selectivity besides just the NOT EXISTS clause.

Multiple NULL values in a Unique index in SQL



this project needed to support having multiple NULL values in the column and
still have a UNIQUE constraint. That is allowed by Oracle but not in SQL Server
and DB2 LUW. There is a way to make this work in SQL Server and DB2 LUW also but
that requires a work-around. Consider this table:

CREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 NVARCHAR(10) NULL)
GO

In this table, COL1 has been declared as the primary key but we want a UNIQUE
constraint to be put on COL2 as well. Please note that COL2 is a nullable column
and that SQL Server does not allow multiple NULL values in a UNIQUE index and treats
them the same way. We can test it out prior to proceeding with the work-around:

Let tus create a unique index first:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL2)
GO

Now, let us try to insert these values:

insert into test_uq (col2) values (’abc’);
insert into test_uq (col2) values (’xyz’);
insert into test_uq (col2) values (Null);

All three will go in. After that, try to insert the NULL value again:

insert into test_uq (col2) values (Null);

and you will get the error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index
‘TEST_UQ_IND_1′.
The statement has been terminated.

The work-around is to have a computed column and define the unique constraint on it.
Here is how you can do that:

1) First, let’s drop the existing unique index:

drop index test_uq.TEST_UQ_IND_1

2) Next, let’s add the computed column:

ALTER TABLE TEST_UQ ADD COL3 AS (CASE WHEN COL2 IS NULL THEN CAST(COL1 AS NVARCHAR(10)) ELSE COL2 END);

In this command, we are stating that whenever the value for COL2 is null,
replace it with the primary key after casting it to the same data-type as that of COL2. By doing so, we will mae sure that COL3
is always NOT NULL and always have unique values. This approach will work well in this case as there should never be a clash of
the values between COL1 and COL2. For example, what-if you needed to do this on a column that was also an interger data-type column?
In
that case, chances of clashes of the data can arise. If you suspect a clash, you can have additional logic like:
(CASE WHEN COL2 IS NULL then -1 * COL1 ELSE COL2 END). That way, you can still maintain the logic and the uniqueness.

3) Now, create the unique index on this column:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)
GO

4) Next, let’s try to insert the NULL value again:

insert into test_uq (col2) values (Null);

This time it will go through. If we examine the contents of the table:

COL1 COL2 COL3
----------- ---------- ----------
1 abc abc
2 xyz xyz
3 NULL 3
5 NULL 5

As you can see, we have allowed multiple NULL values now for
COL2 and still maintained the uniqueness. We can next try to insert the value
“abc” again and see if that preserves our uniqueness criteria:

insert into test_uq (col2) values (’abc’);

This time, we will get an error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1′.
The statement has been terminated.

So, using this work-around, one can preserve the same behavior as Oracle.
This might be useful to you as well in case you are working on a project
that requires conversion from Oracle to SQL Server or Oracle to DB2 LUW.

Thursday, April 10, 2008

Using Index



select *from ZIPCodes where StateName = ‘New York’

Create Index
create nonclustered index idxStateName on ZIPCodes(StateName)
create nonclustered index idxZIPType on ZIPCodes(ZIPType)

Use Index
select *from ZIPCodes with(INDEX(idxZIPType)) where ZIPType = ‘S’

– List of Indexes on Perticular Table
exec sp_helpindex ‘ps_client_master’

Drop Index
drop index ps_client_master.ps_client_master_Index_1

Monday, March 31, 2008

The COALESCE Function

A more efficient approach to creating dynamic WHERE clauses involves using the COALESCE function. This function returns the first non-null expression in its expression list. The following example shows how it works.

DECLARE @Exp1 varchar(30),
@Exp2 varchar(30)

SET @Exp1 = NULL
SET @Exp2 = 'SQL Server'

SELECT COALESCE(@Exp1,@Exp2)

--Results--

------------------------------
SQL Server

The function processes the expression list from left-to-right and returns the first non-null value. The COALESCE function can process an infinite number of expressions (e.g., COALESCE(@Exp1,@Exp2,@Exp3,@Exp4,...)), but for the example presented in this article only two are needed.

Saturday, March 29, 2008

Return Null If A Value Is A Certain Value

You need to return NULL only if the value of your data is a certain value. How do you do this?
There are three different ways.

NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'


SELECT NULLIF(@1,'D')


REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)


CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'


SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

How to return random results from SQL SERVER

Sometimes you want to display 4 random articles/pics/you name it on your web page and you don’t want to write a lot of code.
SQL SERVER 2000 has a neat little function called NEWID() that can help you out.
Run the code below from Query Analyzer and keep hitting F5.
You will see that the results will be different every time.

USE pubs

SELECT top 4 * FROM dbo.authors
ORDER BY NEWID()

Date formatting in SQL Server

Declare @d datetime
select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select
@d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select
@d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select
@d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select
@d,convert(varchar,@d,106),106,'dd mon yy'
union all
select
@d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select
@d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select
@d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select
@d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select
@d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select
@d,convert(varchar,@d,112),112,'yymmdd'
union all
select
@d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select
@d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select
@d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select
@d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select
@d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

SQL:Format Data From Numberformat To Timeformat

Sometimes you have data that's in a number format and you need to show it in a time format. Instead of 2.25 you need to show 02:15
In that case you can use a combination of CONVERT and DATEADD
Run the examples below to see how it works

DECLARE @a DECIMAL(6,2)
SELECT @a = 3.00
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

SELECT @a = 3.15
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

SELECT @a = 3.25
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted


SELECT @a = 3.75
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

Check If Temporary Table Exists

How do you check if a temp table exists?
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL
Let's see how it works

--Create table
USE Norhtwind
GO

CREATE TABLE #temp(id INT)

--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END

--Another way to check with an undocumented optional second parameter
IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END



--Don't do this because this checks the local DB and will return does not exist
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END


--unless you do something like this
USE tempdb
GO

--Now it exists again
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END

--let's go back to Norhtwind again
USE Norhtwind
GO


--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END

now open a new window from Query Analyzer (CTRL + N) and run this code again
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT
'#temp does not exist!'
END


It doesn't exist and that is correct since it's a local temp table not a global temp table


Well let's test that statement
--create a global temp table
CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT
'##temp does not exist!'
END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT
'##temp does not exist!'
END

And yes this time it does exist since it's a global table

Sort A SQL Server Table With CASE Or CHARINDEX

Let's say you have a table with states in your database, you only have 3 values NY, ME and SC.
You want to order the result like this: first NY followed by SC and ME last.
You can do that in two different ways
One: use a case statement in your order by
Two: use Charindex in your order by
Let's see how that works

CREATE TABLE #TEST (
STATE CHAR(2))

INSERT INTO #TEST
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC'

-- order by using CASE


SELECT *
FROM #TEST
ORDER BY CASE STATE
WHEN 'NY' THEN 1
WHEN 'SC' THEN 2
ELSE 3
END


--Order by using CHARINDEX
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'NY-SC-ME')

--or without NY since CHARINDEX will return 0 for NY and it will be first
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-ME')


--the problem is of course if you have more values and you only want to have NY and SC showing up first and second
--let's insert 2 more rows
INSERT INTO #TEST
SELECT 'IL'
UNION ALL
SELECT 'CA'

-- Now the CHARINDEX Order doesn't work
-- the trick is to make it Descending and switch the states around

SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-NY') DESC

or this way

--Order by using CHARINDEX DESC
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'ME-SC-NY') DESC

COALESCE And ISNULL Differences

Run the following block of code


-- The result is 7, integer math
SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)

--The result is 7.5, which is correct
SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)

You will see that the result is not the same ISNULL does integer math while COALESCE does not

COALESCE correctly promotes its arguments to the highest data type in the expression list.
ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int
COALESCE looks at 2.00 and 15 and then promotes the integer to decimal

Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more
Here we have 4 variables and all except for one are null

DECLARE @Var1 VARCHAR(20)
DECLARE @Var2 VARCHAR(20)
DECLARE @Var3 VARCHAR(20)
DECLARE @Var4 VARCHAR(20)

SELECT @Var4 = 'ABC'


--This will return ABC
SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)

How To Split column In SQL

Suppose you have a column in a table that has multiple pieces of information in it that
I need to break out into various columns. The column is random but the
values I need to separate out are the number and the UN number as below:

245 HELIUM, COMPRESSED 2.2 UN1046


I need to separate the 2.2 and the UN1046 into different columns. How do I
parse this?
CREATE TABLE Inventory (ItemDescription VARCHAR(99))
INSERT Inventory VALUES ('245 HELIUM, COMPRESSED 2.2 UN1046' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' )
INSERT Inventory VALUES ('24adada5 HEdUM, sdsdsd 446.0 UN9988' )


SELECT RIGHT(ItemDescription,PATINDEX('% %',
REVERSE(ItemDescription))-1) AS COL1,
LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription))))),
PATINDEX('% %',REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription)))))))))
AS COL2
FROM Inventory

Formatting Data By Using CHARINDEX And SUBSTRING

Let's say you have names stored in the format [XYZ, ABC] but would like it to be [ABC XYZ]
How do you accomplish that in SQL?
SQL provides 2 useful functions (CHARINDEX And SUBSTRING)
Run the code below to see how they work

CREATE TABLE Names (ID INT identity not null,NameField VARCHAR(50), ProperNameField VARCHAR(50))

INSERT INTO Names
SELECT 'XYZ, ABC',NULL
UNION ALL
SELECT 'Smith, John',NULL
UNION ALL
SELECT 'Jackson, Michael',NULL
UNION ALL
SELECT 'Gates, Bill',NULL


UPDATE Names
SET ProperNameField =SUBSTRING(NameField,CHARINDEX(',',NameField) + 2,
LEN(NameField) - CHARINDEX(',',NameField))
+ ' ' + LEFT(NameField,CHARINDEX(',',NameField)-1)


SELECT * FROM Names