Using OVER() with Aggregate Functions

One of new features in SQL 2005 that I haven't seen much talk about is that you can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function. Unfortunately, it isn't especially powerful, and you can't do running totals with it, but it does help you make your code a little shorter and in many cases it might be just what you need.

From which types of statement can you get...

create an insert statement

Many times we are required to write lenghty insert statements for tables with many columns. This script will create an insert statement for u.It takes 2 parameters first tbale name and second the ordinal positions of the columns seperated by ','for example USP_GenerateInsert 'TabName' ,'1,2,3,4,5'Hope this is Helpful

Consider the following batch of T-SQL:

EXEC('SELECT CONVERT(int, ''abc'')')
PRINT...

Consider the following simplified method, used to transfer...

Extracting words with removal of punctuations

Extracting words of a string with removal of unwanted punctuations.This is a flexible function to extract words from a string and tested on both SQL 2000 and 2005As you see the logic of this function is very simple. Maybe the update statement seems strange to beginners because it does not update any field of underlying […]

The AND operator in T-SQL compares two boolean...

_Select Update

I made some modifications to D Simmon's original sp.  1. I added dynamic feature to query tables in other databases (not linked servers) from one sp. I know dynamic sql isn't preferable in most cases but this is the route I took for this.  2. I enabled table name to be sent with owner qualification […]

What does a FULL OUTER JOIN return?

CTE Performance

SQL Server 2005 brings a new feature to T-SQL with Common Table Expressions, which make it possible for more complex subquery expressions than in previous versions. Recursion is a common use and Peter He takes a look at how well these new structures perform.