Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. The primary key will represent a clustered index, while the unique constraint a non clustered index. [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), UNIQUE (UserName) ) [/cc] The drawback is that the indexes (or constraints) need to be unique. One potential way to circumvent this however, is to create a composite unique constraint: [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), FirstName varchar(50), UNIQUE (UserName,UserID) ) [/cc] You can also create the equivalent of a clustered index. To do so, just add the clustered reserved word. [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), FirstName varchar(50), UNIQUE CLUSTERED (UserName,UserID) ) [/cc] Generally, temp tables perform better in situations where an index is needed. The downfall to temp tables is that they will frequently cause recompilation. This was more of an issue with SQL 2000 when compilation was performed at the procedure level instead of the statement level. SQL 2005 and above perform compilation at the statement level so if only one statement utilizes a temp table then that statement is the only one that gets recompiled. Contrary to popular belief, table variables can and do write to disk.
Continue reading ...
One of the most valuable assets of a temp table (#temp) is the ability to add either a clustered or non clustered index. Additionally, #temp tables allow for the auto-generated statistics to be created against them. This can help the optimizer when determining cardinality. Below is an example of creating both a clustered and non-clustered index on a temp table. [cc lang=”sql”] CREATE TABLE #Users ( ID int IDENTITY(1,1), UserID int, UserName varchar(50) ) INSERT INTO #Users ( UserID, UserName ) SELECT UserID = u.UserID ,UserName = u.UserName FROM dbo.Users u CREATE CLUSTERED INDEX IDX_C_Users_UserID ON #Users(UserID) CREATE INDEX IDX_Users_UserName ON #Users(UserName) [/cc] Even though you can implicitly create a clustered index on a table variable (@table) by defining a primary key or unique constraint, it is generally more efficient to use a temp table. The downside is recompilation, however this can be suppressed with the use of (Keep Plan) or (Keep Fixed Plan) hints.
Continue reading ...
The following SQL script will generate a single random number: [cc lang=”sql”] SELECT CAST(1000000-CEILING(RAND()* 899001) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) as int) [/cc]
Continue reading ...
One of my favorite aspects of schemas (which were introduced in 2005) is the ability easily manage permissions. For those getting caught in terminology, schemas are the new abstraction layer that replace object owners. In SQL 2000, every object had an owner. And that owner was a user. In most cases the user was dbo. So your stored procedures would have a naming convention of dbo.spExecuteMe. With schemas, the model has changed. While object owners were actual users, schemas are not. Schemas are abstract objects used to separate owners from the objects. The beauty of this is that you can assign a user access to an entire schema, which may be comprised of hundreds of objects. Instead of assigning permissions at the object level, you can say, “Any user assigned to the role of ‘ExecApp’ will be able to execute any object that is created within the schema ‘App’. This way, by simply adding a user to the ExecApp role, he will inherently be able to execute any procedure in the application schema. Likewise, any procedure created within the schema ‘App’, will automatically be able to execute that procedure. So how do you do it? Start by Creating a schema. [cc lang=”sql”] CREATE SCHEMA App [/cc] Next, transfer stored procedures to this schema, or simply create stored procedures with this schema. To transfer: [cc lang=”sql”] ALTER SCHEMA App TRANSFER dbo.spGetCustomers [/cc] Lastly, create a role that will be allowed to execute procedures in the App schema; Add a user to […]
Continue reading ...
The following shows how to find the version of SQL Server you are running (described as productversion). It also shows how to query the level of the product. The level of the product indicates whether it was the initial release, a service pack, or a beta version: RTM = shipping version SPn = service pack (where n is the version) CTP = Community Technology Preview version [cc lang=”sql”] SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) [/cc] If the above statement does not work, you may be running SQL Server 7.0. In which case, use the following statement: [cc lang=”sql”] SELECT @@VERSION [/cc]
Continue reading ...
