Feb
07
2009
0

SQL Tips – Identifying which version of SQL Server you are using

If you need to find out the version of SQL Server you are using, simply use the following script:

SELECT SERVERPROPERTY('productversion'),
       SERVERPROPERTY('productlevel'),
       SERVERPROPERTY('edition')
Written by John in: SQL Tips,Tips |
Feb
05
2009
2

SQL Tips – List of Top Level Domains

Here is SQL and a text list of the Top Level Domains currently in use on the internet

The list is free for all to use for any purpose.

The original data was sourced from IANA on Thursday 5 February 2008.

If you need to modify it further, remember that you can always use The Worlds Simplest Code Generator.

In the SQL version, the following have been escaped with an extra single quote:

  • Cote d’Ivoire
  • Korea, Democratic People’s Republic of
  • Lao People’s Democratic Republic

Please post a comment if you find this useful.
(more…)

Written by John in: SQL Tips,Tips |
Jan
31
2009
6

SQL Tips – List of New Zealand Towns and Cities

Here is SQL and a text list of the towns and cities in New Zealand.

The list is free for all to use for any purpose.

The original data was sourced from Wikipedia on Saturday 31 January 2008.

If you need to modify it further, remember that you can always use The Worlds Simplest Code Generator.

Please post a comment if you find this useful.
(more…)

Written by John in: SQL Tips,Tips |
Jan
29
2009
2

SHA256 and higher in SQL Server

As part of a new project I’m doing, I needed to use hashes.

A good source of information on hashes is available from wikipedia.

SQL has the built in function HASHBYTES but this only supports up to the SHA128 algorithm and I want to use SHA256.

I created myself a CLR in C#.net and in some moment of feeling generous, decided to share the code.

(more…)

Written by John in: C# Tips,SQL Tips,Tips |
Jan
24
2009
5

SQL Tips – List of US States

Here is SQL and a tab delimited list of the States of the USA for all to use.

The original data was sourced from Wikipedia.

Please post a comment if you find this useful.

Edit: I’ve beed advised that although it’s not technically a state, this list needs District of Columbia as most real world scenarios require it. It’s been added to the top of the list.

(more…)

Written by John in: SQL Tips,Tips |
Jan
23
2009
4

SQL Tips – List of Countries

Here is SQL and a tab delimited list of Countries for all to use.

The original data was sourced from Wikipedia.

Note that some countries have single quotes in their names, these need to be escaped for SQL. The SQL I’m supplying is escaped, but the tab delimited version is not.

Please post a comment if you find this useful.

(more…)

Written by John in: SQL Tips,Tips |
Jan
22
2009
0

