Referência rápida SQL Server

DECLARE @message VARCHAR(1000)
SET @message = 'Message...'
RAISERROR(@message, 0, 1) WITH NOWAIT

CREATE TABLE [User] (
	UserID		INT		IDENTITY	NOT NULL
	, Email		VARCHAR(200)		NOT NULL
	, CONSTRAINT PK_User PRIMARY KEY (UserID)
)

SET IDENTITY_INSERT [User] ON
INSERT INTO [User] (UserID, Email) VALUES (1, 'teste@teste.com.br')
SET IDENTITY_INSERT [User] OFF

DBCC CHECKIDENT('[User]', RESEED, 0)

INSERT INTO [User] (Email) VALUES ('teste2@teste.com.br')
DECLARE @id INT
SELECT @id = CONVERT(INT, SCOPE_IDENTITY())

DROP INDEX UNIQUE_Supplier_Name ON Supplier

CREATE UNIQUE INDEX UNIQUE_Supplier_Name ON Supplier (ReferenceName)

CREATE UNIQUE NONCLUSTERED INDEX UNIQUE__fi_pdv_tb_Coupon__ds_offline_id
ON fi_pdv_tb_Coupon(id_omega, ds_offline_id)
WHERE ds_offline_id IS NOT NULL

DECLARE @productID int

DECLARE cursor1 CURSOR FOR
SELECT ProductID FROM Product (NOLOCK)

OPEN cursor1

FETCH NEXT FROM cursor1 INTO
	@productID

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT '-- productID=' + convert(varchar(20), @productID)

	-- Executar aqui o que precisa ser executado

	FETCH NEXT FROM cursor1 INTO
		@productID
END

CLOSE cursor1
DEALLOCATE cursor1

SELECT
	m.id,
	COUNT(1) OVER()
FROM
	Movie m
WHERE
	m.ChannelID = 1
ORDER BY
	m.Created
OFFSET @OFFSET ROWS FETCH NEXT @COUNT ROWS ONLY

CREATE PROCEDURE dbo.BrandPagedList (
	@page int,
	@pageSize int
) AS
BEGIN

WITH ListResult AS
(
	SELECT
		br.BrandID
		, br.[Name]

		, ROW_NUMBER() OVER(ORDER BY br.[Name] ASC) AS rowNumber
	FROM
		Brand (NOLOCK) br
)
SELECT * , (SELECT MAX(rowNumber) FROM ListResult) as TotalRows FROM ListResult WHERE rowNumber BETWEEN ((@pageSize * (@page - 1)) + 1) AND (@pageSize * @page);

BEGIN TRY
	BEGIN TRANSACTION
	PRINT '--BEGIN TRANSACTION'

	--Execute...

	COMMIT
	PRINT '--COMMIT'

END TRY
BEGIN CATCH

	DECLARE @errorMessage nvarchar(max), @errorSeverity int, @errorState int;

	SELECT
		@errorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)),
		@errorSeverity = ERROR_SEVERITY(),
		@errorState = ERROR_STATE();

	PRINT '--ROLLBACK'
	ROLLBACK

	RAISERROR (@errorMessage, 16, -1);
	RETURN

END CATCH

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnFirstMonthDay' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnFirstMonthDay
go

CREATE FUNCTION dbo.fnFirstMonthDay(@date DATE) RETURNS DATE AS
BEGIN

	IF @date IS NULL
		RETURN NULL

	RETURN DATEADD(day, ((DATEPART(D, @date) -1) * -1), @date)

END

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnFirstWeekDay' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnFirstWeekDay
go

CREATE FUNCTION dbo.fnFirstWeekDay(@date DATE) RETURNS DATE AS
BEGIN

	IF @date IS NULL
		RETURN NULL

	RETURN DATEADD(day, ((DATEPART(DW, @date) -1) * -1), @date)

END

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnGetNumericCharacters' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION fnGetNumericCharacters
GO

