SQLServerCentral apologizes and you can win a book

Question

Post reply

SQLServerCentral apologizes and you can win a book

Press Release

SSCarpal Tunnel

Points: 4393
More actions
December 4, 2013 at 9:17 pm

#280762

Comments posted to this topic are about the item SQLServerCentral apologizes and you can win a book

Viewing 15 posts - 1 through 15 (of 287 total)

You must be logged in to reply to this topic. Login to reply

jim-1022345 Grasshopper Points: 17 More actions · Answer 1

Window Functions are a great addition to TSQL. I use them all the time now.

WITH RandomData AS

(

SELECT 1 AS Number

UNION ALL

SELECT Number + 1

FROM RandomData

WHERE Number < 200

)

SELECT

Number,

ROW_NUMBER() OVER(PARTITION BY Number%10 ORDER BY Number) AS AlternateNTile

FROM RandomData

ORDER BY 1

OPTION (MAXRECURSION 200)

inna.shapiro SSC Rookie Points: 30 More actions · Answer 2

Very usefull staff !

paging example :

with searchCTE as

(

selectCode,

Descript,

ROW_NUMBER() over ( order by Code ) as RowNoCode,

ROW_NUMBER() over ( order by CAST(Code as BIGINT)) as RowNoCodeNum,

ROW_NUMBER() over ( order by Descript ) as RowNoDescript

from #tblData

where--Do SEARCH

( @pSearchBy in ( 0, 2 ) and Code like @sTEMP )

or

( @pSearchBy in ( 1, 2 ) and Descript like @sTEMP )

)

select Code,

Descript

from

( select Code,

Descript,

case

when @pPagingBy = 0 and @pElemName = 'PermProfile' then RowNoCodeNum-- Paging by CodeNum

when @pPagingBy = 0 then RowNoCode-- Paging by Code

else RowNoDescript-- Paging by Descript

end as RowNo

from searchCTE

) T1

where@pPageNo = 0

or

( @pPageNo > 0 and

RowNo between (@pPageNo-1)*@pPageSize + 1 and @pPageNo*@pPageSize

)

order by RowNo;

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Plagiarism sucks. Here is a query I wrote to determine the best order for restoring backups to bring a database current. You can find the full post for the query here: http://www.sqlsoldier.com/wp/sqlserver/day3of31daysofdisasterrecoverydeterminingfilestorestoredatabase

Declare @DBName sysname,

@DBBackupLSN numeric(25, 0);

Declare @Baks Table (

BakID int identity(1, 1) not null primary key,

backup_set_id int not null,

media_set_id int not null,

first_family_number tinyint not null,

last_family_number tinyint not null,

first_lsn numeric(25, 0) null,

last_lsn numeric(25, 0) null,

database_backup_lsn numeric(25, 0) null,

backup_finish_date datetime null,

type char(1) null,

family_sequence_number tinyint not null,

physical_device_name nvarchar(260) not null,

device_type tinyint null)

Set NoCount On;

-- Set the name of the database you want to restore

Set @DBName = N'';

-- Get the most recent full backup with all backup files

Insert Into @Baks (backup_set_id,

media_set_id,

first_family_number,

last_family_number,

first_lsn,

last_lsn,

database_backup_lsn,

backup_finish_date,

type,

family_sequence_number,

physical_device_name,

device_type)

Select Top(1) With Ties B.backup_set_id,

B.media_set_id,

B.first_family_number,

B.last_family_number,

B.first_lsn,

B.last_lsn,

B.database_backup_lsn,

B.backup_finish_date,

B.type,

BF.family_sequence_number,

BF.physical_device_name,

BF.device_type

From msdb.dbo.backupset As B

Inner Join msdb.dbo.backupmediafamily As BF

On BF.media_set_id = B.media_set_id

And BF.family_sequence_number Between B.first_family_number And B.last_family_number

Where B.database_name = @DBName

And B.is_copy_only = 0

And B.type = 'D'

