.

sql planner

Here is some code to help you view the planner (all cached):

create function SqlPlanner(@myhandle varbinary(max))
returns table
as
return select sql.text, cp.usecounts,cp.cacheobjtype,
cp.objtype, cp.size_in_bytes,
qp.query_plan
 from
 sys.dm_exec_sql_text(@myhandle) as sql cross join
 sys.dm_exec_query_plan(@myhandle)	as qp
 join sys.dm_exec_cached_plans as cp
 on cp.plan_handle = @myhandle;

go 

create view PlannerCached
as
select sp.* from sys.dm_exec_cached_plans   as cp
cross apply SqlPlanner(cp.plan_handle) as sp
go

select * from PlannerCached
go

--dbcc freeproccache

you can also create execution plans as follow:

exec sp_create_plan_guide
@name =N'test123',
@stmt = N'select * from core.patients',
@type = N'SQL',
@module_or_batch = N'select * from core.patients;',
@params=null,
@hints = 'OPTION (test)';

Working with hierachyid in SQL

Using hierachyid to store things in tree structures:

declare @myHid1 hierarchyid;
declare @myHid2 hierarchyid;
set @myHid1 = '/1/2/3/1/';
set @myHid2 = '/1/2/2/4/';
select  hyid.ToString() from
 (select @myHid1 as hyid
 union
 select @myHid2 as hyid) as a
  order by hyid

Returns (depth first)

(hyid)
/1/2/2/4/
/1/2/3/1/
  ** Please note all hierachyids ends on a /

– adding length produces breadth first sort

declare @myHid1 hierarchyid;
declare @myHid2 hierarchyid;
set @myHid1 = '/1/2/2/1/';
set @myHid2 = '/1/1/2/1/2/';
select hyid.ToString() from
 (select 4 as len, @myHid1 as hyid
 union
 select 5 as len, @myHid2 as hyid) as a
 order by len, hyid

If you have a table as follow:

Table Staff
(
id int identity primary key,
name nvarchar(50),
node hierarchyid
)
with records:
1, 'Mike', '/'
2, 'Sam', '/1/'
3, 'Pete', '/1/'

this is your code

select S.node.ToString() as node, S.name, S2.name as ReportTo
from Staff as S
join Staff as S2
on S2.node.ToString() = left(S.node.ToString(), 
	len(S.node.ToString()) + 1 -
	    charindex('/', reverse(S.node.ToString()),2 ))

With results:

node, staff name, staff boss name

also explore:
.. where node.IsDescendantOf(‘/3/’) = 1 and node.GetLevel() = 2 ..
select cast(‘/2/’ as hierarchyid)
.GetDescendantOf(‘/3/1/’,'/2/3/’).ToString()
SELECT hierarchyid::Parse(‘/5/4/3/2/1/’)
GetRoot()

depth-first

depth-first

breadth-first

breadth-first

 

working with aggregates in sql

here is an example of using aggregates inline:

select name, surname, score - avg(score) over (partition by surname) as difference
order by surname, difference

– degenerate case:
or if you don’t want to partition just use a constant for partition:

select name, surname, score - avg(score) over (partition by 0) as difference
order by surname, difference

rownumbers in SQL

Working with Row numbers:

SELECT PatientId, FirstName, LastName, ROUND(RiskScore,2,1),
ROW_NUMBER() OVER(ORDER BY DOB DESC) AS Row
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;

To partition as well (by e.g. ward, so that each ward has a row 1…xxx)

SELECT PatientId, FirstName, LastName, ROUND(RiskScore,2,1),
ROW_NUMBER() OVER(PARTITION BY WardId ORDER BY DOB DESC) AS Row
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;

You can also rank in a similar way – one rank no per grouping (ward)
Also consider Dense_Rank but it uses more resource intensive

SELECT PatientId, FirstName, LastName, Ranking,
rank() OVER(ORDER BY WardId) AS Ranking
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;

To allow for paging:

WITH MyTable (PatientId, FirstName, LastName, Risk) AS
(
SELECT PatientId, FirstName, LastName, ROUND(RiskScore,2,1),
ROW_NUMBER() OVER(PARTITION BY WardId ORDER BY DOB DESC) AS RowNumber
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;
) 
SELECT PatientId, FirstName, LastName, Risk
FROM MyTable
WHERE RowNumber BETWEEN 50 AND 60;

ntiling (e.g. breaking it in 200 amount of tiles):

SELECT PatientId, FirstName, LastName, Ranking,
ntile(200) OVER(ORDER BY WardId) AS Ranking
FROM Patients
WHERE Gender IS NOT NULL AND Gender = 'm'
ORDER BY EstimatedDischargeDate;

SQL CTEs

This is a basic example of how to use CTE (common table expression)

with TableName1 as
(query 1) ,
TableName2 as 
(query 2)

select TableName1.color, TableName2.displaytxt
from TableName1 join TableName2
on TableName1.id = TableName2.id
join OtherRealTable
on TableName1.id = OtherRealTable.id

You can also specify column names as follow:

with TableName1 (pay, ave_pay, tot_pay) as
(select a, b, c from d)
select pay, ave_pay, tot_pay from TableName1

 

creating dynamic SQL tables using ‘pivot’ aka “cross tabulation of columns & values”

Need to keep on adding columns to a table in a dynamic way and store it in a database?

Here follows a prototype of the solution:

--drop table MyPasTable
create table MyPasTable
(
  SpellId int,
  ColName varchar(20),
  Value varchar(20)
);  

insert into MyPasTable values
(38753,   'PatientName',      'john'),
(38753,   'Surname',      'smith'),
(38753,   'S2',      '23'),
(32255,   'PatientName',     'jack'), 
(38755,   'S2',     '2154'), 
(38755,   'S5',     '154'), 
(38755,   'PatientName',    'pete')

DECLARE @colsPivot AS NVARCHAR(MAX),
    @colsUnpivot as NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT distinct ',' + quotename(ColName) 
                    from MyPasTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsUnpivot = stuff((select ','+quotename(C.Name)
         from sys.columns as C
         where C.object_id = object_id('MyPasTable') and
               C.name LIKE 'value'
         for xml path('')), 1, 1, '')

set @query 
  = 'select *
      from
      (
        select SpellId, ColName, val
        from MyPasTable
        unpivot
        (
          val
          for SpellIdx in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        max(val)
        for ColName in ('+ @colspivot +')
      ) p'

exec(@query)

drop table MyPasTable

That would result in the following output

SpellId PatientName S2 S5 Surname
32255 jack NULL NULL NULL
38753 john 23 NULL smith
38755 pete 2154 154 NULL

Install SQL Profiler – without uninstalling anything

To install SQL Profiler (without uninstalling anything)
run the following command from the command prompt in your installation folder (e.g. d:\ if on cd)
setup.exe /FEATURES=Tools /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=[ENTER INSTANCE NAME] /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\NETWORK SERVICE” /IACCEPTSQLSERVERLICENSETERMS

you might possibly have to leave out the last parameter if it fails on it: /IACCEPTSQLSERVERLICENSETERMS

#if you receive the following error :
Error result: 1152035024
Result facility code: 1194
Result error code: 43216
it’s because you use the machine specific (x86 / x64 etc) instead of the generic (root folder) setup

connectionstrings

for a local database:

<add name="Database1ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"
      providerName="System.Data.SqlClient" />

Obtaining the row counts for each table

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY row_count DESC
drop table #counts

SQL Server 2008 – Rule “Previous release of Microsoft Visual Studio 2008″ failed

Although you already have VS2008 SP1 installed.

Fix:

start -> run -> “cmd”

Setup /ACTION=install /SkipRules=VSShellInstalledRule

you will still get an error but it installs fine.

 

The SQL Server 2008 installer is checking the HKLM\SOFTWARE\Microsoft\DevDiv\XXX\Servicing\9.0\SP reg keys to tell if SP1 is installed. If you inspect those keys and find any SP keys with the value “0,” that is your road block.