When coming from a programming background, it’s natural to want to frame conditional operations within the familiar if.. else constructs. This happens in a lot of SQL code I have worked with (and I used to be a contributor also). The technique below is based on a common scenario. Say you want to insert new [...]
Archive of posts filed under the TSQL category.
DROP INDEX SYNTAX
Here is the syntax needed in order to drop a single index: USE YourDatabaseName; DROP INDEX IX_Product_1 ON dbo.Product; You can also drop multiple indexes within a single transaction: USE YourDatabaseName; DROP INDEX IX_Product_1 ON dbo.Product, IX_Customer_1 ON dbo.Customer; Permissions The permissions needed to drop an index are alter permissions to the table. This is [...]
SET TRANSACTION ISOLATION LEVEL
This statement is used to set the isolation level for either a connection or a stored procedure. The most typical use I’ve seen is at the top of a stored procedure in order to avoid locking and deadlocks. This is a cleaner alternative to using WITH (NOLOCK) hints on tables. If you set the isolation [...]
Using DBCC CHECKIDENT to Reseed a Table After Delete
I imagine you are just looking for simple syntax in order to reseed the identity column of a table you just deleted from. Here is the quick version: DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) And here is an extended example: — populate a table with identity SELECT ID = IDENTITY(int,1,1) ,name INTO ##reseed_example FROM dbo.sysobjects — delete [...]
CROSS APPLY Explained
My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didn’t understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, it’s other uses evaded me for a while. Luckily, [...]
How to Create a View
In order to create a view, use the “Create View” statement using the following convention: CREATE VIEW dbo.vStoreAccountNum AS SELECT AccountNumber FROM Sales.Customer WHERE CustomerType = ‘S’ Views are essentially stored SELECT Statements. They do not offer any speed advantage over regular SELECT statements. And they are not faster than a SELECT statement executed from [...]
SQL Replace
The SQL Replace function replaces a value within a string with another value. The final output is the original string with the replaced value. There are three parameters to the Replace function. The first is the original string. The next parameter is the string to replace. And the last parameter is the value to replace [...]
How to Call a Stored Procedure
Although there are a few ways to call a stored procedure, only one way is actually recommended. The best way is to explicitly specify the stored procedure parameters in the Execute statement. You do this because if the order of the parameters were to change, then the procedure would error out (or even worse pass [...]
How to Concatenate
Concatenation in SQL Server is done by using the plus (+) operator. Let’s setup a table and look at an example by concatenating customer’s first names and last names: — Create Customers table CREATE TABLE #Customers ( FirstName varchar(50), LastName varchar(50) ) INSERT INTO #Customers ( FirstName, LastName ) SELECT ‘Vincent’, ‘Vega’ UNION SELECT ‘Marsellus’, [...]
SQL Current Date
To get the current date in SQL Server use the GETDATE() function. Here is an example: SELECT CurrentDate = GETDATE() GetDate() returns a datetime data type value. There are also other methods in SQL Server that will return the current date. These are less commonly used. In fact, I have never personally had a need [...]