And BF.physical_device_name Not In ('Nul', 'Nul:')

Order By backup_finish_date desc, backup_set_id;

-- Get the lsn that the differential backups, if any, will be based on

Select @DBBackupLSN = database_backup_lsn

From @Baks;

-- Get the most recent differential backup based on that full backup

Insert Into @Baks (backup_set_id,

media_set_id,

first_family_number,

last_family_number,

first_lsn,

last_lsn,

database_backup_lsn,

backup_finish_date,

type,

family_sequence_number,

physical_device_name,

device_type)

Select Top(1) With Ties B.backup_set_id,

B.media_set_id,

B.first_family_number,

B.last_family_number,

B.first_lsn,

B.last_lsn,

B.database_backup_lsn,

B.backup_finish_date,

B.type,

BF.family_sequence_number,

BF.physical_device_name,

BF.device_type

From msdb.dbo.backupset As B

Inner Join msdb.dbo.backupmediafamily As BF

On BF.media_set_id = B.media_set_id

And BF.family_sequence_number Between B.first_family_number And B.last_family_number

Where B.database_name = @DBName

And B.is_copy_only = 0

And B.type = 'I'

And BF.physical_device_name Not In ('Nul', 'Nul:')

And B.database_backup_lsn = @DBBackupLSN

Order By backup_finish_date Desc, backup_set_id;

-- Get the last LSN included in the differential backup,

-- if one was found, or of the full backup

Select Top 1 @DBBackupLSN = last_lsn

From @Baks

Where type In ('D', 'I')

Order By BakID Desc;

-- Get first log backup, if any, for restore, where

-- last_lsn of previous backup is >= first_lsn of the

-- log backup and <= the last_lsn of the log backup

Insert Into @Baks (backup_set_id,

media_set_id,

first_family_number,

last_family_number,

first_lsn,

last_lsn,

database_backup_lsn,

backup_finish_date,

type,

family_sequence_number,

physical_device_name,

device_type)

Select Top(1) With Ties B.backup_set_id,

B.media_set_id,

B.first_family_number,

B.last_family_number,

B.first_lsn,

B.last_lsn,

B.database_backup_lsn,

B.backup_finish_date,

B.type,

BF.family_sequence_number,

BF.physical_device_name,

BF.device_type

From msdb.dbo.backupset B

Inner Join msdb.dbo.backupmediafamily As BF

On BF.media_set_id = B.media_set_id

And BF.family_sequence_number Between B.first_family_number And B.last_family_number

Where B.database_name = @DBName

And B.is_copy_only = 0

And B.type = 'L'

And BF.physical_device_name Not In ('Nul', 'Nul:')

And @DBBackupLSN Between B.first_lsn And B.last_lsn

Order By backup_finish_date, backup_set_id;

-- Get last_lsn of the first log backup that will be restored

Set @DBBackupLSN = Null;

Select @DBBackupLSN = Max(last_lsn)

From @Baks

Where type = 'L';

-- Recursively get all log backups, in order, to be restored

-- first_lsn of the log backup = last_lsn of the previous log backup

With Logs

As (Select B.backup_set_id,

B.media_set_id,

B.first_family_number,

B.last_family_number,

B.first_lsn,

B.last_lsn,

B.database_backup_lsn,

B.backup_finish_date,

B.type,

BF.family_sequence_number,

BF.physical_device_name,

BF.device_type,

1 As LogLevel

From msdb.dbo.backupset B

Inner Join msdb.dbo.backupmediafamily As BF

On BF.media_set_id = B.media_set_id

And BF.family_sequence_number Between B.first_family_number And B.last_family_number

Where B.database_name = @DBName

And B.is_copy_only = 0

And B.type = 'L'

And BF.physical_device_name Not In ('Nul', 'Nul:')

And B.first_lsn = @DBBackupLSN

Union All

Select B.backup_set_id,

B.media_set_id,

B.first_family_number,

B.last_family_number,

B.first_lsn,

B.last_lsn,

B.database_backup_lsn,

B.backup_finish_date,

B.type,

BF.family_sequence_number,

BF.physical_device_name,

BF.device_type,

L.LogLevel + 1

From msdb.dbo.backupset B

Inner Join msdb.dbo.backupmediafamily As BF

On BF.media_set_id = B.media_set_id

And BF.family_sequence_number Between B.first_family_number And B.last_family_number

Inner Join Logs L On L.database_backup_lsn = B.database_backup_lsn

Where B.database_name = @DBName

And B.is_copy_only = 0

And B.type = 'L'

And BF.physical_device_name Not In ('Nul', 'Nul:')

And B.first_lsn = L.last_lsn)

