Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQLServerCentral apologizes and you can win a book Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2013 9:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2008 7:24 AM
Points: 0, Visits: 0
Comments posted to this topic are about the item SQLServerCentral apologizes and you can win a book
Post #1519856
Posted Wednesday, December 4, 2013 10:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 9:17 PM
Points: 3, Visits: 42
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)

Post #1519860
Posted Wednesday, December 4, 2013 10:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 12:41 AM
Points: 6, Visits: 220
Very usefull staff !
paging example :

with searchCTE as
(
select Code,
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;
Post #1519871
Posted Wednesday, December 4, 2013 11:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1519873
Posted Wednesday, December 4, 2013 11:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:16 AM
Points: 1, Visits: 194
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

Post #1519874
Posted Wednesday, December 4, 2013 11:45 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:57 PM
Points: 949, Visits: 222
-- 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)

SELECT ROW_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
Post #1519877
Posted Thursday, December 5, 2013 12:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 6:17 AM
Points: 88, Visits: 49
Hi,

;WITH e1(n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
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 ;
Post #1519881
Posted Thursday, December 5, 2013 12:09 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 6:09 AM
Points: 746, Visits: 277
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);
Post #1519882
Posted Thursday, December 5, 2013 12:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 7:37 AM
Points: 2, Visits: 56
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;
Post #1519888
Posted Thursday, December 5, 2013 1:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 2,214, Visits: 491
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'
Post #1519893
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse