While the row_number feature in sql 2005+ has proven to be a very powerful feature, there are still ways to implement that same functionality in SQL Server 2000. Let’s first look at the SQL 2005+ implementation of ROW_NUMBER, then compare it to the SQL 2000: [cc lang=”sql”] — SQL 2005+ SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY c.LastName ASC) ,c.LastName ,c.FirstName FROM SalesLT.Customer c [/cc] And the SQL 2000 version: [cc lang=”sql”] SELECT RowNumber = IDENTITY(INT,1,1) ,c.LastName ,c.FirstName INTO #Customer_RowID FROM SalesLT.Customer c ORDER BY c.LastName ASC [/cc] As with everything, there are pro’s and cons however. Using the SQL 2000 version, the total time take to run is 113ms, while the ROW_NUMBER function returns in 134ms (a 21ms difference). That’s a positive for the SQL 2000 version. Problem is, in this case, there were only 440 rows returned. Increase the result set to over 1000+ and the advantages begin to fade. The biggest drawback regarding the SQL 2000 version is that if you are inserting the results using a SELECT..INTO model, you cannot insert the identity column from the table you are selecting from. Like this (notice the *) : [cc lang=”sql”] SELECT RowNumber = IDENTITY(INT,1,1) ,c.LastName ,c.FirstName ,* INTO #Customer_RowID FROM SalesLT.Customer c ORDER BY c.LastName ASC [/cc] Otherwise, you receive the following error message: Cannot add identity column, using the SELECT INTO statement, to table ‘#Customer_RowID’, which already has column ‘CustomerID’ that inherits the identity property. So essentially, with SQL 2000, there is no way to throw […]
Continue reading ...
The maximum value for a smallint in SQL Server is: -32768 through 32767 And the byte size is: 2 bytes other maximum values: BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes) Int: -2147483648 through 2147483647 (4 bytes) TinyInt: 0 through 255 (1 byte)
Continue reading ...
The maximum value for a tinyint in SQL Server is: 0 through 255 And the byte size is: 1 byte other maximum values: BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes) Int: -2147483648 through 2147483647 (4 bytes) SmallInt: -32768 through 32767 (2 bytes)
Continue reading ...
In some situations, deadlocks may need to be dealt with not by changing the source of the deadlock, but by changing handling the deadlock gracefully. An example of this may be an external subscription that runs on a schedule deadlocking with another process. If the subscription deadlocks then it would be ok to just kill it and not allow it to interfere with the source process. However if it raises an error, then the source of publication pull will fail. First to make sure one of the deadlocking procedures becomes a victim over the other, a deadlock priority can be set in order to tell SQL Server which process it should kill first. If the priority is set to “high”, then the procedure will not be the victim of the deadlock, (that is, if the other process does not have a priority). Now to handle the deadlock gracefully, wrap it in a try / catch. If the process is the victim of the deadlock, can simply be rolled back without the error bubbling up to the end-user. To give an example of this, let’s simulate a deadlock by creating two tables and write to them from separate query windows. First we’ll setup the tables. [cc lang=”sql”] ——— setup ———- CREATE TABLE ##t1 ( i int ) CREATE TABLE ##t2 ( i int ) INSERT INTO ##t1 SELECT 4 INSERT INTO ##t2 SELECT 5 ——— /setup ———- [/cc] Now copy the two code snippets into separate windows and see the results. […]
Continue reading ...
Occasionally, there comes a requirement to search all databases on a particular server for either columns with a specific name, or columns relating to a specific subject. In the most recent case, I had to find all similar columns in all databases because the company plans to change the datatype of these columns. My task began with having to locate each of these columns in order to determine the impact of the change, and what actually needed to change. For this, I used the undocumented sp_MSForEachDB procedure. It is a great procedure that actually takes a SQL string as an input parameter. The SQL used for the input parameter must then contain a question mark (?) in which each database replaces the question mark and the SQL is executed. Here is an example: [cc lang=”sql”] sp_MSForEachDB @command1=’USE ?; SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE Data_Type = ”int” AND COLUMN_NAME like ”%ColumnNameHere%”’ [/cc] To utilize correctly, replace the ColumnNameHere with the name of the column you want to find (or what the column name should be like). It will then return you all the columns on the server that are named like the column you specified.
Continue reading ...
