Performance TIPS - II

Hi,

Let's continue with our Performance tips series - II.

Avoid Functions/Procedures with SQL statement using Group By clause:
Functions / Procedures should not use Group By clause in SQL statement. Although whenever we need to group the result we need to use GROUP by clause.

Avoid long table names: We must not use very long table names. We must limit the table name with X characters.

Avoid tables not involved in foreign keys: We must avoid refering tables which don't take part in foreign keys. Whenever we refer to these then we need to find a path to match data and get relevant or required data, so we must avoid referring to tables without foreign keys.

Avoid using functions in Select and Where clause: Whenever we use function in Select statement of Where clause it will be called for each & every row, which will result in a very slow query. Functions like ltrim(), substring() or any user function will result in low performance.

Avoid Functions / Procedures with queries on too many Tables: Avoid Functions / Procedures with queries on more than 4 Tables. Queries with more than 4 Tables is considered complex. Changing the threshold value requires Metric Assistant configuration update. This means that we should not join more than 4 queries but when we follow normalization and create multiple tables, its difficult to have only 4 tables in the query. We may have to refer to more than 4 tables to achieve required result.

Avoid Functions and Procedures doing an Insert, Update or Delete without managing a transaction: Avoid Stored Procedures doing an Insert, Update or Delete and not managing a transaction (execute "begin tran" if @@trancount=0). We must always perform and INSERT, UPDATE or DELETE in a transaction, so that the data is always consistent.

Never use an undocumented feature: We must never use an undocumented feature of sql server because there is no notification when it will be removed from future version, which may cause big maintenance problem.\

Truncate Table statement: Always avoid using Truncate Table statement because when this command is used, the data is not logged. Because where clause can't be used with Truncate, it will delete all data. Unless BEGIN TRANS and ROLLBACK is used, nothing can be recovered. So be sure when to use Truncate table statement.

Avoid tables without clustered Index: We must always have clustered index except for table with very little data much data. Having clustered index always help retrieve result faster.

Avoid cursors: Whenever there is a scenario where we need to loop through data for processing, we just simply create a cursor without evaluating overall impact. Consider a scenario when we need to run nested loop using cursor, we need to maintain the state of the outer loop. There can be alternatives like CTE (Common Table Expressions) or Temp tables with Identity columns and then get the minimum and maximum values to run a loop. There can be other alternatives also.

Avoid cursors inside a loop: As its mentioned in the previous point that we must avoid cursors, but if we are using it make sure its not used in a loop.

Avoid using GOTO statement: As we already know we must always avoid GOTO statement but its true that we always encounter situation when we can't avoid using GOTO. In simple words, use it only when there is no alternative.

Avoid SQL queries not using the first column of a composite index in the
WHERE clause: Whenever we write query we must keep in mind the sequence in which composite index is created. We must use the fields in where clause as per the defined index sequence.

Select * from table: Whenever we write query we must avoid using "select *", we must refer to individual fields.

UNION ALL Vs UNION: When we need to combine 2 resultsets to generate 1 resultset and we know that both resultset are distinct we must use UNION ALL rather than UNION because UNION internally uses DISTINCT after the result is returned.


Do write your comments.

Regards

Performance TIPS - 1

Guys,

Today I would like to share some performance tips, which I got while we ran an analysis tool on our code. I will share this in parts, so here is part - 1.

Stored Procedure naming convention - prefix control: Names of Procedures should start with X. The prefix must not start with sp because sp carries an overhead. If we call a procedure with sp_, it will first search the MASTER database before searching the current database. By default, only members of the dbo_owner role and db_ddladmin role can create stored procedures. Members of the dbo_owner role can give other users the ability to create procedures using a GRANT statement.

Avoid Functions and Procedures doing an Insert, Update or Delete without including error management: Avoid stored procedures or functions to Insert, Update or Delete without error management. You should use @@error for SQL 2000 and BEGIN TRY and BEGIN CATCH for SQL 2005. You will have full control and can handle all exceptions (both application and system) easily and update user accordingly.


Avoid Stored Procedures not returning a status value: We must avoid using a stored procedure which does not return anything. There should be a RETURN status.


Avoid Functions / Procedures with a complex SELECT clause: Avoid Functions / Procedures with a SELECT clause returning more than 9 columns or with a ´SELECT *´ query. Such queries are considered complex. Changing threshold value requires a Metric Assistant configuration update. In practical scenario its generally not possible to restrict the result to 9 columns but we must try to follow wherever we can.

