Extracting Only Numbers from a String

Sometimes we may need to extract only numbers from a string.
The simple approach is to run a while loop on given string to check each and every character and extract it to get the result.
Here is one alternative approach:

Declare @strTemp varchar(100),@strResult varchar(100)
set @strTemp='1zxcv123asd5fqw4er'
set @strResult=''
select @strResult = @strResult+
case when number like '[0-9]' then number else '' end from
(select substring(@strTemp,number,1) as number from 
(select number from master..spt_values 
where type='p' and number between 1 and len(@strTemp)) as t
) as t 
select @strResult as [Numbers] 
go
Result:
112354

Disable a Job via TSQL

You can disable a Job either through Job ID or Job Name:
exec msdb..sp_update_job @job_id = 0xC0923E436928064EA33B46B2A47BFF61 , @enabled = 0


exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 0
You can also manipulate via job category:
UPDATE j
SET j.Enabled = 0
FROM MSDB.dbo.sysjobs j
INNER JOIN MSDB.dbo.syscategories c ON j.category_id = c.category_id
WHERE c.[Name] = 'Database Maintenance';
The other approach would be to add a step at the beginning of your other jobs which checks the status of the backup job and then either aborts or sleeps the current job if the backup is running.
Follow below procedure to Disable or Enable a SQL Server Job Programatically:
Step 1: Find the JobId 
SELECT job_id,name,description,enabled
FROM msdb.dbo.sysjobs
ORDER BY name ASC
Step 2: Update the enabled flag
– Disable SQL Job
DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 0
– Enable SQL Job
DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 1

Create/Drop Scripts for All Existing Foreign Keys

Script to Create Foreign Keys:
SELECT  'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'
        + OBJECT_NAME(F.parent_object_id) + ' ADD CONSTRAINT ' + F.name
        + ' FOREIGN KEY ' + '(' + COL_NAME(FC.parent_object_id, FC.parent_column_id) + ')'
        + ' REFERENCES ' + SCHEMA_NAME(RefObj.schema_id) + '.'
        + OBJECT_NAME(F.referenced_object_id) + ' ('
        + COL_NAME(FC.referenced_object_id, FC.referenced_column_id) + ')'
FROM    SYS.FOREIGN_KEYS AS F
        INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id
        INNER JOIN sys.objects RefObj ON RefObj.object_id = f.referenced_object_id
--WHERE   OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'


Script to Drop Foreign Keys
SELECT  'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'
        + OBJECT_NAME(F.parent_object_id) + ' DROP CONSTRAINT ' + F.name
FROM    SYS.FOREIGN_KEYS AS F
        INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id
--WHERE   OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

Get Tables List With Dependent Tables Name

Below script helps you to create all database tables list along with their dependent tables name in comma separated format.
DECLARE @MainTable VARCHAR(100)
DECLARE @TableFullName VARCHAR(100)
DECLARE @TablesName VARCHAR(1000)


CREATE TABLE #TmpTbl
    (TableCompName VARCHAR(100), TableName VARCHAR(1000))
DECLARE Tmp_Cursor CURSOR static
    FOR SELECT  s.name + '.' + o.name, o.name
       FROM sys.objects o INNER JOIN sys.schemas s 
       ON o.schema_id = s.schema_id
       WHERE type = 'U' ORDER BY s.name, o.name
OPEN Tmp_Cursor
      --FETCH
FETCH FIRST FROM Tmp_Cursor INTO @TableFullName, @MainTable
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @TablesName = COALESCE(@TablesName + ',', '') + s.name + '.'
           + OBJECT_NAME(FKEYID)
           FROM SYSFOREIGNKEYS INNER JOIN sys.objects o 
           ON o.object_id = SYSFOREIGNKEYS.fkeyid
           INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
           WHERE OBJECT_NAME(RKEYID) = @MainTable
        INSERT INTO #TmpTbl
                (TableCompName, TableName)
                SELECT @TableFullName, COALESCE(@TablesName, '')
        SELECT  @TablesName = NULL
        FETCH NEXT FROM Tmp_Cursor INTO @TableFullName, @MainTable
    END
