The following example will return two columns (first name, last name) based on a single, concatenated, comma separated name. table —— name —— Chin, Cheryl Frank, Robert SELECT RIGHT([name], LEN([name]) – CHARINDEX(’,’, [name]) – 1) AS [first], LEFT([name], CHARINDEX(’,’, [name])-1)… Continue Reading
You may receive the following errors if you incorrectly alias your column names: Invalid column name No column was specified Check to make sure that your column names are encased in brackets and are not encased by quotes. Incorrect SELECT… Continue Reading
1 2 3 4 5 6 7 8 DECLARE @string varchar(50), @execSql varchar(1000) SET @string = ‘I like kittens’ SET @execSql = ‘INSERT INTO table (column) SELECT ”’ SET @execSql = @execSql + REPLACE(@string, ‘ ‘, ”’ UNION ALL… Continue Reading
Traditional arithmetic would tell you that 5.1 = 5.10. However, arithmetic can’t compare software versions such as 5.1 to 5.10, since 5.10 is the ‘newer’ version. The function below receives two version numbers and returns which of the two is… Continue Reading
The APPLY operator is similar to the JOIN operator, however it specifically allows you to use table functions with parameters to join the results. table1 column —— a1 a2 a3 a4 a5 Using the query below, CROSS APPLY will join… Continue Reading
A common table expression (CTE) is used in this example to create a temporary result set with a sequential number that allows us to do the arithmetic required to calculate percent change.
To select the columns in a specified table in SQL Server, use this code: SELECT c.name FROM sys.columns c, sys.tables t WHERE t.name = ‘table_name’ AND c.object_id = t.object_id
The MSDN article on decimal and numeric data types explains that precision in the decimal data type is the: …total number of decimal digits that can be stored, both to the left and to the right of the decimal point.… Continue Reading
I came across a question on Stack Overflow in which an individual asked what using INCLUDE with your INDEX does.
Recently I encountered a table of astronomic size. The table is 27.7 GB with ~32.8 million rows. The table only had a single index and the query (below) fetches 4 columns using 3 aggregates filtering the last 6 months from… Continue Reading