Avoid Functions / Procedures with SQL statement including Subqueries: Functions / Procedures should not use SQL statement including Subqueries (at least subqueries should be avoided). We generally use subqueries, we have to use subqueries, we can take output into a table variable then we can use join to avoid subquery.

Avoid using temporary Objects:Triggers, Views, Functions and Procedures should not use temporary Objects (except temporary Tables). We can use temp tables / table variables.

Watch for more tips later on. Do write your comments and let me know what else you want to know.

Regards,

Sudhir

Script to extract objects available on a Linked Server

Hi,

In a scenario where we need to find out all the objects of a LINKED Server and check which object is accessible to us, we need to write following query:

Declare @min int,@max int
Declare @sql varchar(100)
Declare @table_server varchar(100)
Declare @table_catalog varchar(100)
Declare @table_schema varchar(100)
Declare @tablen varchar(100)


Set @table_server = 'LINKEDSERVER' --Name of Linked Server (as per sys.servers table)
Set @table_catalog = 'DATABASENAME' --Database Name
Set @table_schema = 'dbo' --Database owner

--Now create a table variable with identity column to help loop through.

Declare @table table
(Id int identity(1,1),
table_cat varchar(100),
Table_schem varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100))

--Insert data into table variable
Insert into @table
exec sp_tables_ex --Use sp_tables_ex procedure
@table_server = 'LINKEDSERVER',
@table_catalog = 'DATABASENAME',
@table_schema = 'dbo'

select * from @table --Check if anything is populated
set nocount on

--As this populate, remove SYSTEM TABLES
Delete from @table where Table_Type = 'SYSTEM TABLE' 

--Extract Min & Max value to run loop on the table variable.
Select @min = min(id), @max=max(id) from @table

while (@min<=@max)
Begin

--Extract Table name to refer from @table table variable.
Select @tablen = Table_Name from @table where id = @min

--Prepare dynamic query
Set @sql = 'Select top 10 * from ' + @table_server + '.' + @table_catalog + '.' + @table_schema + '.' + @tablen

print @sql
begin try
--Execute dynamic query in TRY block to check for permission
exec(@sql)
end try
begin catch
--If come here then we know this query is not accessible.
print @sql + ' not worked'
End catch
set @min = @min + 1 --Increment loop
End

Let me know if you have any question.

Regards,

Happy SQL Coding

SQL SERVER: Row Constructor, Declare - Initialization and Assignment features of SQL Server 2008

SQL SERVER: Row Constructor, Declare - Initialization and Assignment features of SQL Server 2008

Date and Time data type in SQL Server 2008

Hi,

Today let's discuss about Date & Time data type introduced in SQL Server 2008.

We always had to do extra steps to remove time from Age field or DOB field because we don't need time. Also while comparing 2 dates, we need to remove Time so that we get correct data.

All this extra work or steps will not be required because SQL Server 2008 has introduced 4 new (DATE RELATED) data types :

DATE

TIME

DATETIME2

DATETIMEOFFSET

All these data types separate date and time, support larger date range, improved accuracy and support for timezone. DATE data type stores only Date, TIME data type stored only time. DATETIME2 data type is an improved version of Datetime data type, which is improved to provide better accuracy and a larger date range. DATETIMEOFFSET data type adds time zone component in the date.

Let's check the size of each data type:

Date: Storage in bytes = 3 Accuracy = 1 day
Time: Storage in bytes = 3-5 Accuracy = 100 nanoseconds
Datetime2: Storage in bytes = 6-8 Accuracy = 100 nanoseconds
DateTimeOffSet: Storage in bytes = 8-10 Accuracy = 100 nanoseconds

I would like to highlight one point in this:
The date format is 'YYYY-MM-DD' for all the data types but because date format is language dependent, it is possible when you change the language the format is also changed.

DATE Data Type

Date Only
01-01-0001 to 31-12-9999

TIME Data Type

Time Only, Variable Precision - 0 to 7 decimal places for seconds To 100 nanoseconds

DATETIME2 Data Type

01-01-0001 to 31-12-9999
Variable Precision - to 100 nanoseconds, a datetime type w/ larger fractional seconds and year range than the existing DATETIME type

DATETIMEOFFSET

