1.16 - SQLBits Registration

It's a shame that this is based in Telford, rather than London but ...SQLBits Registration now open and can be found at http://www.sqlbits.com/

1.15 - Linked Server Alias

I take no credit for this - Taken from http://alexpinsker.blogspot.co.uk/2007/08/how-to-give-alias-to-sql-linked-server.html , but very useful.

There are no problems to add SQL linked server to the specific host running it. But what if you need to give it an alias, rather than use hostname as linked server name?
Here is how to do it:
1) Step 1:
• In SQL Server Management Studio open Linked Servers and then 'New Linked Server'.
• Inside of appeared wizard – Select the General tab.
• Specify alias name in "Linked server" field.
• Select SQL Native Client as provider.
• Add sql_server in "Product Name" field (that's the magic).
• In "Data Source" – specify name of the host to be used as linked server.
2) Step 2:
• In Security tab – specify proper security options (e.g. security context)
3) Step 3:
• In Server Options tab – put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.
4) Step 4:
• Enjoy.

1.14 - Product Function In SQL

As some of you may be aware there is no PRODUCT function in SQL so we have to find a solution ourselves. I searched for a few hours and found various ways of doing it using LOG but that did not work fully as it did not take account of negative numbers. I eventually started thinking about using a recursive CTE, tried, failed and looked for an alternative, when I remembered that you could use a variable as a recursive solution and hey presto! It worked; details below:

DECLARE @a DECIMAL(37, 9) = 1.00; -- As 1 * x will always start us off
WITH    cte
          AS (
               SELECT
                    ROW_NUMBER() OVER ( ORDER BY (
                                                   SELECT
                                                        ( NULL )
                                                 ) ) [row]
                FROM
                    sys.tables
UNION ALL --Add random data for test
(SELECT -5 [Row])
UNION ALL --Add random data for test
(SELECT 20 [Row])
UNION ALL --Add random data for test
(SELECT 5.26566666 [Row])
UNION ALL --Add random data for test
(SELECT -3 [Row])
)

    SELECT
            @a = ( @a * [row] ) --Set recursion of the variable
        FROM
            cte


SELECT
CONVERT(VARCHAR, CONVERT(MONEY, @a), 1) --Select in a nice format

1.13 - SQL Relay - Coming to a town near you

http://www.sqlrelay.co.uk/

1.12 - Get SQL Service accounts TSql

/*SQL Service Account*/
DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',@ServiceAccountName OUTPUT, N'no_output'
SELECT @ServiceAccountName

/*SQL Agent Service Account*/
DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLServeragent',
N'ObjectName',@ServiceAccountName OUTPUT, N'no_output'
SELECT @ServiceAccountName

1.11 - SSIS 2012 - Package Configurations Menu Option Missing

Useful Tip if you are developing BI in Visual Studio 2012 - http://dataqueen.unlimitedviz.com/2012/01/ssis-2012-package-configurations-menu-option-missing/

1.10 - Performance Of The IN Operator

I recently had to look at the performance of a query that was running with high CPU, and taking over 40+ seconds to run.
The scenario -
A stored proc used a view that joined to a table with a list of numbers from a table. This table was generated from a string that was passed to the stored proc as a string (possibly 10 values) separated by commas. This string was then split into a table via a UDF (table function) and this was then used in the IN operator to filter the number of results (it had to be dynamic). All indexes possible were made available, within the efficient running of the system.

If I changed the IN operator to a != or = this changed the query time to < 200ms from 40+Seconds but this was not fit for purpose as in this instance as the filter had to be dynamic. I then changed the IN statement that was contained in the WHERE clause to be part of a inner join and to join on the predicates that the IN operator was using ....and, wow! the number of logical reads on the table slumped from 4+million to 4 and the time down to 6 secs!! Not too bad as it was an offline, but crucial process.

So the long and the short is to remember; If you can join to it do so, as you may pay the price for utilisation of IN statements.