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