SELECT TableCompName AS TableName, TableName AS DependentTables
FROM #TmpTbl
DROP TABLE #TmpTbl
CLOSE Tmp_Cursor
DEALLOCATE Tmp_Cursor

Find the SQL Server Port Number

Check the SQL error log by using the below TSQL:
sp_readerrorlog 1, 1, 'listening', 'server'


Here is another method for default instance:
DECLARE @findport_number nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =   'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @findport_number OUTPUT
print 'The server port number = '+@findport_number

View and Disable SQL Agent Jobs with TSQL

I can view all the SQL Agent Jobs in SSMS. But I want to Copy/Paste this in MS Excel. I Wrote the below script with the Help from sysschedules in BOL. 


Declare @Days Table (
      DayID      int
    , DayName VarChar(32)
);
Insert Into @Days
SELECT 1, 'Sunday' UNION ALL
SELECT 2, 'Monday' UNION ALL
SELECT 4, 'Tuesday' UNION ALL
SELECT 8, 'Wednesday' UNION ALL
SELECT 16, 'Thursday' UNION ALL
SELECT 32, 'Friday' UNION ALL
SELECT 64, 'Saturday';

With MyJob
As(
    SELECT Sch.Name AS 'SchName'
        , Sch.Schedule_ID
        , JobSch.Job_ID
        , CASE WHEN Sch.Freq_Type = 1 THEN 'Once' 
            WHEN Sch.Freq_Type = 4 
                AND Sch.Freq_Interval = 1 
                    THEN 'Daily'
            WHEN Sch.Freq_Type = 4 
                THEN 'Every ' + CAST(Sch.Freq_Interval AS VarChar(5)) + ' Days'
            WHEN Sch.Freq_Type = 8 THEN 
                Replace( Replace( Replace(( 
                    SELECT DayName 
                    From @Days AS x 
                    Where Sch.Freq_Interval & x.DayID <> 0 
                    Order By DayID For XML Raw)
                , '"/><row DayName="', ', '), '<row DayName="', ''), '"/>', '') 
                + CASE WHEN Sch.Dreq_Recurrence_Factor <> 0 
                        AND Sch.Dreq_Recurrence_Factor = 1 
                            THEN '; weekly' 
                    WHEN Sch.Dreq_Recurrence_Factor <> 0 THEN '; every ' 
                + CAST(Sch.Dreq_Recurrence_Factor AS VarChar(10)) + ' weeks' End
            WHEN Sch.Freq_Type = 16 THEN 'On day ' 
                + CAST(Sch.Freq_Interval AS VarChar(10)) + ' of every '
                + CAST(Sch.Dreq_Recurrence_Factor AS VarChar(10)) + ' months' 
            WHEN Sch.Freq_Type = 32 THEN 
                CASE WHEN Sch.Freq_Relative_Interval = 1 THEN 'First'
                    WHEN Sch.Freq_Relative_Interval = 2 THEN 'Second'
                    WHEN Sch.Freq_Relative_Interval = 4 THEN 'Third'
                    WHEN Sch.Freq_Relative_Interval = 8 THEN 'Fourth'
                    WHEN Sch.Freq_Relative_Interval = 16 THEN 'Last'
                END + 
                CASE WHEN Sch.Freq_Interval = 1 THEN ' Sunday'
                    WHEN Sch.Freq_Interval = 2 THEN ' Monday'
                    WHEN Sch.Freq_Interval = 3 THEN ' Tuesday'
                    WHEN Sch.Freq_Interval = 4 THEN ' Wednesday'
                    WHEN Sch.Freq_Interval = 5 THEN ' Thursday'
                    WHEN Sch.Freq_Interval = 6 THEN ' Friday'
                    WHEN Sch.Freq_Interval = 7 THEN ' Saturday'
                    WHEN Sch.Freq_Interval = 8 THEN ' Day'
                    WHEN Sch.Freq_Interval = 9 THEN ' Weekday'
                    WHEN Sch.Freq_Interval = 10 THEN ' Weekend'
                END
                + CASE WHEN Sch.Dreq_Recurrence_Factor <> 0 
                        AND Sch.Dreq_Recurrence_Factor = 1 THEN '; monthly'
                    WHEN Sch.Dreq_Recurrence_Factor <> 0 THEN '; every ' 
                + CAST(Sch.Dreq_Recurrence_Factor AS VarChar(10)) + ' months' End
            WHEN Sch.Freq_Type = 64 THEN 'StartUp'
            WHEN Sch.Freq_Type = 128 THEN 'Idle'
          END AS 'Frequency'
        , IsNull('Every ' + CAST(Sch.freq_subday_interval AS VarChar(10)) + 
            CASE WHEN Sch.freq_subday_type = 2 THEN ' seconds'
                WHEN Sch.freq_subday_type = 4 THEN ' minutes'
                WHEN Sch.freq_subday_type = 8 THEN ' hours'
            END, 'Once') AS 'SubFrequency'
        , Replicate('0', 6 - Len(Sch.active_start_time)) 
            + CAST(Sch.active_start_time AS VarChar(6)) AS 'startTime'
        , Replicate('0', 6 - Len(Sch.active_end_time)) 
            + CAST(Sch.active_end_time AS VarChar(6)) AS 'endTime'
        , Replicate('0', 6 - Len(JobSch.next_run_time)) 
            + CAST(JobSch.next_run_time AS VarChar(6)) AS 'nextRunTime'
        , CAST(JobSch.next_run_date AS char(8)) AS 'nextRunDate'
    From msdb.dbo.sysschedules AS sch
    JOIN msdb.dbo.sysjobschedules AS jobsch
        ON Sch.schedule_id = JobSch.schedule_id
    WHERE Sch.enabled = 1
)

