SQL SERVER

How To search entire database?

2008-11-11T21:52:00.000-08:00

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

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

2008-08-03T21:33:00.000-07:00

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

Turning Tables into Delimited Text

2008-07-21T05:46:00.001-07:00

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

CAST and CONVERT (T-SQL)

2008-07-17T01:44:00.000-07:00

How to use cast and convert check out this link

Renaming a Stored Procedure

2008-07-17T00:09:00.001-07:00

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.

Implementing Custom Paging in ASP.NET with SQL Server 2005

2008-07-15T23:03:00.001-07:00

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, 5CREATE 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

Execute Dynamic SQL commands in SQL Server

2008-07-08T00:24:00.000-07:00


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

2008-07-08T00:12:00.000-07:00

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'

Using Coalesce to Pivot

2008-07-07T22:42:00.000-07:00

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

2008-07-07T22:19:00.000-07:00

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?'

Comma

2008-05-15T22:43:00.000-07:00

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

Comma Seprated Input

2008-05-13T05:20:00.000-07:00

=======================
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'''

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

2008-04-23T07:57:00.001-07:00

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

What is Best Way TO Count Number Of Rows

2008-04-22T16:46:00.000-07:00

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

2008-04-22T11:42:00.000-07:00

dbcc checkident (TableName, reseed, 0)

Row To Column

2008-04-16T09:06:00.000-07:00

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

2008-04-16T08:02:00.000-07:00

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

Sorting IP Addresses

2008-04-15T11:37:00.000-07:00

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)

2008-04-15T11:33:00.000-07:00

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

2008-04-15T11:17:00.001-07:00

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.

First – Trigger is fired first
Last - Trigger is fired last
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

2008-04-15T10:44:00.000-07:00

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

2008-04-15T10:38:00.000-07:00

“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

2008-04-15T10:29:00.000-07:00


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.

Using Index

2008-04-10T07:37:00.001-07:00


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

The COALESCE Function

2008-03-31T13:17:00.000-07:00

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.

Return Null If A Value Is A Certain Value

2008-03-29T15:00:00.001-07:00

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

2008-03-29T14:50:00.000-07:00

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

2008-03-29T14:48:00.000-07:00

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

2008-03-29T11:24:00.001-07:00

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

2008-03-29T11:22:00.000-07:00

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

2008-03-29T11:20:00.000-07:00

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

2008-03-29T11:18:00.000-07:00

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

2008-03-29T11:16:00.000-07:00

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

2008-03-29T11:06:00.000-07:00

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

Pad Numbers By Using CASE, LEFT And CONVERT

2008-03-29T11:03:00.000-07:00

Let's say you have a table with integer values below 100000 and you want them in the same format with leading zeros. For example 500 has to be 000500 and 1 has to be 000001
How do you do this in SQL server?
Below are two ways to accomplish this.

CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000

--Use CASE, LEN and CONVERT to pad the values
SELECT value,CASE LEN(value)
WHEN 1 THEN '00000' + CONVERT(VARCHAR,value)
WHEN 2 THEN '0000' + CONVERT(VARCHAR,value)
WHEN 3 THEN '000' + CONVERT(VARCHAR,value)
WHEN 4 THEN '00' + CONVERT(VARCHAR,value)
WHEN 5 THEN '0' + CONVERT(VARCHAR,value)
ELSE CONVERT(VARCHAR,value)
END AS Formattedvalue
FROM #ValueTable

--Use LEFT, LEN and CONVERT to pad the values
SELECT value,LEFT('000000',(6 -LEN(value )))
+ CONVERT(VARCHAR,value) AS Formattedvalue
FROM #ValueTable

Find all Primary and Foreign Keys In A Database

2008-03-29T11:01:00.000-07:00

To find all your foreign and primary keys in your database run the code below.
The ouput will return the primary key, primary key table, foreign key, foreign key table. Primary keys that don't have foreign keys will have N/A in the foreign key output

USE Northwind

SELECT tc.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME

Use OBJECTPROPERTY To Find Out Table Properties

2008-03-29T10:58:00.000-07:00

How many times did you open up a table in design view to find out if a table has
a delete trigger
a clustered index
an identity column
a primary key
a timestamp
a unique constraint
etc etc?

There is an easier way to find out all this info by using the OBJECTPROPERTY function
You will have to get the object id from the table to pass it into the OBJECTPROPERTY function.
To get the object id you can use the OBJECT_ID function.
Run the code below in the Northwind database to see how it works

USE Northwind

DECLARE @objectID INT
SELECT @objectID =OBJECT_ID('customers')

select OBJECTPROPERTY(@objectID,'TableDeleteTrigger') AS 'Table Has A Delete Trigger',
OBJECTPROPERTY(@objectID,'TableHasClustIndex') AS 'Table Has A Clustered Index',
OBJECTPROPERTY(@objectID,'TableHasIdentity') AS 'Table Has An Identity Column',
OBJECTPROPERTY(@objectID,'TableHasPrimaryKey') AS 'Table Has A Primary Key',
OBJECTPROPERTY(@objectID,'TableHasTimestamp') AS 'Table Has A Timestamp',
OBJECTPROPERTY(@objectID,'TableHasUniqueCnst') AS 'Table Has A Unique Constraint',
OBJECTPROPERTY(@objectID,'IsAnsiNullsOn') AS 'ANSI NULLS Is Set To ON'

Lookup OBJECTPROPERTY in Books On Line to find out about additional properties

SQL String Functions Tutorial

2008-03-29T10:48:00.000-07:00

1. CHARINDEX string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.

Example:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6

2. LEFT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.

Example:
Select LEFT ('MICROSOFT SQL SERVER 2000',4)
Result: MICR

3. RIGHT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.

Example:
Select RIGHT ('MICROSOFT SQL SERVER 2000',4)
Result: 2000

4. LEN string function takes 1 argument as string value and returns the length of entered string.

Example:
Select LEN ('MICROSOFT SQL SERVER 2000')
Result: 25

5. REPLACE string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.

Example:
Select REPLACE ('MICROSOFT SQL SERVER 2000','MICROSOFT','MS')
Result: MS SQL SERVER 2000

6. STUFF string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.

Example:
Select STUFF ('MICROSOFT SQL SERVER 2000', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2000

7. SUBSTRING string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length

Example:
Select SUBSTRING ('MICROSOFT SQL SERVER 2000', 11, 3)
Result: SQL

8. LOWER string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting

9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

10. REVERSE string function returns the string in reverse order. It takes 1 argument as string value.

Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA

11. LTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select LTRIM (‘ ASP ’)
Result: ASP-----
blanks at the right side not removed.

12. RTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select RTRIM (‘ ASP ’)
Result: -----ASP
blanks at the left side not removed.

13. PATINDEX function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.

Example:
select PATINDEX('%RO%','MICROSOFT')
Results: 4

14. STR function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.

Example:
select STR(140.15, 6, 1)
Result: 140.2

15. ASCII function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.

Example:
select ASCII('A')
Result: 65

To Get The Current Identity Value From A Table

2008-03-29T10:47:00.000-07:00

Let's first create our two simple tables
CREATE TABLE TestOne (id INT identity,SomeDate DATETIME)
CREATE TABLE TestTwo (id INT identity,TestOneID INT,SomeDate DATETIME)

--Let's insert 4 rows into the table
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())
INSERT TestOne VALUES(GETDATE())

Here are diffrent ways to check for the current value

--1 @@IDENTITY
SELECT @@IDENTITY
--this returns 4

--2 DBCC CHECKIDENT
DBCC CHECKIDENT (TestOne, NORESEED)
after running DBCC CHECKIDENT the message returned is
Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--3 MAX function
SELECT MAX(id)
FROM TestOne
you can also check with the MAX function but this is not recommended becuase you might get some other identity value that is not yours but from a different user

--4 TOP 1 and ORDER BY DESC
SELECT TOP 1 id
FROM TestOne
ORDER BY id DESC
--The same applies here as for the max function, this is not recommended

--5 IDENT_CURRENT
SELECT IDENT_CURRENT('TestOne')
--IDENT_CURRENT is another way to check

--6 SCOPE_IDENTITY
SELECT SCOPE_IDENTITY()
--This one is very similar to @@IDENTITY with one BIG difference (shown later)

How To Delete a null record

2008-03-29T10:45:00.000-07:00

The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL

CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(NULL)

--Check what's in the table
SELECT * FROM #TestDeleteNull

--This won't work
DELETE #TestDeleteNull
WHERE SomeDate = NULL
--(0 row(s) affected)

--This is the way
DELETE #TestDeleteNull
WHERE SomeDate IS NULL
--(1 row(s) affected)

--Check again
SELECT * FROM #TestDeleteNull

SQL Case Statements

2008-03-29T10:38:00.000-07:00

SQL Case statement can be used in 2 forms:

SQL Case statement with simple expression to compare and get results.
SQL Case statement with search or comparison expression to get results.

1. SQL Case statement with simple expression:
You can use default Northwind Database to test the functionality and syntax of SQL case statement.


Syntax:

USE NORTHWIND

SELECT
'Title' =
CASE TITLEOFCOURTESY
WHEN 'MS.' THEN 'MISSUS'
WHEN 'MRS.' THEN 'MISTRESS'
WHEN 'MR.' THEN 'MISTER'
WHEN 'DR.' THEN 'DOCTOR'
ELSE ''
END,
TITLEOFCOURTESY,
FIRSTNAME,
LASTNAME
FROM EMPLOYEES

Output:

Title	TitleOfCourtsey	FirstName	LastName
Missus	Ms.	Nancy	Davolio
Doctor	Dr.	Andrew	Fuller
Missus	Ms.	Janet	Leverling
Mistress	Mrs.	Margaret	Peacock
Mister	Mr.	Steven	Buchanan
Mister	Mr.	Michael	Suyama
Mister	Mr.	Robert	King
Missus	Ms.	Laura	Callahan
Missus	Ms.	Anne	Dodsworth

2. SQL Case Statement with search comparison expressions:

Syntax:


USE NORTHWIND

SELECT
'COMMENT' =
CASE
WHEN  UNITPRICE IS NULL THEN 'NO PRICE DEFINED'
WHEN  UNITPRICE < 20 THEN 'LOW PRICE'
WHEN  UNITPRICE > 20 AND UNITPRICE < 30 THEN 'REASONABLE PRICE'
ELSE 'EXPENSIVE ITEM!!!'
END,
PRODUCTNAME,
UNITPRICE
FROM PRODUCTS

Output:

Comment	ProductName	UnitPrice
Low Price	Chai	18.00
Low Price	Chang	19.00
Low Price	Aniseed Syrup	10.00
Reasonable Price	Chef Anton's Cajun Seasoning	22.00
Reasonable Price	Chef Anton's Gumbo Mix	21.35
Reasonable Price	Grandma's Boysenberry Spread	25.00
Expensive Item!!!	Uncle Bob's Organic Dried Pears	30.00
Expensive Item!!!	Northwoods Cranberry Sauce	40.00

SQL SubSelect-SubQueries

2008-03-29T10:37:00.001-07:00


A sub query or sub select is a select statement that returns a single value 
output result and it can be nested inside other subquery or any SELECT, INSERT,
DELETE OR UPDATE statement.

Example:
USE NORTHWIND

SELECT P.PRODUCTNAME,
(SELECT CATEGORYNAME FROM CATEGORIES WHERE CATEGORYID = P.CATEGORYID)
FROM PRODUCTS P

Subquery used in the above example returns the category name of each product
in every tuple.



Example:

SELECT P.PRODUCTNAME, P.UNITPRICE, P.CATEGORYID
FROM
PRODUCTS P
WHERE
P.PRODUCTID = (SELECT PRODUCTID FROM PRODUCTS WHERE PRODUCTNAME='VEGIE-SPREAD')

Here subquery returns product id as single value to the main SQL query.



Example:

SELECT C.CATEGORYNAME,

(SELECT TOP 1 P.PRODUCTNAME FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID ORDER BY P.UNITPRICE DESC),

(SELECT TOP 1 P.UNITPRICE FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID ORDER BY P.UNITPRICE DESC),

(SELECT MAX(P.UNITPRICE) FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID)
/*TO VERIFY THE RESULT*/

FROM CATEGORIES C

Above example returns the maximum unit price of product, product name and category name.

SQL Count Distinct

2008-03-29T10:34:00.000-07:00

SQL Count Statement is used to count the total number of records saved in a particular table. SQL Count Statement returns total records including duplicates.

SQL Count (*) Statement does not take any other argument such bas column name to evaluate the result. It counts all columns whether they have duplicates or nulls.

E.g.:

USE NORTHWIND

SELECT COUNT (*) FROM ORDERS

Above SQL Query will return 830.

SQL Count (ALL [Column Name]) Statement returns the number of records including duplicates but excluding null value columns

E.g.:

SELECT COUNT (ALL CUSTOMERID) FROM ORDERS

SQL Count (DISTINCT [Column Name]) Statement returns unique, non null records.

E.g.:

SELECT COUNT (DISTINCT CUSTOMERID) FROM ORDERS

Above SQL Query will return 89.

SQL Server 2005 Cast-Convert Date Time Format

2008-03-29T10:30:00.000-07:00

Using CAST function in SQL Server 2005:

Syntax:

CAST (expression as datatype)

CAST function accepts expression name as column name or any SQL function that return expression or value and declare the datatype of the expression into which you want to cast/convert it.

Following are the SQL Server 2005 query syntax to cast DateTime into varchar:

E.g.:

USE Pubs

select cast(pubdate as varchar) from titles

select cast(getdate() as varchar)

Following are the MS SQL Server 2005 query Syntax to convert Time format:

SQL query to convert Time format into hh:mm:ss:

select convert(varchar, getdate(), 108)

SQL query to convert Time format into hh:mi:ss:mmm(24h):

select convert(varchar, getdate(), 114)

You can use datetime type column name instead of getdate() function.

E.g.:

USE PUBS

select convert(varchar,pubdate,108) from titles

select convert(varchar,pubdate,114) from titles

How To Use COALESCE And NULLIF To Prevent Updating Columns When A Parameter IS NULL Or A Default Value

2008-03-29T10:16:00.001-07:00

Let's say you have a proc like this

CREATE PROC prUpdateTable
@Salesman int = -1
AS

..........

If the user calls the proc like this exec prUpdateTable null then @Salesman will be null, if the user calls the proc like this exec prUpdateTable then the value of @Salesman will be -1. In both of this cases you don't want to change the value of the column. What can you do?
You can use a combination of NULLIF and COALESCE to handle this. Your update statement would look like this

UPDATE table
SET Column = COALESCE(NULLIF(@variable,-1),Column)

Here is some code to demonstrate that

CREATE TABLE #foo (id int,salesman int)
INSERT #foo VALUES(1,1)
INSERT #foo VALUES(2,1)
INSERT #foo VALUES(3,1)
INSERT #foo VALUES(4,1)

SELECT * FROM #foo
-------------
1 1
2 1
3 1
4 1

DECLARE @salesman int
SELECT @salesman = 5

--Column value will change to 5
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =1

--Column value won't change
SELECT @salesman = -1
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =2

--Column value won't change
SELECT @salesman = NULL
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =3

--Column value will change to 3
SELECT @salesman = 3
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =4

--And here is the output, as you can see when @salesman was -1 or NULL the table did not get updated
SELECT * FROM #foo
-------------
1 5
2 1
3 1
4 3

DROP TABLE #foo

As you can see only the first and the last update statement changed the value of the salesman column
Of course you would never do this if you were to update only one column, you would skip the update instead. If you have to update multiple columns then this is something you can use instead of writing a bunch of dynamic SQL or nested IF statements.

How To Use Self Join In Sql Server 2000 2005

2008-03-29T10:14:00.000-07:00

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.

For self join in sql you can try the following example:

Create table employees:

emp_id	emp_name	emp_manager_id
1	John	Null
2	Tom	1
3	Smith	1
4	Albert	2
5	David	2
6	Murphy	5
7	Petra	5

Now to get the names of managers from the above single table you can use sub queries or simply the self join.

Self Join SQL Query to get the names of manager and employees:

select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Result:

manager	employee
John	Tom
John	Smith
Tom	Albert
Tom	David
David	Murphy
David	Petra

Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.

from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

Delete Duplicate rows from the table.

2008-03-28T07:27:00.000-07:00

Delete Duplicate rows from the table.

Suppose there is a table called "EmployeeTable" which have some duplicate records. There is a three way to delete the duplicate rows. First way to delete duplicate rows : Select distinct into Emp_Temp_Table from EmployeeTable* In the above line we are inserting all the distinct row of the "EmployeeTable" to another table "Emp_Temp_Table" (Emp_Temp_Table will create automatically when you use the above query.) Actuall the above query create clone of EmployeeTable and insert all the distinct row inside the Clone Table (Emp_Temp_Table). drop table EmployeeTable sp_rename 'Emp_Temp_Table',EmployeeTable' Then Delete the orginal table and rename the clone table with the name of orginal table. Second way to delete duplicate rows : Select distinct into Emp_Temp_Table from EmployeeTable* Truncate table EmployeeTable insert into EmployeeTable select from Emp_Temp_Table* drop table Emp_Temp_Table Third way to delete duplicate rows : Populate the new Primary Key Alter table EmployeeTable add NewPK int NULL Go Declare @intCounter int Set @intCounter = 0 Update EmployeeTable SET @intCounter = NewPK = @intCounter + 1 Select name,RecCount=count(), PktoKeep = max(NewPK) Into #dupes From EmployeeTable Group by name Having count() > 1 Order by count(*) desc Delete dupes except one Primary key for each dup record Delete test from EmployeeTable a join #dupes d a.name where a.NewPK not in (select PKtoKeep from #dupes) Remove the NewPK column ALTER TABLE test DROP COLUMN NewPK go drop table #dupes

How To Copy Data and Structure of One Table To Another New Tables

2008-03-27T13:36:00.000-07:00

select * into newtable from originaltable where 1=1

what Is Cursor

2008-03-26T08:25:00.000-07:00

The basic syntax of a cursor is:

DECLARE @AuthorID char(11)
 
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

 PRINT @AuthorID

 FETCH NEXT FROM c1
 INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.

The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.

What is Triggers

2008-03-26T08:07:00.000-07:00

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.

The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.

SET NOCOUNT ON

CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')

-- Results --

Apr 28 2001  9:56AM

Using EXISTS

2008-03-26T07:47:00.000-07:00

Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a records. Using the EXISTS keyword is a great way to accomplish this.

Here's a simple example from the pubs database using EXISTS:

if EXISTS (select *
 from authors
 where au_id = '172-32-1176')
 Print 'Record exits - Update'
ELSE
 Print 'Record doesn''t exist - Insert'

The EXISTS function takes one parameter which is a SQL statement. If any records exist that match the criteria it returns true, otherwise it returns false. This gives you a clean, efficient way to write a stored procedure that does either an insert or update.

Returning Complex Data from User-Defined Functions

2008-03-26T07:34:00.000-07:00

Returning multiple values from a UDF can be done fairly easily in SQL Server, but we must return those values as a virtual table. The following very simple ParseEmail() function accepts an email address as an argument, parses it, and returns the username and domain separately:


create function EmailParse (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
    declare @i int

    select @i = charindex('@', @email,1);

    if (@i > 1)
        insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
    else
        insert into @t values (null,null)

    return
end

how to use

select username, domain
from   EmailParse('test@test.com')

What is Stored procedure?

2008-03-13T10:20:00.000-07:00

A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.

They allow modular programming.
They allow faster execution.
They can reduce network traffic.
They can be used as a security mechanism.

What are the differences between UNION and JOINS?

2008-03-13T10:17:00.000-07:00

A join selects columns from 2 or more tables. A union selects rows.

How many types of Joins?

2008-03-13T10:12:00.000-07:00

oins can be categorized as:

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.
Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:
- LEFT JOIN or LEFT OUTER JOIN -The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
- RIGHT JOIN or RIGHT OUTER JOIN - A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
- FULL JOIN or FULL OUTER JOIN - A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
Cross joins - Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products. (A Cartesian join will get you a Cartesian product. A Cartesian join is when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself.)

Write a SQL Query to find first day of month?

2008-03-13T10:10:00.000-07:00

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

# Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.

2008-03-13T10:08:00.000-07:00

SELECT CEILING (7.1)

Write a query to convert all the letters in a word to upper case

2008-03-13T10:03:00.000-07:00

SELECT UPPER('test')

Find top salary among two tables

2008-03-13T10:02:00.001-07:00

SELECT TOP 1 sal
FROM (SELECT MAX(sal) AS sal
FROM sal1
UNION
SELECT MAX(sal) AS sal
FROM sal2) a
ORDER BY sal DESC

How to delete the rows which are duplicate (don’t delete both duplicate records).

2008-03-13T10:00:00.000-07:00

SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0

Find duplicate rows in a table?

2008-03-13T09:58:00.000-07:00

Find duplicate rows in a table? OR I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated.
SELECT sid, mark, COUNT(*) AS Counter
FROM marks
GROUP BY sid, mark
HAVING (COUNT(*) > 1)

How to know how many tables contains empno as a column in a database?

2008-03-13T09:05:00.000-07:00

SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'empno')

T-SQL Queries

2008-03-13T09:03:00.000-07:00

There is a table which contains the names like this. a1, a2, a3, a3, a4, a1, a1, a2 and their salaries. Write a query to get grand total salary, and total salaries of individual employees in one query.

SELECT empid, SUM(salary) AS salary
FROM employee
GROUP BY empid WITH ROLLUP
ORDER BY empid

T-SQL Queries

2008-03-13T09:02:00.001-07:00

There is a table day_temp which has three columns dayid, day and temperature. How do I write a query to get the difference of temperature among each other for seven days of a week?

SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference
FROM day_temp a INNER JOIN
day_temp b ON a.dayid = b.dayid + 1
OR
Select a.day, a.degree-b.degree from temperature a, temperature b where a.id=b.id+1

T-SQL

2008-03-13T08:38:00.000-07:00

There are two employee tables named emp1 and emp2. Both contains same structure (salary details). But Emp2 salary details are incorrect and emp1 salary details are correct. So, write a query which corrects salary details of the table emp2

update a set a.sal=b.sal from emp1 a, emp2 b where a.empid=b.empid

T-SQL Queries

2008-03-13T08:32:00.000-07:00

2 tables

Employee	Phone
empid empname salary mgrid	empid phnumber

Select all employees who doesn't have phone?
SELECT empname
FROM Employee
WHERE (empid NOT IN
(SELECT DISTINCT empid
FROM phone))

Select the employee names who is having more than one phone numbers.
SELECT empname
FROM employee
WHERE (empid IN
(SELECT empid
FROM phone
GROUP BY empid
HAVING COUNT(empid) > 1))

Select the details of 3 max salaried employees from employee table.

SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC
Display all managers from the table. (manager id is same as emp id)

SELECT empname
FROM employee
WHERE (empid IN
(SELECT DISTINCT mgrid
FROM employee))
Write a Select statement to list the Employee Name, Manager Name under a particular manager?

SELECT e1.empname AS EmpName, e2.empname AS ManagerName
FROM Employee e1 INNER JOIN
Employee e2 ON e1.mgrid = e2.empid
ORDER BY e2.mgrid

T-SQL: Leap Year Check

2008-03-12T09:23:00.001-07:00

There are various techniques to determine whether a given year is a leap year. The most common way which is widely taught at schools and universities is to detect whether the given year is divided by 4, 100 and not by 400, something like that.

Below is a small T-SQL function that checks for leap year while it uses more smart - shifting technique, which I advice to use where possible.

ALTER FUNCTION F_BIT_LEAP_YEAR
(@p_year SMALLINT)
RETURNS BIT
AS
BEGIN
   DECLARE @p_leap_date SMALLDATETIME
   DECLARE @p_check_day TINYINT

   SET @p_leap_date = CONVERT(VARCHAR(4), @p_year) + '0228'
   SET @p_check_day = DATEPART(d, DATEADD(d, 1, @p_leap_date))
   IF (@p_check_day = 29)
       RETURN 1

   RETURN 0 
END

Use this way:

SELECT dbo.F_BIT_LEAP_YEAR(2003)

T-SQL: Optional Parameters in Select Query (Using ISNULL)

2008-03-12T09:13:00.000-07:00

Suppose, you have to make search in the database table having optional parameters. For example you have some application with advanced search UI where user does not have to fill all search criteria. We can make it with one small trick - using ISNULL function.

CREATE PROC Search
(
   @param1 INT = NULL
  ,@param2 INT = NULL
)
AS
BEGIN

  SELECT column1
    FROM table1
   WHERE column1 = ISNULL(@param1, column1)
     AND column2 = ISNULL(@param2, column2)

END

If the 1st param in ISNULL function is null - it returns 2nd parameter.

HowTo Convert Table Values to Comma-Delimited String

2008-03-12T09:11:00.000-07:00

We have 2 tables related as one-to-many. How can we fetch parent table field, and child table values comma-delimited ? The function below demonstrates a technique how to convert table values into comma-delimited string with one query.

ALTER FUNCTION F_GET_STR
(@p_order_id int)
RETURNS varchar(1000)
AS
   BEGIN

   DECLARE @p_str VARCHAR(1000)
   SET @p_str = ''

   SELECT @p_str = @p_str + ',' + CAST(productid AS VARCHAR(6))
     FROM [order details]
    WHERE orderid = @p_order_id

   RETURN @p_str




END

Use this way:

SELECT orderid ,dbo.F_GET_STR(orderid) FROM orders

Select Middle Record

2008-03-12T09:04:00.000-07:00

SELECT TOP 1 query in T-SQL helps to find the first or the last record of the table data sorted by some criteria. But what if we need to find exactly middle record entry in the table ? Below is a small T-SQL query snippet that demonstrates a technique how to get middle record in a single query

SELECT TOP 1 UserId
FROM (SELECT TOP 50 PERCENT UserId
FROM addressbook WITH (NOLOCK)
ORDER BY UserId ASC) AS T1
ORDER BY 1 DESC

How TO Find age from date of birth

2008-03-12T08:16:00.000-07:00

SELECT FLOOR(DATEDIFF(DAY, @BirthDate, @TargetDate) / 365.25)

Get only Date from DateTime without performing any

2008-03-12T08:14:00.000-07:00

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

How can I find the last day of the month

2008-03-12T08:08:00.001-07:00

SELECT DATEADD(ms, -5, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))

How To get the comma delimited list:

2008-03-12T08:05:00.000-07:00

DECLARE @EmailList nvarchar(100)
SELECT @EmailList = COALESCE(@EmailList,'') + COALESCE(Email,'') + ',' FROM addressbook
WHERE Email IS NOT NULL
SET @EmailList = SUBSTRING(@EmailList,0,LEN(RTRIM(@EmailList)))

PRINT @EmailList

Returning String Instead of NULL:

2008-03-12T08:00:00.000-07:00

Consider a situation in which your database table contains NULL and you don't want to return NULL but some message. Like suppose you have a Person table and a Phone Table and a person does not have a phone number so we can easily return a message saying "No Phone Number Found" instead of returning a NULL.

SELECT P.Name, 'PhoneNumber'
= CASE WHEN Ph.PhoneNumber IS NULL THEN
'No Phone Number Exists'
ELSE
Ph.PhoneNumber
END
FROM tblPerson P, tblPhone Ph
WHERE P.PersonID = Ph.PersonID

You can also use the ISNULL method of the SQL SERVER 2000 to do the same operation. Check out the code below which returns the same result.

SELECT P.Name, Ph.PhoneNumber, ISNULL(Ph.CellNumber,'No Phone Number') AS CellNumber
FROM tblPerson P, tblPhone Ph
WHERE P.PersonID = Ph.PersonID

User Define Function For Converting Column Value in Row Format

2008-03-11T10:05:00.000-07:00

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[udf_GetAddressBookTitle](@AddressId int)
RETURNS @TitleTbl TABLE (Title varchar(1000))
AS

BEGIN

Declare @TitleTblVal varchar(50)
Declare @TitleCombinedVal varchar(1000)
Declare getAllTitleOfAddressBook cursor for
Select Title From AddressBookTitle Where AddressId = @AddressId
Open getAllTitleOfAddressBook
Fetch Next From getAllTitleOfAddressBook Into @TitletblVal
Set @TitleCombinedVal = ''
While (@@FETCH_STATUS=0)
BEGIN
Set @TitleCombinedVal = @TitleCombinedVal + @TitleTblVal +'
'
Fetch Next From getAllTitleOfAddressBook Into @TitletblVal
END
Insert Into @TitleTbl values(@TitleCombinedVal)

RETURN

END

How To Split String in SQL

2008-03-11T10:04:00.000-07:00

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
-- following line added 10/06/04 as null
-- values cause issues
IF @String IS NULL RETURN
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

If there are 1 millinon rows ,how would you remove the data without using delete and truncate.

2008-03-10T03:09:00.001-07:00

The method can be like dropping the table and recreating the structure again

There is an employee table having dep_id field and another department master table.List aa dept not having any employee

2008-03-10T03:08:00.003-07:00

Select dept_id from department where dept_id not in(select dept_id from employee)

What is Trigger?

2008-03-10T03:08:00.001-07:00

Triggers are a special form of stored procedure that comes into effect when a user gives a change command such as insert ,delete,update to a specified table or column
Triggers are often used to enforce referential integrity

Find 3rd max salary

2008-03-10T03:07:00.006-07:00

Count() will give the numer of non-null values in sxp.

Select salary from sal s
Where 2=(select count(8) from sal s1 where s.salary
The same co-related query cabn be used to find the nth max salary the number with the where clause should be n-1 for .

Define having , order by ,group by

2008-03-10T03:07:00.005-07:00

Having- clause set the condintion on the group by clause.
Order By-Order by clause allows sorting of query results by one or more columns.each sort can be either asc of des.

Group by – Group By is used in select statement to divide the output of table into groups.

1transcation log
every changes to the databse whether it is the result of a single ipdate stm or a grouped set of sql stm automatically record in the system table.this table is called tranc log.

Types of constraint

2008-03-10T03:07:00.003-07:00

1. constraint
2. primary key constrain
3.check constrain
4.referential integrity constraint (foreign key)

what are index?

2008-03-10T03:07:00.001-07:00

Indexs help sql server locate data.they speed up the data retrieval by pointing sql server to the location of table column;’s data on disk.There are tow type of index.

Clustered index-clustered index force sqlserver continually sort and re-sort the rows of table so that physical order is always Same as the logical order.you cab have only one cluster index per table.

Non_cluster index-Do not require the physical order of rows to be the same as their index order.each non clusterd index can provide access to the data in a different order.you can have 249 nonclusterd index per table

how do you delete duplicates rows from a table

2008-03-10T03:06:00.005-07:00

1. copy the structure of base_temp into temp_table

select * into temp_table from base_temp where 1=0
2. create a unique index on the columns that contains the duplicate rows with the ignore_dup_key attribute .this may be more columns the key for the table.

Create unique index temp_idx on temp_table(col1,col2…..) with igmore_dup_key
3. Now insert base_table into temp_table
Insert temp_table select * from base_table
Truncate the base table and copy the data in temp_table to base_table

what are joins and what are the types of join

2008-03-10T03:06:00.003-07:00

join links two or more tables by comparing values in specified columns different types of joins are
1. Equi join/inner join
2. Natural join
3. Outer join

can we use aggregate function in the “where clause”

2008-03-10T03:06:00.001-07:00

No However the aggregate functions can be used in a having clause.

what is a cursor ? whay do you need cursor.

2008-03-10T03:05:00.002-07:00

Cursor is a symbolic name that is associated with t-sql select statement through a declarative statement it consist of following parts.
• Cursor result set-a set of row resulting from the excecution of a query assosicated with the cursor.
• Cursor position –pointer to one row within the cursor result set.
• Cursor can be used to access data row by row.

what are the ypes of locking available in Sybase.

2008-03-10T03:05:00.001-07:00

Page Locak

Shared Lock

Exclusive Lock

Table Lock

Intent Lock

what is bcp

2008-03-10T03:04:00.000-07:00

bcp is a unix bulk copy utility used to transfer data from file to table and vice versa.
Ex
1.bcp dbname..tablename out pub_out –c –t ,-r userid –servername
user will be prompted for database password.
In the above example the data from publisher’s table present in pubs2 database is copied to physical file pub_out
2
-c option copies out the data from publisher table in character format
-t option terminator ends each field with comma.
-r row terminator ends each field with return

2.bcp..tablename in pub-out –c –t, -r userid –servername

user will be prompted for database password
above example 2 shows how to copy the data into adaptive server.

How do you delete all records from the file that has 1 million records when the transaction log is full

2008-03-09T22:09:00.000-07:00

Use Truncate instead of delete.as this is a minimally logged operation,there may be just enough
space in the transaction log to do the operation.truncate log only the datapage de-allocation not the deletion
of individual records.it is not a non-logged operation