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 [...]
Archive of entries posted on July 2010
SQL Server SPID – What is it?
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, [...]
Trunc Date in SQL Server
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 [...]
Drop Temp Table If Exists
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 [...]
Insert From Select
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 [...]
Convert Text String to Numbers (Int)
Here is a simple method to convert a text string to numbers (or an integer). This method evaluates each value in a column to determine if the datatype is numeric. If it is, then it converts it to an integer. SELECT CASE WHEN ISNUMERIC(PostalCode) > 0 THEN CAST(PostalCode AS INT) ELSE 0 END FROM SalesLT.Address [...]
List All Columns in Database or Server
To get a list of all columns within a database, you can use the ANSI compliant INFORMATION_SCHEMA.COLUMNS system view. SELECT * FROM INFORMATION_SCHEMA.COLUMNS In order to get all the columns in all the databases however, you need to loop through the databases. To do this, you can use the undocumented sp_MSForEachDB procedure that Microsoft ships. [...]
List All Databases
To list all databases in a SQL Server instance, run the following: SELECT * FROM sys.sysdatabases This also outputs metadata about each database including createdate, compatibility level, and the MDF filename. Popular search terms:sql Server 2008 list all databasesList all databases ownerstsql list of all databases in a sql instancesql server 2008 list databases with [...]
List All Tables in a Database
There are a few methods for listing all the tables in a database. Some methods will be able to show more specifics than others due to the capabilities inherent in the tables being joined. I’ll show you the simplest way first which is probably the most supported way. SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME, * [...]