Script to Disable the Job. 


SELECT Job.Name AS 'JobName'
    , Sch.SchName
    , Sch.Frequency
    , Sch.SubFrequency
    , SubString(Sch.StartTime, 1, 2) + ':' 
        + SubString(Sch.StartTime, 3, 2) + ' - ' 
        + SubString(Sch.EndTime, 1, 2) + ':' 
        + SubString(Sch.EndTime, 3, 2) 
        AS 'ScheduleTime' -- HH:MM
    , SubString(Sch.NextRunDate, 1, 4) + '/' 
        + SubString(Sch.NextRunDate, 5, 2) + '/' 
        + SubString(Sch.NextRunDate, 7, 2) + ' ' 
        + SubString(Sch.NextRunTime, 1, 2) + ':' 
        + SubString(Sch.NextRunTime, 3, 2) AS 'NextRunDate'
      /* Note: the sysjobschedules table refreshes every 20 min, 
        so nextRunDate may be out of date */
    , 'Execute msdb.dbo.sp_update_job @job_id = ''' 
        + CAST(Job.job_id AS char(36)) + ''', @enabled = 0;' AS 'disableScript'
From msdb.dbo.sysjobs AS Job
Join MyJob AS sch
    On Job.Job_ID = Sch.Job_ID
Where Job.enabled = 1 -- do not display disabled jobs
Order By NextRunDate;

Search Suffix using Full Text Search in SQL Server 2005

A solution to search Suffix is given below as its a limitation in FTS that FTS cannot search suffix.
Its not recommended and not efficient as it requires another column but its a solution which can help you out in some cases.
1. Add a column in your table which stores the reverse of the string
like
SET NewColumnName = REVERSE(ColumnName)
2. CREATE PROCEDURE sps_searchSuffix(@searchString varchar(8000)) AS
SET @searchString = REVERSE(@searchString)
DECLARE @Q nVARCHAR(MAX)
SET @Q = 'SELECT * FROM TableName WHERE CONTAINS (ColumnName,''"'+@searchString+'*"'''+')'
EXEC SP_EXECUTESQL @Q
3. And call it like this if you want to search "garding" and you have a data like "regarding"
DECLARE @ST VARCHAR(500)
SET @ST = 'garding'
PRINT @ST
EXEC sps_searchSuffix @ST

Delete Duplicate Records Using Common Tabel Expression

There are many ways to delete the duplicates in a table but sometimes the group by doesnt seem to work so you can try out the new feature of Microsoft SQL Server "Common Table Expression"

With Dups as 

(

SELECT  row_number() over (

partition by ColumnName 

order by  ColumnName ) as RowNum

FROM Table

)

DELETE 

FROM Dups 

WHERE rownum > 1

