Official MS SQL

select range of data rows in MSSQL2005

noreply@blogger.com (Unknown) — Sat, 22 Aug 2009 12:10:00 -0700

We all know if we have to find range of data then we use sub queries but sql2005 provide row_number to find range of data

for ex.
if we have to select data from 21 row to 30 the n

SELECT * FROM
(
SELECT Row_Number() OVER (ORDER BY last_name, first_name) as rowid,
user_id,
last_name,
first_name
FROM user
) as a
where rowid >20 and rowid <31

same if i want 28 record the n

SELECT * FROM
(
SELECT Row_Number() OVER (ORDER BY last_name, first_name) as rowid,
user_id,
last_name,
first_name
FROM user
) as a
where rowid = 28

@@rowcount with MSSQL

noreply@blogger.com (Unknown) — Sat, 22 Aug 2009 11:55:00 -0700

@@ROWCOUNT returns the number of rows affected or read depending on the operation.

terms as follows

@@ROWCOUNT returns the number of rows returned to the user for a SELECT.
@@ROWCOUNT returns the number of rows affected after issuing a INSERT, DELETE, or UPDATE.

e.g

SELECT emp_firstname,emp_lastname from emp_details ;

out put is

emp_firstname emp_lastname

yogesh k

Jhon Bush

out put returns two rows

if we execute following after this the n

SELECT @@ROWCOUNT AS 'Rows previously affected';

then out put will be

Rows previously affected
------------------------
2

(1 row(s) affected

But if we execute query like

SELECT Count(*) as rowcount from emp_details
then

out put is

rowcount
-----------
2

(1 row(s) affected)

and we execute following after this
SELECT @@ROWCOUNT AS 'Rows previously affected';

then

out put is

Rows previously affected
------------------------
1

(1 row(s) affected)

Notice it tells you that it sent back only 1 row to the user.because only one row is return to user

dealy in sql or wait in sql query

noreply@blogger.com (Unknown) — Mon, 30 Jun 2008 04:10:00 -0700

In many application or process we required dealy

there is dealy in sql server
call WAITFOR

select '1'
WAITFOR DELAY '00:00:30'

above query run and wait 30 sec for output

select '2'
WAITFOR Time '15:00'

above query run and wait for 3:00pm for output

WAITFOR gives out put after end of process if you want out put after each wait then use code as follows

select getdate()
WAITFOR DELAY '00:00:30'
Go
select getdate()
WAITFOR DELAY '00:00:30'
Go
select getdate()
Go

alternate logic for delay is while loop but its not accurate
it gives differnt interval on different configuration server.

text file as data source

noreply@blogger.com (Unknown) — Sat, 28 Jun 2008 00:56:00 -0700

In many application we need Text file as datasource or as table

many ways to do this one of that as follows

create one folder on c drive name textfiles
and execute following code

EXEC sp_addlinkedserver txtdatasrc, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', 'c:\textfiles', NULL, 'Text'

now your link server with name " txtdatasrc" is created

execute following code

EXEC sp_tables_ex txtdatasrc

see there is no record because folder does not contain any text file .

add new text file name table1 to folder

and again run command

EXEC sp_tables_ex txtdatasrc

see there is table name "table1#txt" in out put

now you can select rows from table1#txt by using select statment as follows

select * from txtsrv...table1#txt

this link server is very useful to access text as datasource

there are many othere methods and tricks in this stuff we will discuss soon...

Nesting Stored Procedures

noreply@blogger.com (Unknown) — Mon, 16 Jun 2008 04:23:00 -0700

Nesting Stored Procedures

Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels. The nesting level increases by one when the called stored procedure or managed code reference begins execution and decreases by one when the called stored procedure or managed code reference completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling chain to fail. The current nesting level for the stored procedures in execution is stored in the @@NESTLEVEL function.

When a stored procedure executes managed code by referencing a CLR routine, type, or aggregate, this reference also counts as one level of nesting. Methods invoked from within managed code do not count against this limit. The current nesting level is returned by the @@NESTLEVEL function. When a CLR stored procedure performs data access operations through the Microsoft SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL and this level is reflected in the @@NESTLEVEL function.

An error in a nested stored procedure is not necessarily fatal to the calling stored procedure. When invoking stored procedures within stored procedures, use the Transact-SQL RETURN statement to return a return code and check the return code from the calling stored procedure. In this way, you can specify the behavior of your stored procedures when errors occur. For more information about using return codes, see Returning Data Using a Return Code.

Stored procedures can even do a nested call to themselves, a technique known as recursion.

Although the nesting limit is 32 levels, Microsoft SQL Server 2005 has no limit on the number of stored procedures that can be invoked from a given stored procedure, provided that the subordinate stored procedures do not invoke other subordinate stored procedures and the maximum nesting level is never exceeded.

How To get last updated Row from table ?

noreply@blogger.com (Unknown) — Mon, 16 Jun 2008 02:51:00 -0700

timestamp (Transact-SQL)

Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read. To return the current timestamp value for a database, use @@DBTS.

The Transact-SQL timestamp data type is different from the timestamp data type defined in the SQL-2003 standard. The SQL-2003 timestamp data type is equivalent to the Transact-SQL datetime data type.

rowversion is the synonym for the timestamp data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible. For more information, see Data Type Synonyms (Transact-SQL).

In a CREATE TABLE or ALTER TABLE statement, you do not have to specify a column name for the timestamp data type, for example:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);

