SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 How to handle index problem?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 07/19/2013 :  13:09:28  Show Profile  Reply with Quote
This seems like a pretty common scenario...

I have a "students" table with 30 fields. My application has a grid that displays all 30 columns. There are 10 million records in the table and all columns are sortable. The grid makes use of paging (obviously) but when sorts are used a scan is required. In order to make sorting quick for all columns I would need to add an index like this for each column...

CREATE NONCLUSTERED INDEX [idx_Students_FirstName_Incl] ON [dbo].[Students]
(
[FirstName] ASC
)
INCLUDE (
[Column1],
[Column2],
[Column3],
[Column4],
[etc],

This means that my one "students" table is now essentially 31 tables behind the scenes because of the 30 covering indexes. I can get away with not having indexes on columns that are not very selective but I still need about 20 indexes to make sorting speedy. How do others handle this?

Thanks.

Edited by - ferrethouse on 07/19/2013 13:10:26

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 07/19/2013 :  13:16:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
Although your table is small, if the performance is not acceptable then I'd suggest partitioning. I can't imagine that the performance is that slow on such a small table though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 07/19/2013 :  13:21:47  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Although your table is small, if the performance is not acceptable then I'd suggest partitioning. I can't imagine that the performance is that slow on such a small table though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



10 million records is small? I do expect it to grow substantially over time.

I will investigate partitioning but we are using web edition (hosted on AWS) and I believe partitioning is an Enterprise feature.

Note: The sorts take about 8 seconds without an index and 2 seconds with an index. 8 seconds isn't bad but I like to optimize. I also like to reduce IO stress for scalability.

Edited by - ferrethouse on 07/19/2013 13:23:03
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 07/19/2013 :  15:06:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's not a tiny table, but yeah it's currently small to medium. I generally think of medium-sized tables at about 50-100 million rows. Large maybe at 500 million. Then very-large in the billions.

Have you seen any write performance issue with the 30 indexes? What about storage?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 07/19/2013 15:07:13
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 07/19/2013 :  15:35:48  Show Profile  Reply with Quote
quote:
Have you seen any write performance issue with the 30 indexes? What about storage?


Not concerned about storage. AWS has good read latencies but not so good write latencies. I haven't seen performance issues yet because I haven't yet stress tested the app (it is in development) but I'm anticipating some. The table in question has a read/write ratio of about 10.

It doesn't sound like there are any options other than partitioning. Perhaps I will just index the columns most likely to be sorted most frequently.

Just hoping there was some sort of magic solution ;)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 07/19/2013 :  15:50:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
Back in the day, 30 indexes would have been a problem for SQL 2000 or lower. But with 2005 and newer and especially with advances in hardware, you should be fine.

Now maybe visakh, Peso or someone else has a magic solution, but adding the indexes is what my plan would be. I'd probably do partitioning right off the bat if you think it'll reach a few hundred million rows or bigger. It's easier to add it now than to wait until you need it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 07/22/2013 :  17:35:31  Show Profile  Reply with Quote
quote:
I'd probably do partitioning right off the bat if you think it'll reach a few hundred million rows or bigger. It's easier to add it now than to wait until you need it.


I just did the math on Enterprise Edition. Our SQL server has 32 cores. That's $220,000 up front plus whatever the annual maintenance is! Yikes.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 07/22/2013 :  17:49:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There is a cap for 24(?) cores.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 07/22/2013 :  18:03:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Even if there are 50 rows in each page, you have a total of 200,000 pages.
Who will ever look through all of them?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 07/22/2013 :  18:21:41  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

Even if there are 50 rows in each page, you have a total of 200,000 pages.
Who will ever look through all of them?



Not sure I understand. In a sort operation SQL Server itself has to look through all of the pages via an index scan.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 07/22/2013 :  19:18:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by SwePeso

There is a cap for 24(?) cores.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



Enterprise edition supports the operating system's maximum. For Windows 2008 R2, that's 64 sockets. Some hardware has 10+ cores, so you are looking at hundreds of cores. I currently have a system that has 32 cores. Due to software assurance that we purchased, we get to still license it per CPU rather than per CORE. Although we have to install it using the CORE installer of SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 07/22/2013 :  19:25:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by ferrethouse

quote:
I'd probably do partitioning right off the bat if you think it'll reach a few hundred million rows or bigger. It's easier to add it now than to wait until you need it.