Connecting to SQL Server Using SQLCMD Utility

Using the SQLCMD utility you can execute Transact-SQL statements, stored procedures or any other SQL commands from a command prompt instead of having to launch SSMS or some other GUI. This is a very effective utility available to SQL Server Database Administrators or Developers to run Simple or Ad-Hoc queries against a SQL Server database instance.


How
Open a Command Prompt window and browse to the location where the SQLCMD utility is available on your machine. By default you will be able to see the SQLCMD utility under “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\” location. The below examples show how to connect to the default instance of the Database Engine on port 1433, a named instance and a named instance listening on port 1933.  (You will need to substitute your server name and instance name to connect).
--Connection SQL Server using Windows Authentication
--Default Instance
SQLCMD -S SERVERNAME –E
--Named Instance
SQLCMD -S SERVERNAME\INSTANCENAME –E
--OR
SQLCMD -S SERVERNAME\INSTANCENAME,1933 –E


Once you are connected to the database engine using SQLCMD utility, copy and paste the sample blocking code query below in the SQLCMD window and press "Enter". Then type "GO" and press "Enter" to execute the query.


SELECT  session_id, blocking_session_id FROM sys.dm_exec_requests 
WHERE session_id > 50


To close a SQLCMD session type "EXIT" and press "Enter" as shown in the above snippet.


--Connecting SQL Server Using SQL Server Authentication
For this connection we need to also specify the -User and -Password parameters as well.


--Default Instance
SQLCMD -S SERVERNAME –U sa –P SQLPassword 
--Named Instance
SQLCMD -S SERVERNAME\INSTANCENAME –U sa –P SQLPassword 
--OR
SQLCMD -S SERVERNAME\INSTANCENAME,1933 –U sa –P SQLPassword 


Once you are connected to the database engine using SQLCMD utility you can run the above blocking query or any other query to test. 

Difference between CUBE and ROLLUP

Some time we need to summarize data very quickly without writing very complex query. CUBE And ROLL UP are great commands that give us very good summary options.
We can summarize the data without writing dirty and complex queries with the help of CUBE and ROLL Up Operators.
CUBE generates a result set that represents aggregates for all combinations of values in the selected columns.
ROLLUP generates a result set that represents aggregates for a hierarchy of values in the selected columns.
Example to see the difference
DECLARE @t TABLE(
ItemName varchar(100),
Color varchar(100),
Combinations int
)
insert into @t values ('Chair','Black',10)
insert into @t values ('Chair','Blue',10)
insert into @t values ('Table','Black',10)
insert into @t values ('Table','Blue',10)
GO
select ItemName,Color,SUM(Combinations) as TotalCnt from @t
Group by ItemName,Color with Rollup
GO
select ItemName,Color,SUM(Combinations) as TotalCnt from @t
Group by ItemName,Color with cube
GO
Rollup added below rows in addition to general group by rows.
Chair   NULL    20
Table   NULL    20
NULL    NULL    40


Cube added below rows in addition to general group by rows.
Chair   NULL    20
Table   NULL    20
NULL    NULL    40
NULL    Black   20
NULL    Blue    20


Difference between Cube and rollup is, cube calculated the sum of combinations w.r.t both Item and Color, where as rollup calculated the sum of combinations only w.r.t Item. In other words, CUBE evaluates aggregate expression with all possible combinations of columns specified in group by clause, where as the Rollup evaluates aggregate expressions in only the order of columns specified in group by clause.To easily remember this remind the shape of cube and rollup.

Temp Table Vs Table Variable

Temp tables behave same as normal tables and are bound by transactions. Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction. 


Transaction logs are not recorded for the table variables, hence they are out of scope of the transaction mechanism. While Transaction logs are recorded for the temporary table.


Any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. 


Table variables don't participate in transactions, logging or locking. This means they're faster.


Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option


You can create a temp table using SELECT INTO, which can be quicker to write and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.


Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.

SQL Server Denali : Choose

CHOOSE function returns a value from the given array based on the specified index position.

Syntax : Choose ([Position], [Value1],[Value2],…,[ValueN])

Here,
Position : The position number of the value to return. Position number starts from 1
[Value1],[Value2],…,[ValueN] : List of values.