01-01-0001 to 31-12-9999
Variable Precision - to 100 nanoseconds
Time Zone Offset (From UTCTime) Preserved
Not Time Zone Aware - No Daylight Saving Time Support

The three new types that contain a time component (TIME, DATETIME2, and DATETIMEOFFSET) enable you to specify fractional seconds precision in parentheses following the type name. The default is 7, meaning 100 nanoseconds. If you need a fractional second accuracy of milliseconds, such as three for example, you must explicitly specify it: DATETIME2(3).

declare @e datetime2(3)
set @e=GETDATE()
select @e
------------------------------
2009-07-17 11:28:44.090
------------------------------

declare @d datetime2(5)
set @d=GETDATE()
select @d

------------------------------
2009-07-17 11:28:44.09000
------------------------------

declare @f datetime2(7)
set @f=GETDATE()
select @f

------------------------------
2009-07-17 11:28:44.0900000
------------------------------

The precision changes as per the specified size.

In the sys.columns view, the precision attribute describes the total number of characters in the default literal string representation of the value, and the scale describes the number of digits in the fractional part of the seconds. In INFORMATION_SCHEMA.COLUMNS, the DATETIME_PRECISION attribute describes the number of digits in the fractional part of the seconds.

Let's look at examples of these:

DECLARE
@d AS DATE = '2009-02-12',
@t AS TIME = '12:30:15.1234567',
@dt2 AS DATETIME2 = '2009-02-12 12:30:15.1234567',
@dto AS DATETIMEOFFSET = '2009-02-12 12:30:15.1234567 +02:00';

SELECT @d AS [@d], @t AS [@t], @dt2 AS [@dt2], @dto AS [@dto];

Here is the output:
@d = 2009-02-12
@t = 12:30:15.1234567
@dt2 = 2009-02-12 12:30:15.1234567
@dto = 2009-02-12 12:30:15.1234567 +02:00

Functions associated with Date data types:

To support the new date and time data types, SQL Server 2008 introduces new functions and enhanced existing functions. The new functions are SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET.

SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET return the current system date and time value.
SYSDATETIME returns the current date and time as a DATETIME2 value,
SYSUTCDATETIME returns the current date and time in UTC as a DATETIME2 value, and SYSDATETIMEOFFSET returns the current date and time along with the system time zone as a DATETIMEOFFSET value.

To get only the current date or only the current time, you can cast the value returned from the SYSUTCDATETIME function to DATE or TIME, as the following example shows:

SELECT
CAST(SYSDATETIME() AS DATE) AS [current_date],
CAST(SYSDATETIME() AS TIME) AS [current_time];

current_date current_time
2009-07-17 11:12:24.9059210

NOTE: When You convert a datetime datatype to DATE, SQL Server query optimizer still relies on index ordering to process the query more efficiently. This is contrary to the usual behavior, where conversion of a column to a different type prevents the optimizer from relying on index order. The new behavior means that the optimizer might consider an index seek for a query filter that has a conversion to DATE. For example, the plan for the following query performs an index seek on the index on the CurrencyRateDate DATETIME column:

USE AdventureWorks;
SELECT FromCurrencyCode, ToCurrencyCode, EndOfDayRate
FROM Sales.CurrencyRate
WHERE CAST(CurrencyRateDate AS DATE) = '20040701';

FromCurrencyCode ToCurrencyCode EndOfDayRate
USD ARS 3.5501
USD AUD 1.7763
USD BRL 3.6949
USD CAD 1.5758
USD CNY 8.2872
USD EUR 0.9962
USD GBP 0.6343
USD JPY 120.586
USD MXN 10.255
USD SAR 3.7541
USD USD 1.00
USD VEB 1362.0302

The structure of the table is:
Column_name Type
ScaleCurrencyRateDate datetime


The SWITCHOFFSET function adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC value.

SELECT SYSDATETIMEOFFSET()
2009-07-17 11:19:42.7380350 +08:00

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');
2009-07-16 22:19:42.7380350 -05:00

The TODATETIMEOFFSET function sets the time zone offset of an input date and time value. The main purpose of the TODATETIMEOFFSET function is to convert types that are not time zone aware to DATETIMEOFFSET by the given time zone offset. If the given date and time value is a DATETIMEOFFSET, the TODATETIMEOFFSET function changes the DATETIMEOFFSET value based on the same original local date and time value plus the new given time zone offset.

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-05:00');
2009-07-17 11:21:09.3356644 -05:00