I just did the math on Enterprise Edition. Our SQL server has 32 cores. That's $220,000 up front plus whatever the annual maintenance is! Yikes.



Yeah it's expensive. We pay hundreds of thousands, maybe millions, of dollars on Enterprise edition amongst our hundreds of servers. Most are using the old server license+CAL scheme, but maybe 40 or so are using CPU licensing (normally 2 sockets).

So I'd proceed with adding the indexes and then performing a load test to see how writes are doing. These days with the hardware the way it is, it should be fine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 07/22/2013 :  19:39:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by tkizer

Enterprise edition supports the operating system's maximum.
I meant that the licensing caps at 24 cores and you don't have to pay for more cores, even if there are more.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 07/22/2013 :  19:56:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I have a performant solution for the 30-column Students table with 10,000,000 rows.
My suggestion doesn't multiply the size 30 times, it grows the data with about 68% only (1.7 times) as it uses a helper table and one index.
(50 row(s) affected)
Table 'Students'. Scan count 0, logical reads 212, physical reads 0.
Table 'Sorting'. Scan count 1, logical reads 5, physical reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 81 ms.

name      rows       reserved     data         index_size  unused
Students   10000000  11472664 KB  11428576 KB  42664 KB    1424 KB
Sorting   300000000   7738416 KB   7717104 KB  21240 KB      72 KB
The solution doesn't use Enterprise features such as compression and partitioning. However, there is a semifixed pagesize of currently 50 rows. It is possible to change but requires a huge update.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 07/23/2013 04:36:28
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 07/23/2013 :  04:34:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And here is the actual query.
SELECT		s.StudentID,
		s.Col01,
		s.Col02,
		s.Col03,
		s.Col04,
		s.Col05,
		s.Col06,
		s.Col07,
		s.Col08,
		s.Col09,
		s.Col10,
		s.Col11,
		s.Col12,
		s.Col13,
		s.Col14,
		s.Col15,
		s.Col16,
		s.Col17,
		s.Col18,
		s.Col19,
		s.Col20,
		s.Col21,
		s.Col22,
		s.Col22,
		s.Col23,
		s.Col24,
		s.Col25,
		s.Col26,
		s.Col27,
		s.Col28,
		s.Col29,
		s.Col30
FROM		dbo.Students AS s 
INNER JOIN	dbo.Sorting AS w ON w.SortColumn = 16
			AND PageNum = 1971
			AND w.StudentID = s.StudentID
ORDER BY	w.RowNum;


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 07/23/2013 12:29:14
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 07/23/2013 :  11:36:29  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

And here is the actual query.
SELECT		s.StudentID,
		s.Col01,
		s.Col02,
		s.Col03,
		s.Col04,
		s.Col05,
		s.Col06,
		s.Col07,
		s.Col08,
		s.Col09,
		s.Col10,
		s.Col11,
		s.Col12,
		s.Col13,
		s.Col14,
		s.Col15,
		s.Col16,
		s.Col17,
		s.Col18,
		s.Col19,
		s.Col20,
		s.Col21,
		s.Col22,
		s.Col22,
		s.Col23,
		s.Col24,
		s.Col25,
		s.Col26,
		s.Col27,
		s.Col28,
		s.Col29,
		s.Col30
FROM		dbo.Students AS s 
INNER JOIN	dbo.Sorting AS w ON w.SortColumn = 'Col16'
			AND PageNum = 19712
			AND w.StudentID = s.StudentID
ORDER BY	w.RowNum;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



Looks interesting. Can you post the CREATE script for the helper table and index?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 07/23/2013 :  12:25:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sure. My mistake...
This example creates 100,000 rows in the Student table. You can easily change that to 10,000,000 by changing 100 to 1000 in both places.
-- Create an environment for testing
SET NOCOUNT ON;
GO

USE TempDB;
GO

IF OBJECT_ID('dbo.Sorting') IS NOT NULL
	DROP TABLE	dbo.Sorting;
GO

IF OBJECT_ID('dbo.Students') IS NOT NULL
	DROP TABLE	dbo.Students;
GO

CREATE TABLE	dbo.Students
		(
			StudentID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			Col01 CHAR(36) NOT NULL,
			Col02 CHAR(36) NOT NULL,
			Col03 CHAR(36) NOT NULL,
			Col04 CHAR(36) NOT NULL,
			Col05 CHAR(36) NOT NULL,
			Col06 CHAR(36) NOT NULL,
			Col07 CHAR(36) NOT NULL,
			Col08 CHAR(36) NOT NULL,
			Col09 CHAR(36) NOT NULL,
			Col10 CHAR(36) NOT NULL,
			Col11 CHAR(36) NOT NULL,
			Col12 CHAR(36) NOT NULL,
			Col13 CHAR(36) NOT NULL,
			Col14 CHAR(36) NOT NULL,
			Col15 CHAR(36) NOT NULL,
			Col16 CHAR(36) NOT NULL,
			Col17 CHAR(36) NOT NULL,
			Col18 CHAR(36) NOT NULL,
			Col19 CHAR(36) NOT NULL,
			Col20 CHAR(36) NOT NULL,
			Col21 CHAR(36) NOT NULL,
			Col22 CHAR(36) NOT NULL,
			Col23 CHAR(36) NOT NULL,
			Col24 CHAR(36) NOT NULL,
			Col25 CHAR(36) NOT NULL,
			Col26 CHAR(36) NOT NULL,
			Col27 CHAR(36) NOT NULL,
			Col28 CHAR(36) NOT NULL,
			Col29 CHAR(36) NOT NULL,
			Col30 CHAR(36) NOT NULL
		);
GO

INSERT		dbo.Students
		(
			Col01,
			Col02,
			Col03,
			Col04,
			Col05,
			Col06,
			Col07,
			Col08,
			Col09,
			Col10,
			Col11,
			Col12,
			Col13,
			Col14,
			Col15,
			Col16,
			Col17,
			Col18,
			Col19,
			Col20,
			Col21,
			Col22,
			Col23,
			Col24,
			Col25,
			Col26,
			Col27,
			Col28,
			Col29,
			Col30
		)
SELECT		NEWID() AS Col01,
		NEWID() AS Col02,
		NEWID() AS Col03,
		NEWID() AS Col04,
		NEWID() AS Col05,
		NEWID() AS Col06,
		NEWID() AS Col07,
		NEWID() AS Col08,
		NEWID() AS Col09,
		NEWID() AS Col10,
		NEWID() AS Col11,
		NEWID() AS Col12,
		NEWID() AS Col13,
		NEWID() AS Col14,
		NEWID() AS Col15,
		NEWID() AS Col16,
		NEWID() AS Col17,
		NEWID() AS Col18,
		NEWID() AS Col19,
		NEWID() AS Col20,
		NEWID() AS Col21,
		NEWID() AS Col22,
		NEWID() AS Col23,
		NEWID() AS Col24,
		NEWID() AS Col25,
		NEWID() AS Col26,
		NEWID() AS Col27,
		NEWID() AS Col28,
		NEWID() AS Col29,
		NEWID() AS Col30
FROM		master.dbo.spt_values AS a
INNER JOIN	master.dbo.spt_values AS b ON b.Type = 'P'
			AND b.Number BETWEEN 1 AND 100
INNER JOIN	master.dbo.spt_values AS c ON c.Type = 'P'
			AND c.Number BETWEEN 1 AND 10
WHERE		a.Type = 'P'
		AND a.Number BETWEEN 1 AND 100;
GO

CREATE TABLE	dbo.Sorting
		(
			SortColumn TINYINT NOT NULL,
			PageNum INT NOT NULL,
			StudentID INT NOT NULL,
			RowNum TINYINT NOT NULL
		);
GO

INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  1 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col01) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  2 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col02) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  3 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col03) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  4 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col04) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  5 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col05) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  6 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col06) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  7 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col07) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  8 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col08) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  9 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col09) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 10 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col10) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 11 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col11) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 12 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col12) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 13 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col13) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 14 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col14) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 15 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col15) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 16 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col16) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 17 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col17) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 18 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col18) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 19 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col19) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 20 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col20) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 21 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col21) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 22 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col22) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 23 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col23) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 24 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col24) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 25 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col25) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 26 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col26) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 27 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col27) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 28 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col28) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 29 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col29) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 30 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col30) - 1 AS RowNum FROM dbo.Students) AS d;
GO

CREATE UNIQUE CLUSTERED INDEX UCX_Sorting ON dbo.Sorting (SortColumn, PageNum, StudentID);
GO

ALTER TABLE	dbo.Sorting
WITH CHECK
ADD CONSTRAINT	FK_Sorting_Students
FOREIGN KEY	(
			StudentID
		)
REFERENCES	dbo.Students
		(
			StudentID
		);
GO



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000