Example :

SELECT CHOOSE(1, 'a', 'b', 'c') AS First,

CHOOSE(2, 'a', 'b', 'c') AS Second;

/*
First Second
----- ------
a     b
*/
-- If the index value exceeds the bounds of the array of values,
-- then CHOOSE returns null.

SELECT CHOOSE(0, 'a', 'b', 'c') AS First,

CHOOSE(4, 'a', 'b', 'c') AS Second

/*
First Second
----- ------
NULL  NULL
*/
-- If the provided index value has a numeric data type other than int,
-- then the value is implicitly converted to an integer

SELECT CHOOSE(2.5, 'a', 'b', 'c') AS First,

CHOOSE(3.9, 'a', 'b', 'c') AS Second;

/*
First Second
----- ------
b     c
*/


Example 2:
/* 
We can use Choose function in Select List, Group By Clause and Order By Clause also. 
*/

SELECT p.Name, SUM(s.OrderQty) SellQuantity, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th') AS Quarter

FROM Sales.SalesOrderDetail s INNER JOIN Production.Product p ON p.ProductID = s.ProductID

WHERE s.ModifiedDate >= '2006-01-01' AND s.ModifiedDate < ' 2007-01-01'

GROUP BY p.Name,CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

HAVING SUM(s.OrderQty) > 600

ORDER BY p.Name, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

If we look at the execution plan, we will see that Compute Scalar is used to support CHOOSE function. The engine has taken the CHOOSE function and converted it into a CASE.

SQL Server Denali: Get Last Date of Month

In SQL Server Denali CTP3 a new function is introduced, though which you can easily find out last date of any month. Prior to this we were using different methods to get last date of month. Following one, I like most.

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GetDate())+1,0))

But now it’s more easy with EOMONTH() function. Just provide a date in DateTime format or in string format and it will return last date of required month.

SELECT EOMONTH (GETDATE()) AS Result;

Or you can add number months to get last date.

SELECT EOMONTH (GETDATE(),3) AS Result;

SQL Server Denali: FORCESEEK and FORCESCAN

FORCESEEK and FORCESCAN are the new functions in SQL Server Denali. 
Before Denali, FORCESEEK hint exists and enhanced in Denali CTP3. FORCESCAN table hint is new addition. 
FORCESEEK: It forces optimizer to use index seek only. Sometimes optimizer does not user proper plan and use index scan which cause high reads on the system. We can use forceseek here to force otpimizer to use index seek which can give better performance.
FORCESCAN: Specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation. Here are few examples of queries with hints of FORCESEEK and FORCESCAN.


-- FORCESEEK
SELECT  ObjectName FROM ObjectItems WITH (FORCESEEK) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESEEK and specified index
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK,INDEX (IX_ObjectType_CreateDate)) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESEEK and specified index using at least the specified index columns
SELECT ObjectName FROM ObjectItems WITH (FORCESEEK(IX_ObjectType_CreateDate(ObjectType,CreateDate))) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESCAN
SELECT  ObjectName FROM ObjectItems WITH (FORCESCAN) WHERE ObjectType = 'SQL_STORED_PROCEDURE'


-- FORCESCAN and specified index
SELECT  ObjectName FROM ObjectItems WITH (FORCESCAN, INDEX(IX_ObjectId)) WHERE ObjectType = 'SQL_STORED_PROCEDURE'

Debug query in SSMS

In SQL Server 2008 its possible to debug queries in SQL Server Management Studio. It is very simple and easy.
In toolbar we have toolbar of Debug , if not we can open it as below



Now we will run below query in SQL Server Management Studio
DECLARE @I INT
SET @I = 1
WHILE @I <= 100
BEGIN
    PRINT @I
    SET @I = @I + 1
END     
Now start debugging as shown below

Open watch window as show below

Now create breakpoints in the query, by clicking at starting of the row.

Type Parameter @i in the Name field of watch window. Monitor the value of @i in the watch window.

Now I want to set its value to some new number.
See below how to modify it. Modify it and start debugging.

SQL Server Denali : Lead and Lag

LEAD() and LAG() are two interesting functions introduced in SQL Server Denali.