This function is different from SWITCHOFFSET in several ways. First, it is not restricted to a DATETIMEOFFSET value as input; rather it accepts any date and time data type. Second, it does not try to adjust the time based on the time zone difference between the source value and the specified time zone but instead simply returns the input date and time value with the specified time zone as a DATETIMEOFFSET value.

SELECT TODATETIMEOFFSET(SYSDATETIME(), '-05:00');
2009-07-17 11:22:31.9018632 -05:00

DATEADD, DATEDIFF, DATENAME, and DATEPART functions are updated to add support for microseconds and nanoseconds. Many other functions are enhanced to support new date and time types, among them are, type conversion functions (CAST and CONVERT), set and aggregate functions (such as MAX, MIN), metadata functions (such as TYPEPROPERTY, COLUMNPROPERTY), and system functions (such as DATALENGTH, IS_DATE).

Hope this information is useful. Do let me know if you have any question.

Regards,

Happy SQL Coding

Row Constructor, Declare - Initialization and Assignment features of SQL Server 2008

Hi,

Today we will discuss a few new features of SQL Server 2008.

Declare and Initialize variable + compound assignment operators
Row Constructors

Let's start with Declare and Initialize variables part:

SQL Server 2008 allows you to declare and variable and initialise it at the same time without writing SET statement. It enables to initialize variable inline as part of the variable declaration statement instead of using separate DECLARE and SET statments.

Let's look at a few examples:

Declare @TDate Date = getdate()
select @TDate

output = 2009-07-13

Declare @TTime Time = getdate()
select @TTime

output = 09:23:38.1900000

Compound assignment operators help to assign a value to a column without using the traditional way set @var = @var + 1. The various operators are:

+= (Plus Equals)
-= (Minus Equals)
*= (Multiply Equals)
/= (Divide Equals)
%= (Modulo Equals)

These can be used wherever assignment or any simple calculation needs to be done using a numeric variable. Let's look at a few examples:

Declare @intval int=0
set @intval+=10
select @intval

output = 10

DECLARE @price AS MONEY = 10.00;
SET @price += 2.00;
SELECT @price;

output = 12.00

DECLARE @cost AS MONEY = 10.00;
SET @cost -= 2.00;
SELECT @cost;

output = 8.00

Declare @minvar int=1
Declare @maxar int=10
while (@minvar <= @maxar) BEGIN print 'IN LOOP' Set @minvar+=1 END 
output =
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP
IN LOOP


--An example of fabonacci series:

Declare @output numeric(20,2)=1;
Declare @maxar int=4
while (@maxar>1)
BEGIN
print 'IN LOOP'
Set @output*=@maxar
set @maxar-=1
END
select @output

output = 24.00 

Row Constructor

Now Let's look at Row constructor or Table value constructor. SQL Server 2008 introduces support for table value constructors through the values clause. We can now insert muliple rows in a table using single VALUES clause.

If a developer of DBA ask the following question:

I don’t want to write INSERT INTO statement for every row. Please help me what to do?

The answer is: USE ROW CONSTRUCTORS

You can avoid INSERT statements for each row. Internally, these are stored in a “constant table” that contains the rows. Only one query is executed. This can speed up some operations, but you should measure for your application to see if this works well for you.

Let's look at an example to understand this feature.

USE AdventureWorks;

IF OBJECT_ID('dbo.Cust', 'U') IS NOT NULL
DROP TABLE dbo.Cust;

CREATE TABLE dbo.Cust
(
custid INT NOT NULL,
companyname VARCHAR(25) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
CONSTRAINT PK_Cust PRIMARY KEY(custid)
);

INSERT INTO dbo.Cust(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

Select * from dbo.Cust


Output =
custid companyname phone address
1 cust 1 (111) 111-1111 address 1
2 cust 2 (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
4 cust 4 (444) 444-4444 address 4
5 cust 5 (555) 555-5555 address 5

One thing to mention here is that even though no explicit transaction is defined here, this INSERT statement is considered as an atomic operation. So if any row fails to enter the table, the entire INSERT operation will fail.

A table value constructor can be used to define table expressions such as key derived tables and CTEs, and can be used where table expressions are allowed (such as in the FROM clause of a SELECT statement or as the source table in a MERGE statement). The following example demonstrates using the VALUES clause to define a derived table in the context of an outer SELECT statement:

SELECT *
FROM
(VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5')
) AS C(custid, companyname, phone, address);

The outer query can operate on this table expression like any other table expression, including joins, filtering, grouping, and so on.

Hope it must be clear.

Do let me know if there is any question.

Enjoy SQL coding

OUTPUT Command

Hi,

Today we will look at OUTPUT command. OUTPUT command is equivalent to a trigger where you can get the old and new values using INSERTED & DELETED tables. This works similarly to trigger but you don't have to create a trigger to get the job done.

The OUTPUT command returns information about rows affected by an INSERT, UPDATE or DELETE command. The result set can be returned to the calling aplication and used for requirements such as archiving or logging / auditing.

In general, the output of INSERT, UPDATE or DELETE is taken into a table variable & then can be used to save it permanently, if required.

Let's look at an example:

Create table OutputSample
(
ID int,
age int,
name varchar(100)
)

--Now lets insert value in the OUTPUTSAMPLE table
Insert into OutputSample values (1,10, 'test')
Insert into OutputSample values (2,15, 'test1')
Insert into OutputSample values (3,20, 'test2')

Select * from OutputSample

--Now let's implement OUTPUT with INSERT statement

--Create a table variable
DECLARE @tabs table (Id int, age int, Name varchar(100))

--Insert into table using OUTPUT - INSERT statement
Insert into OutputSAMPLE
OUTPUT Inserted.ID, Inserted.age, Inserted.Name into @tabs --Putting data into Table variable
values(4,35,'Another One')

Select * from @tabs --check out the values of table variable

select * from OutputSAMPLE --Check out data from Main table

--You can use the same to insert data in a normal table...



--Now let's see an example using UPDATE statement

--Declare a table variable
DECLARE @tabs table
(Id int, age int, Name varchar(100), oldname varchar(100))

--Update the value
Update OutputSAMPLE
Set name='Updated Another One'
OUTPUT Inserted.ID, Inserted.age, Inserted.Name, Deleted.Name into @tabs
Where ID=4

Select * from @tabs --time to check output

Select * from OutputSAMPLE

When the OUTPUT clause is used for an UPDATE command, both DELETED and INSERTED table are available, DELETED table contains the values before the update and the INSERTED table contains the values after the update.




--Now let's see an example using DELETE statement

--Create a table variable
DECLARE @tabs table (Id int, age int, Name varchar(100))


DELETE outputSample
OUTPUT Deleted.ID, Deleted.age, Deleted.Name into @tabs
Where Id=1

Select * from @tabs --check output

Select * from OutputSample

Do let me know if there are any comments.

Enjoy SQL Coding.

Extract User group & its database information

Hi,

Following script can be used with SQL 2000, 2005 & 2008. For example if you have 10 users in current database, it will retrieve all Group information along with default database, if the user has a login account else if will return a blank. This also eliminates system users (DBO, SYS, INFORMATION_SCHEMA and GUEST). Although we can use sp_helpuser procedure but it will return all the user's information including system users. A loop is used to get information per user rather than all users.

Begin
--Create a temp table that will hold the main result. This script will check your version, if SQL 2005 or 2000 or 2008,
--because sp_helpuser return 6 values in SQL 2000 and 7 values in SQL 2005 / 2008.
Create table #tmpUserPerm
(UserName varchar(100),
GroupName varchar(100),
LoginName varchar(100),
DefDBName varchar(100),
UserId int,
SID varbinary(100),
DefSchemaName varchar(100) null)

Declare @name varchar(100)
Declare @ver varchar(100)
Declare @maxval int
Declare @minval int

--Create a temp table that will store all users except DBO and GUEST. If you want all users then --you can remove "and name not in ('DBO', 'GUEST','INFORMATION_SCHEMA', 'SYS')" from ---the following statement.

select identity(INT, 1,1) as ID, uid, name into #TmpUser from sysusers
where issqluser = 1 and hasdbaccess <> 0 and name not in
('DBO', 'GUEST', 'INFORMATION_SCHEMA', 'SYS')

Select @maxval = max(ID), @minval = min(ID) from #TmpUser

--Execute the below query to get current version of SQL SERVER

set @ver = convert(varchar(100),SERVERPROPERTY('productversion'))

if (left(@ver,2) = '9.' or left(@ver,2) = '10') --If SQL 2005 or 2008 then
begin
--Run a loop for all users
While (@minval <= @maxval)
BEGIN
Select @name = name from #TmpUser where ID = @minval
--Get data from sp_helpuser for current value of user (@NAME)
insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName,    DefSchemaName, UserId, SID)
Exec sp_helpuser @name
Set @minval = @minval+1
END

END
else --If SQL SERVER 2000 or other
begin
--Run cursor for all the user names
While (@minval <= @maxval)
BEGIN
Select @name = name from #TmpUser where ID = @minval
--Get data from sp_helpuser for current value of user (@NAME)
insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName,   DefSchemaName, UserId, SID)
Exec sp_helpuser @name
Set @minval = @minval+1
END
end