Insert Into @Baks (backup_set_id,

media_set_id,

first_family_number,

last_family_number,

first_lsn,

last_lsn,

database_backup_lsn,

backup_finish_date,

type,

family_sequence_number,

physical_device_name,

device_type)

Select backup_set_id,

media_set_id,

first_family_number,

last_family_number,

first_lsn,

last_lsn,

database_backup_lsn,

backup_finish_date,

type,

family_sequence_number,

physical_device_name,

device_type

From Logs

Option(MaxRecursion 0);

-- Select out just the columns needed to script restore

Select RestoreOrder = Row_Number() Over(Partition By family_sequence_number Order By BakID),

RestoreType = Case When type In ('D', 'I') Then 'Database'

When type = 'L' Then 'Log'

End,

DeviceType = Case When device_type in (2, 102) Then 'Disk'

When device_type in (5, 105) Then 'Tape'

End,

PhysicalFileName = physical_device_name

From @Baks

Order By BakID;

Set NoCount Off;

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

rdward-939670 Grasshopper Points: 21 More actions · Answer 4

Here is a sample for when you need a row number but don't care what it is ordered by:

SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), * FROM sys.objects

smutallib SSC Eights! Points: 968 More actions · Answer 5

-- The following script will generate time between given start time and end time

-- Row_number() window function at the end is used to generate serial no.

DECLARE @T_AVAILABLE_TIME TABLE(T_RUNTIME TIME,POST_DATE DATETIME)

DECLARE@START_TIME TIME,

@END_TIME TIME,

@Inc INT;

SET @Inc = 10; --Incrementing by 10 minutes

SET @START_TIME='09:00:00'

SET @END_TIME='11:00:00';

WITH Vals AS (

SELECT @START_TIME RunTime

UNION ALL

SELECT DATEADD(mi,@Inc,RunTime)

FROM Vals

WHERE DATEADD(mi,@Inc,RunTime) < @END_TIME

)

INSERT INTO @T_AVAILABLE_TIME(T_RUNTIME,POST_DATE)

SELECT LEFT(RunTime,8) AS RUNTIME,GETDATE()

FROm Vals

OPTION (MAXRECURSION 0)

SELECTROW_NUMBER() OVER(ORDER BY POST_DATE) AS SERIAL,

LEFT(T_RUNTIME,8) AS FROM_TIME,

LEFT(DATEADD(MI,10,T_RUNTIME),8) AS TO_TIME

FROM @T_AVAILABLE_TIME A

zsolt.miskolczi SSC Enthusiast Points: 106 More actions · Answer 6

Hi,

;WITH e1(n) AS

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1

), -- 10

e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10 = 100

e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2 AS c) -- 10*100 = 1.000

SELECT ROW_NUMBER() OVER (ORDER BY n) as mySequence

FROM e3 ORDER BY n ;

diego.perdices SSCommitted Points: 1701 More actions · Answer 7

A fun and different way to SELECT DISTINCT.

WITH DistinctCTE

AS (

SELECT [Column] = MIN(t.[Column])

FROM dbo.[Table] T

UNION ALL

SELECT R.[Column]

FROM (

SELECT T.[Column],

rn = ROW_NUMBER() OVER (ORDER BY T.[Column])

FROM dbo.[Table] T

JOIN DistinctCTE R

ON R.[Column] < T.[Column]

) R

WHERE R.rn = 1

)

SELECT *

FROM DistinctCTE

OPTION (MAXRECURSION 0);

davidadlington78 SSC Journeyman Points: 90 More actions · Answer 8

Would love a copy of the book!

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,

FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

sabinweb Hall of Fame Points: 3923 More actions · Answer 9

It is not ok to plagiarizing someone.

That person spent time in research, in testing, writing and then , another person comes and steals a hole work...

SELECT

low,

ROW_NUMBER() OVER(PARTITION by low ORDER BY number) as aRowNumber

FROM MASTER..spt_values

WHERE TYPE='p'

lien.revanth Grasshopper Points: 13 More actions · Answer 10

I like way when people publicly apologize when something went wrong.

Microsoft has given below simple definition which helps in understanding its intention to use

"the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results."

Sample query to get the rank of each row within the partitioned by parameter_id of a result set(to fetch password parameters from sys.all_parameters).

SELECT RANK() OVER(Partition by parameter_id order by max_length desc) as rank_, * from sys.all_parameters where name like '%@password'

Neil Burton SSC-Insane Points: 23043 More actions · Answer 11

I'd been pointed at Itzik's work to solve a problem I'd set myself so a copy of the book would be good.

Here's the code I used to deal with the first half of the problem.

create table #Orders (OrderID int)

insert into #Orders values

(111101),

(111102),

(111103),

(111105),

(111106),

(111110),

(111111),

(111112),

(111113),

(111119),

(111125)

;

with cte as(

select

row1 = ROW_NUMBER() over(order by sl.OrderID desc)

,sl.OrderID

from #Orders sl

group by sl.OrderID

)

select

Row = ROW_NUMBER() over(order by o1.row1)

,diff = o2.OrderID - o1.OrderID

,o1.OrderID

into #missing

from cte o1

join cte o2 on o1.row1 = (o2.row1+1)

where

(o2.OrderID - o1.OrderID) > 1

select * from #missing

drop table #missing,#Orders

Edit Spelling

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

kgresham SSC Enthusiast Points: 197 More actions · Answer 12

;with src(val) as (

select 1 union all

select 2 union all

select 3 union all

select 3

),

row(val,rn) as (

select val,

row_number() over (partition by val order by (select null))rn

from src

)

select val from row where rn = 1

--use windows fn all the time for de-duping with business logic

waqasshami Newbie Points: 5 More actions · Answer 13

SET NOCOUNT ON

BEGIN TRY

BEGIN TRANSACTION

/********************************************************************************************/

PRINT '--> Update ClassificationCriteria';

UPDATE CC1 set CC1.Sequence = CC2.rownum FROM ClassificationCriteria AS CC1

JOIN

(SELECT ROW_NUMBER() OVER(PARTITION BY ClassificationID ORDER BY ClassificationID, ParentID, Sequence) - 1 AS rownum, CriteriaID FROM ClassificationCriteria)

AS CC2 ON CC1.CriteriaID = cc2.CriteriaID

/********************************************************************************************/

PRINT '==> Commiting transaction';

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION;

PRINT '!!! Error: ' + ERROR_MESSAGE();

PRINT '<== Transaction has been rolled back';

END CATCH

Mauricio_ SSCrazy Points: 2935 More actions · Answer 14

This is a definition for a field in a very nice view I've designed to solve a problem:

(SELECT INTERNETID

FROM (SELECT InternetId, row_number() OVER (ORDER BY InternetId) AS Fila

FROM PaydayLoans Loan

WHERE Loan.CUSTOMERID = cust.CUSTOMERID

GROUP BY InternetId) AS Temporal

WHERE Temporal.Fila = 3) = loan.InternetId THEN 1 ELSE 0 END) AS ThirdPayments

I'm sure Itzik Ben-Gan can find a better way to do this but I hope his book helps me

Cheers,

Mauricio

SQLServerCentral apologizes and you can win a book

Cookies on SQLServerCentral