LEAD() can be used to access data from a subsequent row in the same result set and its counterpart LAG() function can be used to access data from a previous row in the result set without using a self-join. 


Syntax: LEAD/LAG (scalar_expression [,offset] [,default]) OVER ([partition_by_clause] order_by_clause)
/* Create Table */
CREATE TABLE ExamResults
(SubjectName varchar(25), Student  varchar(50), Number int)
-- Inserting sample records
INSERT INTO ExamResults 
VALUES
('Maths', 'Student 1', 45),
('Physics', 'Student 2', 45),
('Physics', 'Student 1', 50),
('Chemistry', 'Student 3', 20),
('Physics', 'Student 3', 35),
('Biology', 'Student 1', 20),
('Biology', 'Student 2', 60),
('Biology', 'Student 3', 65),
('Chemistry', 'Student 1', 75),
('Biology', 'Student 4', 30)
GO
/* Query using LEAD() and LAG() */
SELECT Student , SubjectName, Number, 
LAG(Number,1,0) OVER(PARTITION BY SubjectName ORDER BY Number) as LG,
LEAD(Number,1,0) OVER(PARTITION BY SubjectName ORDER BY Number) as LD
FROM ExamResults ORDER BY SubjectName, Number
GO
/*
OUTPUT :
Student      SubjectName   Number   LG  LD
-------------   --------------------  -----------   ----  ----
Student 1   Biology                20      0   30
Student 4   Biology        30    20   60
Student 2   Biology        60    30   65
Student 3   Biology        65    60     0
Student 3   Chemistry             20      0   75
Student 1   Chemistry        75    20     0
Student 1   Maths        45      0     0
Student 3   Physics        35      0   45
Student 2   Physics        45    35   50
Student 1   Physics        50    45     0
*/

SQL Server Denali: Select/Skip Top/Bottom N Rows

SQL Server Denali introduces query paging in which you can specify range of rows returned by a SELECT statement. This can be handy when you want to limit number of rows returned by the statement.
This is implemented in ORDER BY clause. two new keywords are added to ORDER BY clause:
1. OFFSET : Skips top N number of rows from the result set
2. FETCH : Fetch next N number of rows from the result set (after skipping rows specified by OFFSET)
1. Return bottom 15 rows only:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode DESC OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY
2. This will remove first 15 rows from the result:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode OFFSET 15 ROWS
3. Remove first 15 rows from the result, and return next 10 rows:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode OFFSET 15 ROWS FETCH NEXT 10 ROWS ONLY

Paging with using Offset/Fetch Next Rows of Order by Clause

Check out the following query. for example below i want to fetch row numbers from 101 to 200.
DECLARE @Rows INT = 100 -- Number of rows returns per page
DECLARE @PageNumber INT = 2 -- Page number
SELECT *
FROM  sys.objects
ORDER BY object_id
OFFSET (@Rows * (@PageNumber - 1)) ROWS
FETCH NEXT @Rows ROWS ONLY;
GO
Offset keyword is used to skip rows before retrieve any rows and Fetch next keyword is used to retrieve specified number of rows.

SQL Server Denali: DateFromParts()

DateFromParts() is a new function introduced in SQL Server Denali. It takes three input parameters Year, Month and Day and returns value in Date format. 

Syntax: DateFromParts(Year, Month, Day)

There are 5 more functions related to DateTime introduced in SQL Server Denali.

TimeFromParts() function returns output in TIME format.

Syntax: TimeFromParts(Hour, Minute, Seconds, Fractions, Precision)

SmallDateTimeFromParts() function returns output in SmallDateTime2 format.

Syntax: SmallDateTimeFromParts(Year, Month, Day, Hour, Minute)

DateTimeFromParts() function returns output in DATETIME format.

Syntax: DateTimeFromParts( year, month, day, hour, minute, seconds, milliseconds)

DateTime2FromParts() function returns output in DATETIME2 format.

Syntax: DateTime2FromParts(Year, Month, Day, Hour, Minute, Seconds, Fractions, Precision)

DateTimeOffSetFromParts() function returns output in datetimeoffset format.

Syntax: DateTimeOffSetFromParts(Year, Month, Day, Hour, Minute, Seconds, Fractions, Hour_offset, Minute_offset, Precision)