--Now show data & drop temp tables.
select * from #tmpUserPerm order by 1
drop table #Tmpuser
drop table #tmpUserPerm

end

Let me know if you have any comments.

Regards,

Sudhir

TABLESAMPLE in SQL Server 2005

Hi,

Let's look at TABLESAMPLE clause of SQL Server 2005. This clause returns a random, representative sample of the table expressed as either an approximate number of rows or a percentage of the total rows. TABLESAMPLE returns a result set containing a sampling og rows from all rows processed by the query.

select * from cd_country TABLESAMPLE (10 ROWS)
or
select * from cd_country TABLESAMPLE (10 PERCENT)

The sample of rows retrieved is different every time. The tablesample clause can't be used with views or in an inline table-values function.

Using Repeatable clause

Adding a REPEATABLE clause returns the same sample result set each time as long as no changes are made to the data in the table. Repeatable indicates that the selected sample can be returned more than once. If the same seed is ised, the same rows will be returned each time the query is run as long as no changes have been made to the data in the table.

select * from cd_country TABLESAMPLE (10 ROWS)  Repeatable (5)
or
select * from cd_country TABLESAMPLE (10 PERCENT) Repeatable (5)

Enjoy SQL

Top Statement in T-SQL

Hi,

Today we will discuss TOP statement of T-SQL. What all we can do with TOP statement and where all we can apply TOP clause?

Top clause is mandatory whenever we need to use order by statement while creating a view.
The TOP clause limits the number of rows returned in a result set. SQL Server 2005 has added usage of expression in TOP clause earlier it used to be a constant value. The TOP clause can be used in SELECT, INSERT, UPDATE and DELETE statements.

Suppose we have a table with duplicate rows, earlier we used to come up with a quite complex query to retrieve that single row, either using SET ROWCOUNT 1 or some other query, now

DELETE top 1 from table.

This will delete the 1st row from the table.

TOP used as expression

Declare @t int
Set @t = 10
Select top @t from table.

Declare @v int
set @v=100
Select top @v/@t from table


--Update statment

update top (1) test --table name
set name='aa'

This will update 1st row of the table and set the name ='aa'

With TIES example:

Select top 10 * WITH TIES from table 1 order by Employee ID

With TIES clause causes the SELECT TOP statement to return an additional X rows where EmployeeID is same as EmployeeID of 4th field.

Enjoy SQL coding.

Take a look at new features of SQL Server 2005

Let's list down some of the new features of SQL Server 2005 & discuss all one by one in detail:

.NET Common Language Runtime integration: This means you can write stored procedures, functions, triggers, user-defined types, and even new aggregates in any .NET language.

Native HTTP SOAP Access: This means that the database server can serve up data via Web Services with no additional middleware at all, not even an instance of IIS.

New xml data type: You can store XML data natively in its own column type now.

XQuery support: SQL Server supports XQuery for querying against XML data, including extensions that allow XML DDL operations.

Improved Error Handling: T-SQL now supports TRY-CATCH blocks for modern error handling.

Query Notifications: With query notifications, an external application can request the database engine to notify it when the data in a cached result set changes. This can dramatically lessen the load from requerying.

Failover clustering now extends to 8 nodes on 32-bit systems, and 4 nodes on 64-bit systems.

Database mirroring introduces a new reliability model for hot standby on a database-by-database basis.

Users now have acess to a database during a restore operation, within certain limits. This helps get you up and running faster after problems.

Multiple Active Result Sets (MARS): SQL Server 2005 introduces the ability for multiple statements to return result sets at the same time on a single connection. MARS enables you to execute multiple queries yielding multiple results, over a single connection. An application can move between open result sets as needed. The performance and scalability benefits are obvious.
This new trick is courtesy of the new ADO.NET, in tandem with SQL Server 2005's ability to accommodate multiple active commands. Since MARS is part SQL Server 2005 and part ADO.NET 2.0, it is only available if you're using both.

Recursive Queries and Common Table Expressions:CTE is a view build especially for a query and used in one shot: each time we execute the query. In one sense it can be called a "non persistent view". The basic use of a CTE is to make clear some expression that contains a query twice or more in a complex query.

PIVOT and UNPIVOT Operators: Use PIVOT and UNPIVOT relational operators to manipulate a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where necessary on any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

APPLY, CROSS APPLY and OUTER APPLY Operators: The APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression. You specify APPLY in the FROM clause of a query, similar to the way you use the JOIN relational operator. APPLY comes in two forms: CROSS APPLY and OUTER APPLY.
CROSS APPLY invokes a table-valued function for each row in an outer table expression. You can refer to columns in the outer table as arguments to the table-valued function. CROSS APPLY returns a unified result set out of all of the results returned by the individual invocations of the table-valued function. If the table-valued function returns an empty set for a given outer row, that outer row is not returned in the result. OUTER APPLY is very similar to CROSS APPLY, but it also returns rows from the outer table for which the table-valued function returned an empty set. Null values are returned as the column values that correspond to the columns of the table-valued function.

Database snapshots:A database snapshot is a static, read-only copy of a source database that gives you a consistent view of a database as of a certain point in time. You can use the snapshot for operations supported by a read-only database, such as reporting applications. You can create multiple database snapshots on a source database. You can recover the source database to the point when the snapshot was created from a database snapshot. However, when recovering from a snapshot, the source database must be available. Database snapshots are available only in the Enterprise Edition of Microsoft SQL Server 2005 and all recovery models support database snapshots.

Ranking functions: Ranking functions return a ranking value for each row in a partition. Depending on the function used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
RANK()
NTILE()
DENSE_RANK()
ROW_NUMBER()

Transact-SQL Enhancements: New Metadata Views

Transact-SQL Enhancements: TOP Enhancements: TOP is enhanced in the following major ways:
You can now specify a numeric expression returning the number or percentage of rows to be affected by your query, optionally using variables and subqueries.
You can now use the TOP option in DELETE, UPDATE, and INSERT queries.

Transact-SQL Enhancements: TABLESAMPLE The TABLESAMPLE clause limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. You can use TABLESAMPLE to quickly return a sample from a large table when either of the following conditions is true:
1.The sample does not have to be a truly random sample at the level of individual rows.
2.Rows on individual pages of the table are not correlated with other rows on the same page.
SELECT FirstName, LastName FROM Person.Contact TABLESAMPLE (10 PERCENT) ;

Varchar(max) Data Type: The max specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data. These are similar to text, ntext & image data types in previous versions.

Output Keyword: Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable.

DDL Triggers: DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. DDL triggers, like regular triggers, fire stored procedures in response to an event. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP.

Let's look at Merge in SQL Server 2008

Hi,

As you must be aware of the various new features introduced in SQL Server 2008, one of them is MERGE statement.

What is the purpose of Merge:

•MERGE lets you INSERT, UPDATE, AND DELETE in a single operation (i.e. single pass of the data set)
•You provide a source and target table
•Conceptually, this is a full outer join between the two tables and lets you provide commands for rows that match, rows only in the source, and rows only in the target

Actually the NOLOCK and READUNCOMMITTED hints are ignored on any update to an access method to prevent corruption. The original “ignore” decision was likely a result of unintentional support left in the product in an earlier version coupled with not wanting to break customers’ scripts. MERGE just extends the practice to be consistent with I/U/D.

MERGE is in many ways a more direct representation of the internal query algebra to the user. There’s one operator to do I/U/D and there is an $ACTION column that tells the operator what to do. You can see this more easily in MERGE. It translates down to an I/U/D in the query execution operators.

Merge Operations based on :
–WHEN MATCHED
–WHEN [TARGET] NOT MATCHED
–WHEN SOURCE NOT MATCHED

You can specify commands for at least 1 and at most all of these cases and define what should happen (INSERT, UPDATE, DELETE). The Source table can be any table source, target table can be an updateable view but not be a remote table or distributed partitioned view


Merge statment is great for large tables (ex: data warehouse population) where one-pass transforms can give a performance boost. It also can simplify T-SQL logic and push it into the database engine

