May 22, 2017 at 6:46 am
ACK! A Celko article that sounds exactly as he does on the forums... Yeah, sorry. Don't think I can finish reading that one.
Interesting that Sequence won't let me use DISTINCT, UNION, or UNION ALL. The error message being:
Msg 11721, Level 15, State 1, Line 51
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.
I can see this being useful in some circumstances, but not in others. It may not work for me here because I do need unique records, not dups. I'm going to test my identity / reseed vs the sequence and see whether the normal code is pulling duplicates or not. And, as always, thank you for the input.
May 22, 2017 at 8:30 am
Brandie Tarvin - Monday, May 22, 2017 6:46 AMACK! A Celko article that sounds exactly as he does on the forums... Yeah, sorry. Don't think I can finish reading that one.Interesting that Sequence won't let me use DISTINCT, UNION, or UNION ALL. The error message being:
Msg 11721, Level 15, State 1, Line 51
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.
I can see this being useful in some circumstances, but not in others. It may not work for me here because I do need unique records, not dups. I'm going to test my identity / reseed vs the sequence and see whether the normal code is pulling duplicates or not. And, as always, thank you for the input.
Don't let yourself get trolled.
May 22, 2017 at 8:55 am
Ed Wagner - Monday, May 22, 2017 8:30 AMBrandie Tarvin - Monday, May 22, 2017 6:46 AMACK! A Celko article that sounds exactly as he does on the forums... Yeah, sorry. Don't think I can finish reading that one.Interesting that Sequence won't let me use DISTINCT, UNION, or UNION ALL. The error message being:
Msg 11721, Level 15, State 1, Line 51
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.
I can see this being useful in some circumstances, but not in others. It may not work for me here because I do need unique records, not dups. I'm going to test my identity / reseed vs the sequence and see whether the normal code is pulling duplicates or not. And, as always, thank you for the input.
Don't let yourself get trolled.
It's funny if you picture him bashing himself for all the rules he violated in his examples.
The only real use I've seen in SEQUENCEs is to have an identity columns sharing values among different tables, or creating a composite key in a single column. Otherwise, it becomes a more complex identity.
May 22, 2017 at 9:14 am
Brandie Tarvin - Monday, May 22, 2017 6:46 AMInteresting that Sequence won't let me use DISTINCT, UNION, or UNION ALL. The error message being:Msg 11721, Level 15, State 1, Line 51
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.
I can see this being useful in some circumstances, but not in others. It may not work for me here because I do need unique records, not dups. I'm going to test my identity / reseed vs the sequence and see whether the normal code is pulling duplicates or not. And, as always, thank you for the input.
Brandie
I have used SEQ objects in 3 ways. The nice thing is that you can have the ever-increasing value be unique across multiple tables.
First the SEQ object
IF OBJECT_ID('dbo.MySequence', N'SO') IS NULL
BEGIN
CREATE SEQUENCE dbo.MySequence
AS BIGINT
MINVALUE 1
START WITH 1
INCREMENT BY 1;
END;
GO
1 - As a DEFAULT values for a NOT NULL INT value. just like an IDENTITY
IF OBJECT_ID('dbo.Table1', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Table1 (
ID INT NOT NULL DEFAULT NEXT VALUE FOR dbo.MySequence
, SomeDate DATETIME NOT NULL
);
END;
GO
INSERT INTO dbo.Table1 (SomeDate)
VALUES (GETDATE())
, (GETDATE())
, (GETDATE());
GO
SELECT * FROM dbo.Table1;
DROP TABLE dbo.Table1;
GO
2 - As an externally generated INT value
IF OBJECT_ID('dbo.Table2', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Table2 (
ID INT NOT NULL
, SomeGUID UNIQUEIDENTIFIER NOT NULL
);
END;
GO
INSERT INTO dbo.Table2 (ID, SomeGUID)
VALUES (NEXT VALUE FOR dbo.MySequence, NEWID())
, (NEXT VALUE FOR dbo.MySequence, NEWID());
SELECT * FROM dbo.Table2;
DROP TABLE dbo.Table2;
GO
3 - In a batch process. But this one requires jumping through some hoops.
IF OBJECT_ID('dbo.GetNextSequenceNum', N'P') IS NULL
BEGIN
EXEC sp_executesql N'CREATE PROCEDURE dbo.GetNextSequenceNum AS DUMMY:;';
END;
GO
ALTER PROCEDURE dbo.GetNextSequenceNum (
@NumCount INT = 1
) AS
BEGIN
DECLARE
@range_first_value SQL_VARIANT = NULL
, @range_last_value SQL_VARIANT = NULL;
SET @NumCount = ABS(ISNULL(@NumCount, 1));
IF ( @NumCount = 0 ) SET @NumCount = 1;
EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.MySequence'
, @range_size = @NumCount
, @range_first_value = @range_first_value OUTPUT
, @range_last_value = @range_last_value OUTPUT;
;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
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (SELECT TOP (@NumCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
SELECT
seq = CAST(@range_first_value AS BIGINT) + N-1
, extID = N
FROM cteTally;
END;
GO
SELECT
ID = CAST(NULL AS BIGINT)
, rn = ROW_NUMBER() OVER (ORDER BY src.word)
, src.word
INTO #MyNewData
FROM (
VALUES ('the'), ('quick'), ('brown'), ('fox')
) AS src(word)
CREATE TABLE #seqNums (
seq BIGINT
, extRef INT
);
DECLARE @NumCount INT = (SELECT COUNT(*) FROM #MyNewData);
INSERT INTO #seqNums (seq, extRef)
EXEC dbo.GetNextSequenceNum @NumCount;
UPDATE nd
SET ID = sn.seq
FROM #MyNewData AS nd
INNER JOIN #seqNums AS sn
ON nd.rn = sn.extRef;
SELECT * FROM #MyNewData;
DROP TABLE #seqNums;
DROP TABLE #MyNewData;
DROP PROCEDURE dbo.GetNextSequenceNum;
GO
May 22, 2017 at 12:11 pm
DesNorton - Monday, May 22, 2017 9:14 AMThe nice thing is that you can have the ever-increasing value be unique across multiple tables.
Yeah, I saw that in the articles. That really is a wonderful feature. I've got a certain other process I could use that on in which some data is split between multiple tables, and using SEQUENCE to connect all that data with the same faux-key without having to do the hoops that we currently do would be really nice. I'm definitely eyeing this for that.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply