Archive for Microsoft SQL

Identity

14 Jul, 2006  No Comment

/*
Getting the current value of identity column of a table.
*/
SELECT
IDENT_CURRENT(‘Invoice’)

/* OR */

DBCC CHECKIDENT (invoice,NORESEED)

/*
Getting the incremental values of identity column of all tables from one database.
*/
USE training2005
SELECT TABLE_NAME,IDENT_INCR(TABLE_NAME)AS
 IDENT_INCR FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_INCR(TABLE_NAME)IS NOT NULL

/*
Getting the incremental value of identity column of a tables.
*/
SELECT TABLE_NAME,IDENT_INCR(TABLE_NAME)
 AS IDENT_INCRFROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_INCR(TABLE_NAME)
 IS NOT NULL ANDTABLE_NAME=‘Invoice’

/*
Getting the start values
of identity column of all tables from one database
*/
USE training2005
SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME)
AS IDENT_SEED FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_NAME)IS NOT NULL

/*
Getting the startvalue of identity column of a table.
*/
SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME)AS IDENT_SEED
FROM INFORMATION_SCHEMA.TABLESWHERE IDENT_SEED(TABLE_NAME) IS NOT NULL AND
TABLE_NAME=‘Invoice’

SQL 2k Tips : UPDATETEXT

4 Jul, 2006  1 Comment

I have received the email from Grace on Tuesday, February 01, 2005 4:07 PM. Wow! She was sharing some interesting things related to SQL.
Thank you so much, Grace!

Hi Micheal,

Do you know that ntext data type can’t do concatenation. To overcome this problem, we can use UPDATETEXT.
The following are the example of using UPDATETEXT.


DECLARE @ptrval binary(16)
Declare @strComments nvarchar(4000)

SET @strComments = 'comments'

SELECT @ptrval = TEXTPTR(rd.Comment)
FROM vyRequisitionDetails rd
WHERE rd.RequisitionID = 'REQPhyuHT02012005-Delhi'

UPDATETEXT vyRequisitionDetails.Comment @ptrval NULL 0 @strComments

Grace

EXEC & EXEC sp_executesql

4 Jul, 2006  7 Comments

EXEC and EXEC sp_executesql for Searching..


DECLARE @sql as nvarchar(100)
DECLARE @paraDOB datetime
SET @paraDOB = '1/1/1981'
SET @sql=N''
SET @sql=N'SELECT * FROM EmpMast'
SET @sql=@sql + ' WHERE DOB >= @paraDOB'
exec sp_executesql @sql,N'@paraDOB datetime',@paraDOB</code>

Cool.. isn’t it?
But there are one limitation of using sql_executesql. As this statement accept the NVARCHAR which has only 4000 words capacity, the SQL string muct be less than 4000 words. :(

But I got the solution to solve this problem. By using EXEC with VARCHAR variable is the solution for this problem.

Here is the example.

DECLARE @sql as varchar(100)
DECLARE @paraDOB datetime
SET @paraDOB = '1/1/1981'
SET @sql=''
SET @sql='SELECT * FROM EmpMast'
SET @sql=@sql + ' WHERE DOB >= ''' + CONVERT(NVARCHAR,@paraDOB) + ''''
EXEC (@sql)      ---- Note: ( ) is important.

When we use sp_executesql, we can pass hidden parameter. sp_executesql’s performance is better than EXEC.

Note: some people used to pass all parameters if they are using sp_executesql statement. Actually, it is NOT necessary to pass all parameters. The parameters are only needed to be passed when the value of parameters are needed to get back.