December 4, 2013 at 9:17 pm
Comments posted to this topic are about the item SQLServerCentral apologizes and you can win a book
December 4, 2013 at 10:12 pm
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)
December 4, 2013 at 10:56 pm
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;
December 4, 2013 at 11:23 pm
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;
December 4, 2013 at 11:29 pm
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
December 4, 2013 at 11:45 pm
-- 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
December 5, 2013 at 12:05 am
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 ;
December 5, 2013 at 12:09 am
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);
December 5, 2013 at 12:47 am
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;
December 5, 2013 at 1:05 am
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'
December 5, 2013 at 1:13 am
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'
December 5, 2013 at 1:20 am
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
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
December 5, 2013 at 1:34 am
;with src(val) as (
select 1 union all
select 2 union all
select 2 union all
select 3 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
December 5, 2013 at 1:36 am
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
December 5, 2013 at 1:40 am
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
Viewing 15 posts - 1 through 15 (of 287 total)
You must be logged in to reply to this topic. Login to reply