Wednesday, October 15, 2014

A Truly Random Post

Want to generate a truly random number? It's a little more tricky than it first seems.

First off, TSQL has a RAND function. Simple huh? Not so fast. We'll start with some definitions...

RAND Returns a pseudo-random value of the float datatype from 0 through 1, exclusive.
The value returned is based on the seed, hence providing the same seed always produces the same values.
It is difficult to use RAND in set based results as it is only evaluated once per batch.
It will return the same value for each of the row in a query if the same seed is used.
Therefore to use RANDeffectively you need to use different seed values.

CHECKSUM Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indexes.
It returns an integer and is good (meant) for generating a one way hash.

NEWID Returns a unique value of type uniqueidentifier i.e. a 16-byte GUID.
for example ; 0EFD9C6D-7FF5-4CB1-A71B-69CF8FF02B4B

ABS Returns the absolute value of the input, i.e. a positive number.

Generating Random Numbers

Method 1 :

This involves using both NEWID and RAND for 2 levels of randomness.
Using CHECKSUM to change a string into a given integer is repeatable (not random), although the values are far from being visually predictable.

Because fractional values are generated in a float i.e. less than 1, the result is multiplied by the maximum value and cast as an integer to generate whole numbers.

Firstly, the workings ...

DECLARE @data_newid  uniqueidentifier
DECLARE @data_int  int
DECLARE @data_float  float
DECLARE @maxvalue  int

SET @maxvalue = 100

-- Generate Unique 16 bit GUID
SET @data_newid = NEWID() 
-- Generate hash value integer of the GUID
SET @data_int = CHECKSUM(@data_newid)
-- Use the hash value to seed the RAND function
SET @data_float = RAND(@data_int)

-- Show Results
SELECT @data_newid as d_uniqueidentifier
SELECT @data_int as d_int
SELECT @data_float as d_float
SELECT CAST(@maxvalue * @data_float AS int) as randomnumber

The abridged version...

DECLARE @maxvalue int
SET @maxvalue = 100
SELECT CAST(@maxvalue * RAND(CHECKSUM(NEWID())) AS int) RandomNumber

Method 2 :

This involves recasting NEWID() first to varbinary, then to int. ABS is used to convert any negative results to positive. Because high values are generated by this method, we use %, the modulo operator to retrieve a result. If we want numbers up to and including 15, then we divide by 16 as 0 to 15 would be the remainder generated by modulo.

-- The Workings

DECLARE @data_newid  uniqueidentifier
DECLARE @data_varbinary varbinary
DECLARE @data_int int
DECLARE @maxvalue int

SET @maxvalue = 100

SET @data_newid = NEWID()
SET @data_varbinary = CAST(@data_newid AS varbinary)
SET @data_int = CAST(@data_varbinary as int)

SELECT @data_newid as d_uniqueidentifier
SELECT @data_varbinary as d_varbinary
SELECT @data_int as d_int
SELECT @data_int % (@maxvalue +1) as randomnumber

The abridged version...
DECLARE @maxvalue int
SET @maxvalue = 100
SELECT (ABS(CAST(CAST(NEWID() AS varbinary) AS int)) % (@maxvalue + 1)) RandomNumber

Method 3

Similar to Method 1, but only using NewID for the random element. Because a checksum can be negative, we use ABS to ensure the result is positive. Then we use the modulo operator like method 2, to get the remainder as an integer.

DECLARE @maxvalue int
SET @maxvalue = 100
SELECT ABS(CHECKSUM(NewId())) % (@maxvalue + 1) RandomNumber

Generating a Random number within a range

The following example adapts version 1 so that you can provide a lower and upper value for the number you want generated.

DECLARE @MaxValue int
DECLARE @MinValue int
SET @MaxValue = 5000
SET @MinValue = 0

SELECT CAST((((@MaxValue) + 1) - (@MinValue))* RAND(CHECKSUM(NEWID())) + (@MinValue) AS int) RandomNumber


The HOBT : Check your lucky numbers
Less than dot : SET Based random numbers
Blackwasp : Random numbers in TSQL Queries
Technet : Checksum
Technet : Floor
Technet : Round
Technet : Abs
Technet : % (Modulo)

Tuesday, October 14, 2014

Uses of FOR XML

Generating XML (Funnily enough)
SQL Server - FOR XML Basics

Grouped Concatenation  using FOR XML PATH
TSQL – Concatenate Rows using FOR XML PATH()

Splitting Strings
Generating and Splitting a Delimited String Column

Wednesday, October 8, 2014

Testing SQL Server Connectivity

Starting with the basics, can we see the server? (run ping from the command line)

PING ipaddress/hostname 

Is a response returned from the host?

This checks the host is present.
Caveats : PING (ICMP requests) might be blocked by a firewall.
If you can ping an IP Address by not a hostname, you need to investigate name resolution and DNS.

Check SQL is listening on TCP.

TELNET ipaddress/hostname 1433

Does Telnet open a session succesfully? (Does the command window go blank with a flashing cursor in the top left)

This tests the default instance of SQL Server on the specified (in this case default) port.
The SQL Listener/Browser service is never queried.
Annoyingly TELNET is not installed by default, hence has to be added to versions of Windows from Vista onwards.

If you don't connect successfully, your first checks should be

  1. Is the SQL Server service is running? 
  2. Does the firewall allows TCP 1433?

To connect to a named instance, lookup the port it is running on from it's TCP/IP Properties (under IP Addresses > IP All > TCP Dynamic Ports)
Then TELNET to the hostname with the discovered port number

TELNET ipaddress/hostname 1433

The presence of a SQL Named instance is broadcast by the SQL Browser Service which communicates on UDP port 1434.
If you can connect via port but not by specifying the instance name >

  1. check the named instance is running
  2. check the browser service is is running.
  3. check UDP port 1434 is allowed in the firewall configuration.

If you want to test connectivity via named pipes or Shared Memory you can use SQLCMD as detailed here... Steps to troubleshoot SQL connectivity issues

Further Reading

SQL Server Connectivity Issues with Named Instances

SQL Server – Finding TCP Port Number SQL Instance is Listening on

Using named instances? Test your DAC connection!

Steps to troubleshoot SQL connectivity issues

Thursday, October 2, 2014

TSQL : Revealing Foreign Key Constraints

Back in the day I blogged on revealing Referential Integrity via Information_Schema views. Here is an updated script to do the same.
    ForeignKeyName   =
   ,TableName   = OBJECT_NAME(fk.parent_object_id)  
   ,ColumnName   = COL_NAME(fkc.parent_object_id, fkc.parent_column_id)
   ,ReferencesTable  = OBJECT_NAME (fk.referenced_object_id)
   ,ReferencesColumn = COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) 
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc 
   ON fk.object_id = fkc.constraint_object_id