December 10, 2014 at 10:24 am
Is it possible to use the row_number function as the default value of a table? I've searched all over and haven't been able to find anything on this. I plan on using this for a quote table with multiple line items. Pretty much sequential row numbering for each new line item added to the table grouped by quotes.
Ex: ROW_NUMBER() over (partition by
order by [ID])
Any input is appreciated. Thanks!
December 10, 2014 at 11:04 am
Quick solution using a calculated column
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_ROWNUM') IS NOT NULL DROP TABLE dbo.TBL_ROWNUM;
IF OBJECT_ID('dbo.UDF_COUNT_ROWS') IS NOT NULL DROP FUNCTION dbo.UDF_COUNT_ROWS;
DECLARE @COUNT_FUNC NVARCHAR(MAX) = N'
CREATE FUNCTION dbo.UDF_COUNT_ROWS
(
@RN_ID INT
,@QUOTE INT
)
RETURNS INT
AS
BEGIN
RETURN
(1 + (SELECT COUNT(RN_ID)
FROM dbo.TBL_ROWNUM X
WHERE X.QUOTE = @QUOTE
AND X.RN_ID < @RN_ID
))
END';
EXEC (@COUNT_FUNC);
CREATE TABLE dbo.TBL_ROWNUM
(
RN_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_ROWNUM_RN_ID PRIMARY KEY CLUSTERED
,QUOTE INT NOT NULL
,RN_VAL INT NOT NULL
,QUOTE_LN AS ( dbo.UDF_COUNT_ROWS( RN_ID,QUOTE ))
,RN_TD DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_ROWNUM_RN_TD DEFAULT (GETDATE())
);
INSERT INTO dbo.TBL_ROWNUM (QUOTE,RN_VAL)
VALUES (1000,10)
,(1000,20)
,(1000,30)
,(1100,10)
,(1200,10)
,(1200,30);
SELECT
X.RN_ID
,X.QUOTE
,X.RN_VAL
,X.QUOTE_LN
,X.RN_TD
FROM dbo.TBL_ROWNUM X;
Results
RN_ID QUOTE RN_VAL QUOTE_LN RN_TD
----------- ----------- ----------- ----------- -----------------------
1 1000 10 1 2014-12-10 18:05:50.593
2 1000 20 2 2014-12-10 18:05:50.593
3 1000 30 3 2014-12-10 18:05:50.593
4 1100 10 1 2014-12-10 18:05:50.593
5 1200 10 1 2014-12-10 18:05:50.593
6 1200 30 2 2014-12-10 18:05:50.593
December 10, 2014 at 12:29 pm
Eirikur Eiriksson (12/10/2014)
Quick solution using a calculated column😎
USE tempdb;GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_ROWNUM') IS NOT NULL DROP TABLE dbo.TBL_ROWNUM;
IF OBJECT_ID('dbo.UDF_COUNT_ROWS') IS NOT NULL DROP FUNCTION dbo.UDF_COUNT_ROWS;
DECLARE @COUNT_FUNC NVARCHAR(MAX) = N'
CREATE FUNCTION dbo.UDF_COUNT_ROWS
(
@RN_ID INT
,@QUOTE INT
)
RETURNS INT
AS
BEGIN
RETURN
(1 + (SELECT COUNT(RN_ID)
FROM dbo.TBL_ROWNUM X
WHERE X.QUOTE = @QUOTE
AND X.RN_ID < @RN_ID
))
END';
EXEC (@COUNT_FUNC);
CREATE TABLE dbo.TBL_ROWNUM
(
RN_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_ROWNUM_RN_ID PRIMARY KEY CLUSTERED
,QUOTE INT NOT NULL
,RN_VAL INT NOT NULL
,QUOTE_LN AS ( dbo.UDF_COUNT_ROWS( RN_ID,QUOTE ))
,RN_TD DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_ROWNUM_RN_TD DEFAULT (GETDATE())
);
INSERT INTO dbo.TBL_ROWNUM (QUOTE,RN_VAL)
VALUES (1000,10)
,(1000,20)
,(1000,30)
,(1100,10)
,(1200,10)
,(1200,30);
SELECT
X.RN_ID
,X.QUOTE
,X.RN_VAL
,X.QUOTE_LN
,X.RN_TD
FROM dbo.TBL_ROWNUM X;
Results
RN_ID QUOTE RN_VAL QUOTE_LN RN_TD----------- ----------- ----------- ----------- -----------------------
1 1000 10 1 2014-12-10 18:05:50.593
2 1000 20 2 2014-12-10 18:05:50.593
3 1000 30 3 2014-12-10 18:05:50.593
4 1100 10 1 2014-12-10 18:05:50.593
5 1200 10 1 2014-12-10 18:05:50.593
6 1200 30 2 2014-12-10 18:05:50.593
Why the dynamic sql to create your function?
I get really nervous using a scalar udf as a computed column but it does work. Of course, if a row is deleted then the numbering gets all out of whack.
delete TBL_ROWNUM
where RN_ID = 3;
INSERT INTO dbo.TBL_ROWNUM (QUOTE,RN_VAL)
VALUES (1000,30);
SELECT
X.RN_ID
,X.QUOTE
,X.RN_VAL
,X.QUOTE_LN
,X.RN_TD
FROM dbo.TBL_ROWNUM X;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2014 at 12:38 pm
I know it´s not perfect, the dynamic is just to contain the whole thing in a single batch, took it from an old code of mine which took the table name as a parameter.
😎
December 10, 2014 at 12:42 pm
Eirikur Eiriksson (12/10/2014)
I know it´s not perfect, the dynamic is just to contain the whole thing in a single batch, took it from an old code of mine which took the table name as a parameter.😎
Ahh that makes sense. I just couldn't figure why in the world you had it in dynamic sql. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2014 at 1:14 pm
December 11, 2014 at 8:40 am
Thanks for the quick reply, Eirikur! It worked like a charm and I appreciate the help. As Sean mentioned, users may change their mind and remove line certain line items. Lets say there are 5 line items (1-5) and a user decided they don't need line item #3. It would throw off the numbers, showing 1, 2, 4, and 5. Is another function, or even a trigger needed to update the current line items to go in order? Possibly a FOR UPDATE trigger?
December 11, 2014 at 8:44 am
sKreetz! (12/11/2014)
Thanks for the quick reply, Eirikur! It worked like a charm and I appreciate the help. As Sean mentioned, users may change their mind and remove line certain line items. Lets say there are 5 line items (1-5) and a user decided they don't need line item #3. It would throw off the numbers, showing 1, 2, 4, and 5. Is another function, or even a trigger needed to update the current line items to go in order? Possibly a FOR UPDATE trigger?
Could you just use the ROW_NUMBER at the time you pull the data? Is there a reason it must be persisted? Doing it dynamically is a lot less prone to problems.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 11, 2014 at 8:50 am
This is just combining what I said and what Sean just said, but you can either apply the row number when you generate the report, or create a view with that column defined, if you want to access it without having to write it over and over.
if exists (select 1 from sys.views where name = 'vrn') drop view dbo.vrn
go
create view dbo.vrn
as
select rn_id, quote, rn_val, rn_td, quote_ln = row_number() over (partition by quote order by rn_val)
from dbo.tbl_rownum
Trying to do this with update triggers will make you prematurely bald.
December 11, 2014 at 8:53 am
JeeTee (12/11/2014)
Trying to do this with update triggers will make you prematurely bald.
+100000
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 11, 2014 at 9:12 am
Thank you for the quick replies and the help, guys. Please bear with me as I am a complete newb to SQL, so I apologize in advance for any stupid questions. This table is used to store line items for each quote and as users update these records, the line items need to show the correct line number for each item of the quote. I didn't even think of doing line numbers through a view. If line number were to be utilized through a view, that means I can do away with the column within the table, right?
December 11, 2014 at 9:18 am
That's correct. If you do it through a view, anyone calling that view who doesn't know what's going on will experience it as any other column, but as you say, there's no need for it to be in the actual table. The view is just an abstraction layer between the physical implementation of the table, and what you want people to see.
December 12, 2014 at 9:04 pm
sKreetz! (12/11/2014)
Thanks for the quick reply, Eirikur! It worked like a charm and I appreciate the help. As Sean mentioned, users may change their mind and remove line certain line items. Lets say there are 5 line items (1-5) and a user decided they don't need line item #3. It would throw off the numbers, showing 1, 2, 4, and 5. Is another function, or even a trigger needed to update the current line items to go in order? Possibly a FOR UPDATE trigger?
It has a problem but slightly different from what you describe, as the calculated column is not "PERSISTED" the numbering is always dense, removing items from a series will then truncate the series to the number of remaining items, hence alter the initial numbering.
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply