Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nico
Starting Member
18 Posts |
 Posted - 2002-08-19 : 08:40:30
|
| Hello,I'm programming in .NET and there's a nifty component called the DataGrid which supports paging and sorting. Microsoft's implementation however is rather crude. They query the entire recordset, loop through it, and display X records (X being the page size). An improvement would be to select the top X records starting from pagenumber * pagesize, but this only works if you're sorting on ID (or any other sequential, unique value). What I need is the ability to filter on rownumbers.Luckily I found an article here that discussed the use of a temporary table in order to store a rownumber.CREATE TABLE #RowNumber (RowNumber int IDENTITY (1, 1),id int )I'm assuming that the # marks the table as a temporary table, and I do take care of dropping it. And now, finally, for my question: what happens when multiple users perform this query? Will the RowNumber table become locked? In other words, do I need to add a user specific ID, and if so, how? Where is this RowNumber table stored anyway?Kind regards, Nico R. |
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2002-08-19 : 08:52:52
|
| Aaahhhh - Isn't Books Online fantastic - the answer to all of your questions in one place.From BOL:Temporary TablesYou can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)INSERT INTO #MyTempTable VALUES (1)If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE: A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.All other local temporary tables are dropped automatically at the end of the current session.Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended. macka. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-08-19 : 08:54:48
|
| temporary tables are session specific.....you should have no conflicts because of multiple users.....lookup BOL for details.don't know 100% where they get saved....but TEMPDB shoulds like a goood home! |
|
|
|
Nico
Starting Member
18 Posts |
Posted - 2002-08-19 : 09:37:27
|
| Thanks for the info, and thanks for the reference to BOL.Kind regards, Nico R. |
|
|
|
|
|
|
|
|
Using #RowNumber table for sorted paging