CREATE FUNCTION dbo.fnGetNumericCharacters(@text VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS
BEGIN

	IF @text IS NULL
		RETURN NULL

	DECLARE @index INT = PATINDEX('%[^0-9]%', @text)

	WHILE @index > 0
	BEGIN
		SET @text = STUFF(@text, @index, 1, '' )
		SET @index = PATINDEX('%[^0-9]%', @text )
	END

	IF len(@text) = 0
		RETURN NULL

	RETURN @text

END
GO

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnRemoveDiacritics' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnRemoveDiacritics
GO

CREATE FUNCTION dbo.fnRemoveDiacritics (@text VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN

	SELECT @text = (@text collate SQL_Latin1_General_Cp1251_CS_AS)

    RETURN @text

END

IF EXISTS(SELECT * FROM sys.objects where name = 'fnRemoveSpecialCharacters' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnRemoveSpecialCharacters
GO

CREATE FUNCTION dbo.fnRemoveSpecialCharacters (@text VARCHAR(MAX), @allowSpaces BIT = 1)
RETURNS VARCHAR(MAX) AS
BEGIN

    DECLARE @matchExpression AS VARCHAR(100)

	IF @allowSpaces = 1
		SET @matchExpression = '%[^a-z0-9 ]%'
	ELSE
		SET @matchExpression = '%[^a-z0-9]%'

	WHILE PatIndex(@matchExpression, @text) > 0
        SET @text = Stuff(@text, PatIndex(@matchExpression, @text), 1, '')

    RETURN @text

END
GO

IF EXISTS(SELECT name FROM sys.objects WHERE name = 'fnSplit' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnSplit
GO

CREATE  FUNCTION dbo.fnSplit(@text VARCHAR(MAX), @separator VARCHAR(20) = ' ')
RETURNS @strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value VARCHAR(MAX)
)
AS
BEGIN

DECLARE @index int = -1

WHILE (LEN(@text) > 0)
  BEGIN
    SET @index = CHARINDEX(@separator , @text)
    IF (@index = 0) AND (LEN(@text) > 0)
      BEGIN  
        INSERT INTO @strings VALUES (@text)
          BREAK
      END
    IF (@index > 1)
      BEGIN  
        INSERT INTO @strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index))
      END
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

SELECT
	a.ItemID,
	(SELECT
		STUFF(
				(SELECT ',' + CONVERT(VARCHAR(10), si.SupplierID) FROM SupplierItem si (NOLOCK) WHERE a.ItemID = si.ItemID AND si.Active = 1 FOR XML PATH(''))
			,1,1,'')
	) as SupplierIdList
FROM
	Item a (nolock)
WHERE
	a.ItemID in (303216, 5)

IF EXISTS(SELECT name FROM sys.objects WHERE Name = 'fnPadString' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnPadString
GO

CREATE FUNCTION dbo.fnPadString (@text VARCHAR(MAX), @padWith char(1), @length int) RETURNS VARCHAR(MAX) AS
BEGIN

DECLARE @ret VARCHAR(MAX)
SELECT @ret = ISNULL(REPLICATE(@padWith, @length - LEN(ISNULL(@text,0))), '') + @text
RETURN @ret

END
GO

;WITH cte AS
(
	SELECT
		cp.id_omega
		, cp.id_coupon
		, cp.dt_coupon
		, cp.ts_control
		, cp.ds_offline_id
		, ROW_NUMBER() OVER(PARTITION BY cp.ds_offline_id ORDER BY cp.ds_offline_id) AS RowNumber
	FROM
		fi_pdv_tb_Coupon cp (nolock)
		INNER JOIN (
			SELECT id_omega, ds_offline_id, COUNT(ds_offline_id) as qtd FROM fi_pdv_tb_Coupon (NOLOCK) GROUP BY id_omega, ds_offline_id HAVING COUNT(ds_offline_id) > 1
		) b
		on cp.id_omega = b.id_omega
		and cp.ds_offline_id = b.ds_offline_id
)

UPDATE cte SET ds_offline_id = ds_offline_id + '_' + convert(varchar(10), RowNumber) where RowNumber > 1

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnGetUrl' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnGetUrl
go

CREATE FUNCTION dbo.fnGetUrl(@text varchar(200)) RETURNS VARCHAR(200) AS
BEGIN

	--remove
	SET @text = replace(@text, '&', '')
	SET @text = replace(@text, '.', '')
	SET @text = replace(@text, '(', '')
	SET @text = replace(@text, ')', '')
	SET @text = replace(@text, '''', '')
	SET @text = replace(@text, '"', '')
	SET @text = replace(@text, '%', '')
	SET @text = replace(@text, '$', '')
	SET @text = replace(@text, '+', '')

	--separador
	SET @text = replace(@text, ' e ', '-')
	SET @text = replace(@text, ',', '-')
	SET @text = replace(@text, ' - ', '-')
	SET @text = replace(@text, '/', '-')
	SET @text = replace(@text, '\', '-')
	SET @text = replace(@text, ' ', '-')

	SET @text = replace(@text, '--', '-')

	--remove acentuação
	SELECT @text = @text Collate SQL_Latin1_General_CP1253_CI_AI

	SET @text = ltrim(rtrim(lower(@text)))

	RETURN @text

END
GO

SELECT
	p.BusinessEntityID, t.Name AS Territory
FROM
	Sales.SalesPerson p
CROSS JOIN
	Sales.SalesTerritory t
ORDER BY
	p.BusinessEntityID;

--drop table [Local]

CREATE TABLE [Local]
	(LocalID int not null,
	LocalPaiID int null,
	Nome varchar(50) not null)
go

INSERT INTO [Local] values (100, null, 'Bloco 1');
	INSERT INTO [Local] values (110, 100, 'Prédio 1');
			INSERT INTO [Local] values (111, 110, '1º Andar');
			INSERT INTO [Local] values (112, 110, '2º Andar');
			INSERT INTO [Local] values (113, 110, '3º Andar');
			INSERT INTO [Local] values (114, 110, '4º Andar');
	INSERT INTO [Local] values (120, 100, 'Prédio 2');
			INSERT INTO [Local] values (121, 120, '1º Andar');
			INSERT INTO [Local] values (122, 120, '2º Andar');
			INSERT INTO [Local] values (123, 120, '3º Andar');
	INSERT INTO [Local] values (12, 100, 'Prédio 3');
INSERT INTO [Local] values (200, null, 'Bloco 2');
	INSERT INTO [Local] values (210, 200, 'Prédio 1');
	INSERT INTO [Local] values (220, 200, 'Prédio 2');
	INSERT INTO [Local] values (230, 200, 'Prédio 3');
INSERT INTO [Local] values (300, null, 'Bloco 3');
go


WITH Loc AS
		(SELECT 1 as Nivel, l.LocalID, l.Nome, CONVERT(VARCHAR(1000), l.Nome) as CaminhoCompleto FROM [Local] l (NOLOCK) WHERE l.LocalPaiID IS NULL
		UNION ALL
		SELECT Nivel+1, l.LocalID, l.Nome, CONVERT(VARCHAR(1000), Loc.CaminhoCompleto + ' / ' + l.Nome) as CaminhoCompleto
		FROM [Local] l (NOLOCK)
		INNER JOIN Loc on l.LocalPaiID = Loc.LocalID
		WHERE l.LocalPaiID is not null
		)
	SELECT LocalID, Nome, CaminhoCompleto FROM Loc;
go

IF EXISTS(SELECT name FROM sys.objects WHERE Name = 'fn_Local_Caminho_Completo' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fn_Local_Caminho_Completo
GO

CREATE FUNCTION dbo.fn_Local_Caminho_Completo(@LocalID int)
returns VARCHAR(1000)
AS
BEGIN

	DECLARE @caminho varchar(1000);

	WITH Loc AS
		(SELECT l.LocalID, l.LocalPaiID, l.Nome, CONVERT(VARCHAR(1000), l.Nome) as CaminhoCompleto FROM [Local] l (NOLOCK) WHERE l.LocalID = @LocalID
		UNION ALL
		SELECT l.LocalID, l.LocalPaiID, l.Nome, CONVERT(VARCHAR(1000), l.Nome + ' / ' + Loc.CaminhoCompleto) as CaminhoCompleto
		FROM [Local] l (NOLOCK)
		INNER JOIN Loc on l.LocalID = Loc.LocalPaiID
	)
	SELECT @caminho = CaminhoCompleto FROM Loc WHERE LocalPaiID IS NULL

	return @caminho;
END
go

ALTER TABLE [Local] ADD CaminhoCompleto AS dbo.fn_Local_Caminho_Completo(LocalID);
go

select * from [Local];
go

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'OrderInsert')
	DROP PROCEDURE OrderInsert
go

IF EXISTS(SELECT name FROM sys.types WHERE is_user_defined = 1 AND is_table_type = 1 AND name = 'OrderInsert_OrderItemTableType')
	DROP TYPE dbo.OrderInsert_OrderItemTableType
GO

CREATE TYPE OrderInsert_OrderItemTableType AS TABLE (
	  ItemID INT
	, Quantity INT
)
GO

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'OrderInsert')
	DROP PROCEDURE dbo.OrderInsert
GO

CREATE PROCEDURE dbo.OrderInsert (
	  @clientID INT
	, @totalValue DECIMAL(15,2)
	, @orderItems OrderInsert_OrderItemTableType READONLY
)
as
BEGIN

INSERT INTO [Order] (
	  ClientID
	, TotalValue
)
VALUES (
	@clientID
	, @totalValue
)

DECLARE @orderID INT
SET @orderID = CONVERT(INT, SCOPE_IDENTITY())

INSERT INTO OrderItem (
	OrderID
	, ItemID
	, Quantity
)
SELECT
	@orderID
	, ItemID
	, Quantity
FROM
	@orderItems

SELECT @orderID as id

END
go

USE AdventureWorks2012;  
GO  
SELECT
	i.ProductID
	, p.Name
	, i.LocationID
	, i.Quantity  
  , RANK() OVER(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank  
FROM
	Production.ProductInventory AS i   
	INNER JOIN Production.Product AS p   
		ON i.ProductID = p.ProductID  
WHERE
	i.LocationID BETWEEN 3 AND 4  
ORDER BY
	i.LocationID;  

USE AdventureWorks2012;  
GO
SELECT
	TOP(10) BusinessEntityID
	, Rate
	, DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  
FROM
	HumanResources.EmployeePayHistory;

CREATE PROCEDURE dbo.QueueGetNext (
	@queueOperationId INT = null) as
BEGIN

	DECLARE @queueId VARCHAR(36) = NULL

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

		SELECT TOP 1
			@queueId = QueueId
		FROM
			[Queue] WITH (UPDLOCK)
		WHERE
			QueueStatusId = 1 --Incluido
			AND (@queueOperationId IS NULL OR QueueOperationId = @queueOperationId)
		ORDER BY
			RequestDate ASC

		--Atualiza
		UPDATE
			[Queue]
		SET
			QueueStatusId = 2 --Processando
		WHERE
			QueueId = @queueId

	COMMIT TRANSACTION

	SELECT * FROM [Queue] WHERE QueueId = @queueId

END