insert into ExampleTable(PriKey) values(1)
insert into ExampleTable(PriKey) values(2)
insert into ExampleTable(PriKey) values(3)
insert into ExampleTable(PriKey) values(4)
Select MAX(timestamp) from ExampleTable
update ExampleTable set PriKey =110 where PriKey =3
Select MAX(timestamp) from ExampleTable

If you do not specify a column name, the Microsoft SQL Server 2005 Database Engine generates the timestamp column name; however, the rowversion synonym does not follow this behavior. When you use rowversion, you must specify a column name.

Duplicate timestamp values can be generated by using the SELECT INTO statement in which a timestamp column is in the SELECT list. We do not recommend using timestamp in this manner.

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

rowversion (Transact-SQL)

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. This property makes a rowversion column a poor candidate for keys, especially primary keys. Any update made to the row changes the rowversion value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible. For more information, see Data Type Synonyms (Transact-SQL).

The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

In a CREATE TABLE or ALTER TABLE statement, you do not have to specify a column name for the timestamp data type, for example:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);

If you do not specify a column name, the SQL Server Database Engine generates the timestamp column name; however, the rowversion synonym does not follow this behavior. When you use rowversion, you must specify a column name, for example:
CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion) ;

A nonnullable rowversion column is semantically equivalent to a binary(8) column. A nullable rowversion column is semantically equivalent to a varbinary(8) column.

You can use the rowversion column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the rowversion value is updated. If no change is made to the row, the rowversion value is the same as when it was previously read. To return the current rowversion value for a database, use @@DBTS.

You can add a rowversion column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time. You may also want to know how many rows and which rows were updated without re-querying the table.

how to get new inserted row id in sql

noreply@blogger.com (Unknown) — Mon, 16 Jun 2008 00:58:00 -0700

SCOPE_IDENTITY

Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Syntax

SCOPE_IDENTITY( )

Return Types

sql_variant

@@IDENTITY

Returns the last-inserted identity value.
Syntax

@@IDENTITY

Return Types

numeric

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY will return the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

USE tempdb
GO
CREATE TABLE TZ (
Z_id int IDENTITY(1,1)PRIMARY KEY,
Z_name varchar(20) NOT NULL)

INSERT TZ
VALUES ('Lisa')
INSERT TZ
VALUES ('Mike')
INSERT TZ
VALUES ('Carla')

SELECT * FROM TZ

--Result set: This is how table TZ looks
Z_id Z_name
-------------
1 Lisa
2 Mike
3 Carla

CREATE TABLE TY (
Y_id int IDENTITY(100,5)PRIMARY KEY,
Y_name varchar(20) NULL)

INSERT TY (Y_name)
VALUES ('boathouse')
INSERT TY (Y_name)
VALUES ('rocks')
INSERT TY (Y_name)
VALUES ('elevator')

SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
---------------
100 boathouse
105 rocks
110 elevator

/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
BEGIN
INSERT TY VALUES ('')
END

/*FIRE the trigger and find out what identity values you get
with the @@IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/

@@IDENTITY
115
/*@@IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier insert on TZ*/

IDENT_CURRENT

Returns the last identity value generated for a specified table in any session and any scope.
Syntax

IDENT_CURRENT('table_name')
Arguments

table_name

Is the name of the table whose identity value will be returned. table_name is varchar, with no default.
Return Types

sql_variant

IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

* IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

* @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

* SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

This example illustrates the different identity values returned by IDENT_CURRENT, @@IDENTITY, and SCOPE_IDENTITY.

USE pubs
DROP TABLE t6
DROP TABLE t7
GO
CREATE TABLE t6(id int IDENTITY)
CREATE TABLE t7(id int IDENTITY(100,1))
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
INSERT t7 DEFAULT VALUES
END
GO
--end of trigger definition

SELECT * FROM t6
--id is empty.

SELECT * FROM t7
--id is empty.

--Do the following in Session 1
INSERT t6 DEFAULT VALUES
SELECT @@IDENTITY
/*Returns the value 100, which was inserted by the trigger.*/

SELECT SCOPE_IDENTITY()
/* Returns the value 1, which was inserted by the
INSERT stmt 2 statements before this query.*/

SELECT IDENT_CURRENT('t7')
/* Returns value inserted into t7, i.e. in the trigger.*/

SELECT IDENT_CURRENT('t6')
/* Returns value inserted into t6, which was the INSERT statement 4 stmts before this query.*/

-- Do the following in Session 2
SELECT @@IDENTITY
/* Returns NULL since there has been no INSERT action
so far in this session.*/

SELECT SCOPE_IDENTITY()
/* Returns NULL since there has been no INSERT action
so far in this scope in this session.*/

SELECT IDENT_CURRENT('t7')
/* Returns the last value inserted into t7.*/

Varchar(Max) limit of max in Sql2005 ?

noreply@blogger.com (Unknown) — Tue, 3 Jun 2008 22:25:00 -0700

While developing one store proc i face problem of varchar(max)

my string was genreated more than 100000 charactes and by default varchar(max) size is 65,536

actual size of any max type is 2^30-1
if you place max text size is 1 then it set to max available size

ms sql optimization tips

noreply@blogger.com (Unknown) — Sun, 1 Jun 2008 21:45:00 -0700

Some Tips For optimization

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.

This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

Call stored procedure using its fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.