Let's look at some Restrictions:
–NOLOCK, READUNCOMMITED disallowed (to maintain index integrity)
–Join condition should only be on the matching columns to avoid semantic issues
–Target Table can not be a remote table or distributed partitioned view. Source table can be any row source.


Recommendation-
Experiment with this before a wide-scale deployment. It’s not intended for use in all places where I/U/D are done.

Some more facts on Merge statment:
MERGE statement is transactional
–No explicit transaction required
One Pass Through Tables
–At most a full outer join
–Matching rows = when matched
–Left-outer join rows = when target not matched
–Right-outer join rows = when source not matched


You can specify multiple WHEN clauses
–For MATCHED and SOURCE NOT MATCHED
–Only one WHEN clause for TARGET NOT MATCHED
•MERGE can be used with any table source
•A MERGE statement causes triggers to be fired once
•Rows affected includes total rows affected by all clauses

Let's look at an example:
Here is the code to create and populate the target and source tables:SET NOCOUNT ON;

-- Create Target Table
CREATE TABLE Customers (LastName VARCHAR(50), FirstName VARCHAR(50));
INSERT INTO Customers VALUES ('Doe', 'Jane');
-- Create Source Table
CREATE TABLE NewCustomers(LastName VARCHAR(50), FirstName VARCHAR(50));
INSERT INTO NewCustomers VALUES ('Doe', 'John');
INSERT INTO NewCustomers VALUES ('Smith', 'Doris');

Above we have created a Customers table with 1 record, and a NewCustomers table with 2 different records. The records in the NewCustomers table will be used to update and insert records in to the Customers table. The first record in the NewCustomers table is used to update the existing Customers record, where as the second record in the NewCustomers table will be inserted into the Customers table. To do the insert and update, we will use the following MERGE statement:-- Update and Insert into Customers

MERGE Customers AS C
USING NewCustomers AS NC
ON C.LastName = NC. LastName
WHEN MATCHED THEN
UPDATE SET C.FirstName = NC.FirstName
WHEN NOT MATCHED THEN
INSERT (LastName, FirstName) VALUES (NC.LastName,NC.FirstName);

The target table is identified by naming it immediately following MERGE statement, “Customers”. MERGE statement uses the “USING” clause to identify the source of the updates and inserts, which is the NewCustomers table. In order to identify which records need to update and which to insert we need to identify a key column to match on between the Customers and NewCustomers table. To accomplish this matching of records between the two tables the MERGE statement uses the “ON” clause just as you would do when you are joining two tables. we are joining the source and target tables based on the LastName column in source and target tables. The different “WHEN” criteria of the MERGE statement identify the conditions for when an update or insert is to be performed. When a record is found in both source (NewCustomers) and target (Customers) tables then an UPDATE statement is performed to update on the FirstName column. This condition meets the “WHEN MATCHED” criteria above. When a join does not find a record in the target (Customers) table for a record in the source (newCustomer), then this is the “WHEN NOT MATCHED” criteria is executed. When this occurs an INSERT statement is performed to insert the LastName and FirstName value into the Customers table. After I have run my MERGE statement, my updated Customers table contains the following records:

LastName FirstName
--------------------------------------------------
Doe John
Smith Doris

By looking at the output, the MERGE statement changed the FirstName column of the “Doe” record from “Jane” to “John”. The MERGE did an UPDATE to accomplish this by using the “MATCHED” criteria. The second record was created when the “NOT MATCH” criteria inserted a record.


What are the benefits of Using MERGE

The MERGE statement simplifies the amount of work it takes to implement insert and update logic. Now with a single statement you can easily implement insert, update, and delete logic to handle criteria for maintaining a table. The MERGE statement handles all the joining of the source and target. This minimizes the amount of code you need to write to handle merge logic.


The following example uses MERGE to update the ProductInventory table in the AdventureWorks sample database on a daily basis, based on orders that are processed in the SalesOrderDetail table. The Quantity column of the ProductInventory table is updated by subtracting the number of orders placed each day for each product in the SalesOrderDetail table. If the number of orders for a product drops the inventory level of a product to 0 or less, the row for that product is deleted from the ProductInventory table.

Take a look at following example:

USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO

CREATE PROCEDURE Production.usp_UpdateInventory
@OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED 
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty, 
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501'

Here $action means UPDATE, INSERT or DELETE. $action is specific to MERGE statement only. It is not available when OUTPUT command is used with a simple INSERT, UPDATE or DELETE command.


Enjoy.....