SQL Tips – List of International Dialling Codes (IDC's)

I’ve been working on a new project and needed a list of International Dialling Codes to insert into my database.

Here are the results of my work for all to use.
The list is supplied as SQL and then tab delimited.

Note that Côte d’Ivoire (Ivory Coast) contains a single quote and as such needs escaping properly. The SQL is escaped, but the tab delimited version is not.

The original list was sourced from Wikipedia.

Please post a comment if you find this useful.

(more…)

Written by John in: SQL Tips,Tips |
Jan
13
2009
0

SQL Tips – Saving Changes Not Permitted

If you’re new to SQL 2008, you’ve probably seen the following message:

sql2008_nosave

The error reads:
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

You’ve probably done one of the following:

  • Changed the data type of a column
  • Dropped a column
  • Added a new column to the middle of the table
  • Changed the nullability of a column
  • Altering the order of columns

There is a very simple fix to this…
sql2008_nosave2

Go to tools > options from the menu bar.
Select “Designers” from the Left Hand Side menu of the options dialog.
Uncheck “Prevent saving changes that require table re-creation”
Click OK

Written by John in: SQL Tips,Tips |
Jan
12
2009
0

SQL Performance – Using LOWER() on case insensitive collations

Take a look at the following SQL.

SELECT firstName, lastName
FROM tUsers
WHERE LOWER(firstName) = 'john'

The above SQL is Syntactically correct. But, and this is a big but, unless you are using a case sensitive collation, you are wasting a lot of resources.

The following code will work fine when using the default (case INsensitive) collation.

SELECT firstName, lastName
FROM tUsers
WHERE LOWER(firstName) = 'JoHn' --Mixed case to prove a point

If you don’t believe me, look at the query plan for both queries while using a case insensitive collation.

Written by John in: SQL Tips,Tips |
Nov
14
2008
0

SQL Joins and Applies – Understanding APPLY

This is part of a four part post series on SQL Joins and Applies.
If you want to follow the examples given, you can get the T-SQL to create the tables, function and test data from my earlier post SQL Joins and Applies – Test Data.

The other posts in this series are:
SQL Joins and Applies – Understanding INNER JOIN
SQL Joins and Applies – Understanding CROSS JOIN
SQL Joins and Applies – Understanding OUTER JOIN

For some reason APPLY is not well known or understood. It is required when you have a Table-Valued Function (UDF, XML.nodes(), etc) and you need to process a table of values against the function and get a “Joined” result.

From Books Online (For an outer Join):

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

To simplify matters, let’s first look at timed when an APPLY is not required when using a Table-Valued Function in a query:

SELECT		tsi.sysID [sysID_table],
		tudf.calculatedValue [result_function]
FROM		dbo.tblSourceIntegers tsi
LEFT OUTER JOIN	dbo.udfGet5Rows(2) tudf
ON		tsi.sysID = tudf.calculatedValue

In the above query, the function is called with a constant which means the function can be evaluated to a table BEFORE the join is performed. Any values which do not rely on the rest of the query should work in place of the value of 2 – You can even use a random integer here.

Here is one that uses a value from the table:

SELECT	tsi.sysID [sysID_table]
FROM	dbo.tblSourceIntegers tsi
WHERE	8 IN (SELECT calculatedValue FROM dbo.udfGet5Rows(tsi.sysID))

Although the function is using a value from the main query, this is in the WHERE which happens after the table is loaded into memory and as such.

As soon as the function is being executed for each value in the query as part of the query result, an Apply needs to be used.

There are two types of applies, CROSS APPLY and OUTER APPLY.

  • CROSS APPLY will not “Join” if the Table-Valued Result is NULL
  • OUTER APPLY will “Join” if the Table-Valued Result is NULL

Note: If the table-Valued function returns a column with NULL in it, CROSS APPLY will still perform the “join”. The only time CROSS APPLY will not is when the Table-Valued Result is actually a NULL table.

Here is a sample CROSS APPLY:

SELECT		tsi.sysID [sysID_table],
		applied.calculatedValue
FROM		dbo.tblSourceIntegers tsi
CROSS APPLY	dbo.udfGet5Rows(tsi.sysID) applied

The result of the above Cross Apply is:

sysID_table calculatedValue
----------- ---------------
1           1
1           2
1           NULL
1           4
1           5
2           2
2           3
2           NULL
2           5
2           6
4           4
4           5
4           NULL
4           7
4           8
5           5
5           6
5           NULL
5           8
5           9

(20 row(s) affected)

Note that the NULLs on the Right hand column were one row out of 5 rows in the table returned from the Table-Valued Function.

Now let’s try the OUTER APPLY:

SELECT		tsi.sysID [sysID_table],
		applied.calculatedValue
FROM		dbo.tblSourceIntegers tsi
OUTER APPLY	dbo.udfGet5Rows(tsi.sysID) applied

The result of the above Outer Apply is:

sysID_table calculatedValue
----------- ---------------
1           1
1           2
1           NULL
1           4
1           5
2           2
2           3
2           NULL
2           5
2           6
NULL        NULL
4           4
4           5
4           NULL
4           7
4           8
5           5
5           6
5           NULL
5           8
5           9

(21 row(s) affected)

Note that the OUTER APPLY only returned one more row from when the function was called with a NULL value as the input parameter which the function returned a NULL result.

To review, an APPLY is useful for when you need to apply the values from a table to a Table-Valued function. APPLY is very resource intensive as it is recursive (The function will be run once for every matching value from the first table).

Written by John in: SQL Tips,Tips |