Select Distinct a way to tell SQL Server to not return duplicate rows. Adding the distinct keyword makes it an expensive query because it essentially performs a group by for all of the returned columns. That being the case, it should only optimally be used on queries that return a small number of rows, or small number of columns. When using Select Distinct, you should keep in mind that this is generally a workaround to either improper joining of tables, or an improper where clause that is not limiting the result set to only returning one instance of a record. In order to utilize distinct, all you need to do, is place it before the select list. [cc lang=”sql”] SELECT DISTINCT * FROM yourtable [/cc] This will surely only return a unique instance of every record in the result.
Continue reading ...
A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session. Viewing SPIDs In order to view all the connections in SQL Server execute the following query. [cc lang=”sql”] SELECT * FROM sys.dm_exec_sessions [/cc] From here we see a session_id shown in the left hand column. This is also known as the SPID. To find the SPID for your current execution window run this. [cc lang=”sql”] SELECT @@SPID [/cc] Connection Settings Each SPID can have it’s own connection settings. Connection settings can be defined by the connection string or the default values for the SQL Server instance, so if two SPIDs come in through the same connection string, then they will also have the same connection settings. This is important because execution plans that are generated for queries are dependent on the connection settings. If two different SPIDs come in with different connection settings, then they will not use the same execution plan defined for a SQL statement. In order to view the connections settings execute the SQL Statement shown above. Transaction Isolation SPIDs can have their own transaction isolation levels defined globally. When set within a connection, all proceeding executions maintain the same transaction isolation. For more on isolation levels, view this Temp […]
Continue reading ...
In Oracle there is a function (trunc) used to remove the time portion of a date. In order to do this with SQL Server, you need to use the convert function. Convert takes 3 parameters, the datatype to convert to, the value to convert, and an optional parameter for the formatting style. It is this third parameter we will use in order to customize the format of the date. [cc lang=”sql”] — Month first SELECT CONVERT(varchar(12),GETDATE(), 101) — 06/29/2009 SELECT CONVERT(varchar(12),GETDATE(), 110) — 06-29-2009 SELECT CONVERT(varchar(12),GETDATE(), 100) — Jun 29 2009 SELECT CONVERT(varchar(12),GETDATE(), 107) — Jun 29, 2009 — Year first SELECT CONVERT(varchar(12),GETDATE(), 102) — 2009.06.29 SELECT CONVERT(varchar(12),GETDATE(), 111) — 2009/06/29 SELECT CONVERT(varchar(12),GETDATE(), 112) — 20090629 — Day first SELECT CONVERT(varchar(12),GETDATE(), 103) — 29/06/2009 SELECT CONVERT(varchar(12),GETDATE(), 105) — 29-06-2009 SELECT CONVERT(varchar(12),GETDATE(), 104) — 29.06.2009 SELECT CONVERT(varchar(12),GETDATE(), 106) — 29 Jun 2009 — Time only SELECT CONVERT(varchar(12),GETDATE(), 108) — 07:26:16 SELECT CONVERT(varchar(12),GETDATE(), 114) — 07:27:11:203 — Date Only No Time (SQL 2008) [thank you John] SELECT Cast(GetDate() AS date); — 08/12/2011 [/cc]
Continue reading ...
If you’re here then you’ve probably run into the situation where you’ve automatically created a temp table in your script, and every time you execute the script you have to drop the temp table manually. Yes, this can be a pain. I’ve done this myself many times. So here’s the easy solution. We need to check if the temp table exists within the TempDB database and if it does, we need to drop it. [cc lang=”sql”] IF OBJECT_ID(N’tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END [/cc] To replicate this, let’s run the following command in the same window multiple times: [cc lang=”sql”] — (Replace the product table below with your table) SELECT * INTO #Temp FROM SalesLT.Product [/cc] We get the error message: Now we simple add our snippet of code, and we are able to execute without having to manually drop anymore. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END — (Replace the product table below with your table) SELECT * INTO #Temp FROM SalesLT.Product [/cc]
Continue reading ...
The method of inserting records from one table to another differs depending on whether or not the target table already exists. Neither method is difficult, however one method requires more coding. Insert where target table does not exist If it does not, then you are in luck, there is a method that could not be simpler using SELECT..INTO. Select into automatically creates a table based on the result of a select statement. This select statement can be as simple or complex as you like. The only think you need to do, is add the line: INTO TABLENAME right above the FROM statement. Here goes: [cc lang=”sql”] — Copy all the records into a new table SELECT * INTO CustomerCopy FROM SalesLT.Customer — View the table SELECT * FROM CustomerCopy [/cc] It should be noted that this method does not create any keys that may be existing from the old table to the new table. Insert where target table does exist It’s not that this method is more difficult, it just requires more code. To do this, you need to use the INSERT INTO statement. When using this method, you need to specify the column names of the target table you want to insert, and the column names of the source table you are inserting from. Here is an example: [cc lang=”sql”] INSERT INTO [AdventureWorksLT2008].[SalesLT].[Customer] ( [NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[CompanyName] ,[SalesPerson] ,[EmailAddress] ,[Phone] ,[PasswordHash] ,[PasswordSalt] ,[ModifiedDate] ) SELECT [NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[CompanyName] ,[SalesPerson] ,[EmailAddress] ,[Phone] […]
Continue reading ...
