Helpful Resources on SQL & Development

Going old school (Early 2000-ish) with a list of links on a page. Oh, Yeah!!
(No particular order -- will build on)

SQL:

SQL Bits (Video) http://sqlbits.com/
Brent Ozar Unlimited (Video & Blog) http://www.brentozar.com/
Strictly SQL(Blog) http://strictlysql.blogspot.com/
SQL Blog (Blog) http://sqlblog.com/
BI SQL Pass http://bi.sqlpass.org/
SQL Server Central http://www.sqlservercentral.com/
DBA Stack Exchange http://dba.stackexchange.com/
Katie & Emil (Video & Blog) http://www.katieandemil.com/
SQL Server Pro http://sqlmag.com/
SQL Pass Session Recordings (Videos)
http://www.sqlpass.org/LearningCenter/SessionRecordings.aspx
List of SQL MVP Blogs: http://technet.microsoft.com/en-us/sqlserver/bb671052.aspx

.Net

Channel 9 (Video) http://channel9.msdn.com/

Microsoft Virtual Academy (Video) http://www.microsoftvirtualacademy.com/

Scott Hanselman http://www.hanselman.com/blog/
Stack Overflow http://stackoverflow.com/
 

Comic Relief:

Midnight DBAs (Video) http://midnightdba.itbookworm.com/
The DailyWTF http://thedailywtf.com/
XKCD http://xkcd.com/
Average Data Miner (My Tumblr Account) http://avgdataminer.tumblr.com/
DBA Reaction http://dbareactions.tumblr.com/
Dev Ops Reaction http://devopsreactions.tumblr.com/
TSql Jokes http://tsqljokes.tumblr.com/

MISC

Free School Courses Online https://www.coursera.org/

MIT Free Courses Online: http://ocw.mit.edu/courses/find-by-topic/

Stanford Free Online Courses: http://online.stanford.edu/courses/

 

A couple of Linq to Sql Query Examples

Just a quick reference of a couple basic query conversions for Linq to SQL

Code Snippet

21. //inner join
22.             //SELECT [t0].[CountyName]
23.             //FROM [dbo].[County] AS [t0]
24.             //INNER JOIN [dbo].[State] AS [t1] ON [t0].[StateID] = [t1].[StateID]
25.             //WHERE LOWER([t1].[StateName]) = @p0
26.             var counties =  from c in db.Counties
27.                             join st in db.States on c.StateID equals st.StateID
28.                             where st.StateName.ToLower().Equals("florida")
29.                             select c.CountyName;
30.  
31.  
32.             //left join -- use DefaultIfEmpty and new object
33.             //SELECT [t0].[CountyName],
34.             //(CASE WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(50),@p0)
35.             //      ELSE CONVERT(NVarChar(50),[t2].[StateName])
36.             //      END) AS [StateName]
37.             //FROM [dbo].[County] AS [t0]
38.             //LEFT OUTER JOIN
39.             //(
40.             //      SELECT 1 AS [test], [t1].[StateID], [t1].[StateName]
41.             //      FROM [dbo].[State] AS [t1]
42.             //) AS [t2] ON [t0].[StateID] = [t2].[StateID]
43.             var counties2 = from c in db.Counties
44.                             join st in db.States on c.StateID equals st.StateID
45.                             into stateCounties
46.                             from co in stateCounties.DefaultIfEmpty()
47.                             select new { CountyName = c.CountyName, StateName = (co == null)? "": co.StateName };
48.  
49.  
50.             //Same table query
51.             //SELECT [t0].[CountyName]
52.             //FROM [dbo].[County] AS [t0],
53.             //     [dbo].[County] AS [t1]
54.             //WHERE ([t0].[CountyName] = [t1].[CountyName]) AND ([t0].[CountyID] <> [t1].[CountyID])
55.             var counties3 = from c1 in db.Counties
56.                             from c2 in db.Counties
57.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
58.                             select new
59.                             {
60.                                 CountyName = c1.CountyName,
61.                             };
62.  
63.  
64.  
65.  
66.             var counties4 = (from c1 in db.Counties
67.                             from c2 in db.Counties
68.                             where c1.CountyName == c2.CountyName && c1.CountyID != c2.CountyID
69.                             group c1 by c1.CountyName into dups
70.                             select new
71.                             {
72.                                 CountyName = dups.Key,
73.                                 Count = dups.Count()
74.                             }
75.                             ).OrderBy(n => n.CountyName);

Some resources I found useful:
http://codesamplez.com/database/linq-to-sql-join-tutorials
http://weblogs.asp.net/rajbk/archive/2010/03/12/joins-in-linq-to-sql.aspx
http://msdn.microsoft.com/en-us/library/bb386913.aspx
   More specifically to joins:
   http://msdn.microsoft.com/en-us/library/bb399397.aspx

View overall I/O statistics for all databases in your server

A sql script to find the I/O statistics for all databases on the server. This can be helpful for

"drafting a server consolidation strategy, or pointing to eventual need to check perf counters if I/O is waiting more than expected"

- From "Blog do Ezequiel" http://blogs.msdn.com/b/blogdoezequiel/archive/2012/03/08/the-sql-swiss-army-knife-3-view-i-o-per-file-updated.aspx

Code Snippet

-- 2012-03-08 Pedro Lopes (Microsoft) pedro.lopes@microsoft.com (http://blogs.msdn.com/b/blogdoezequiel/)
--
-- Checks the cumulative IO per database file and related information
--
SELECT f.database_id, DB_NAME(f.database_id) AS database_name, f.name AS logical_file_name, f.[file_id], f.type_desc,
    CAST (CASE
        -- Handle UNC paths (e.g. '\\fileserver\readonlydbs\dept_dw.ndf')
        WHEN LEFT (LTRIM (f.physical_name), 2) = '\\'
            THEN LEFT (LTRIM (f.physical_name),CHARINDEX('\',LTRIM(f.physical_name),CHARINDEX('\',LTRIM(f.physical_name), 3) + 1) - 1)
            -- Handle local paths (e.g. 'C:\Program Files\...\master.mdf')
            WHEN CHARINDEX('\', LTRIM(f.physical_name), 3) > 0
            THEN UPPER(LEFT(LTRIM(f.physical_name), CHARINDEX ('\', LTRIM(f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS NVARCHAR(255)) AS logical_disk,
    fs.size_on_disk_bytes/1024/1024 AS size_on_disk_Mbytes,
    fs.num_of_reads, fs.num_of_writes,
    fs.num_of_bytes_read/1024/1024 AS num_of_Mbytes_read,
    fs.num_of_bytes_written/1024/1024 AS num_of_Mbytes_written,
    fs.io_stall/1000/60 AS io_stall_min,
    fs.io_stall_read_ms/1000/60 AS io_stall_read_min,
    fs.io_stall_write_ms/1000/60 AS io_stall_write_min,
    ((fs.io_stall_read_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_read_pct,
    ((fs.io_stall_write_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_write_pct,
    ABS((sample_ms/1000)/60/60) AS 'sample_HH',
    ((fs.io_stall/1000/60)*100)/(ABS((sample_ms/1000)/60))AS 'io_stall_pct_of_overall_sample'
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]
ORDER BY 18 DESC

SQL CLR Aggregate: Median

Since Sql Server doesn't have an aggregate for Median, I figured, that I'll create my first SQL CLR to handle this problem.

So, I created my intial project, using the .Net Framework 3.5 and C# SQL CLR aggregate template, and started coding:

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;


[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = false,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)]
public struct Median : IBinarySerialize
{
    //Variables to hold the values;
    private List<double> ld;

    public void Init()
    {
        ld = new List<double>();
    }

    public void Accumulate(SqlDouble Value)
    {
        if (!Value.IsNull)
        {
            ld.Add(Value.Value);
        }
    }

    ///

    /// Merge the partially computed aggregate with this aggregate.
    ///
    /// The other partial results to be merged
    public void Merge(Median Group)
    {
        this.ld.AddRange(Group.ld.ToArray());
    }

    ///

    /// Called at the end of aggregation, to return the results.
    ///
    /// The median of all inputted values
    public SqlDouble Terminate()
    {
        //debug: return (SqlDouble)ld.Count;

        //special case 0 values
        if (ld.Count == 0)
            return SqlDouble.Null;

        ld.Sort();
        int index = (int)ld.Count / 2;

        if (ld.Count % 2 == 0)
        {
            return (SqlDouble)(((double)ld[index] + (double)ld[index - 1]) / 2);
        }
        else
        {
            return (SqlDouble)((double)ld[index]);
        }
    }


    #region IBinarySerialize Members

    public void Read(System.IO.BinaryReader r)
    {
        int cnt = r.ReadInt32();
        this.ld = new List<double>(cnt);
        for(int i = 0; i < cnt; i++)
        {
            this.ld.Add(r.ReadDouble());
        }
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(this.ld.Count);
        foreach (double d in this.ld)
        {
            w.Write(d);
        }
    }

    #endregion
}

Some of the intial changes that I've made from the default:
1) The SqlUserDefinedAggregate default was Format.Native. The problem with using the default Serializer is that I needed a way to store my values, preferably in a List. By changing the format to Format.UserDefined  -- I was able to create my own serializer. In this case I used the IBinarySerialize interface.

2) I also changed the default IsInvariant attributes.

• IsInvariantToDuplicates - set to false since I want duplicates.
• IsInvariantToNull - set to false since I will handle Null values in my code.
• IsInvariantToOrder - set to false, I can do this in code.

Once the code is compiled, you will need to place the dll on the same machine as the SQL Server is located.

Using the following query to set the assembly and aggregate name:

Code Snippet

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Median')
   DROP AGGREGATE Median
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyClrCode')
   DROP ASSEMBLY MyClrCode
go


CREATE ASSEMBLY MyClrCode
FROM 'C:\Median.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE AGGREGATE dbo.Median
(@input float)
RETURNS float
EXTERNAL NAME MyClrCode.Median

Once done, time to do some testing. An example:

Code Snippet

--Test 1 value
select
dbo.Median(x.y)
from
(
    select 1 as y
)x
GO

--Test Accuracy
select
dbo.Median(x.y)
from
(
    select 1 as y
    union all
    select 2 as y
)x
GO

Median in SQL

Median is not a default aggregate in SQL-Server, but is sometime a perferable statistical function than Average. So here is quick tip on how I got the median:

Define: Median -

"The median of a finite list of numbers can be found by arranging all the observations from lowest value to highest value and picking the middle one. If there is an even number of observations, then there is no single middle value; the median is then usually defined to be the mean of the two middle values" - Wikipedia http://en.wikipedia.org/wiki/Median

By using ROW_NUMBER() function twice I can get my approximation of where the median is located.

For Example:
ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY Current ASC

-and-

ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY Current DESC

By using these two values I can pull the absolute value from their subtraction

ABS(ASCRow - DescRow)

To get an approximation of where it is associated with the median. Now if the values you are sorting are distinct, then you can find the median by looking for numbers that are less than or equal to 1 and then taking their average.

ABS(ASCRow - DescRow) <=1

In my case I can get more than a count of 2 numbers that are at the median, in this case I did another ROW_NUMBER function but this time order on the absolute difference and then I selected the top row in an outer query:

ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY MedianDistance) as ROW_NUM


This is where I stopped, even though there is still the chance of not getting the "true" Median. I would basically need to go further and pull all data points with the same value as the MedianDistance for those partitions and do an average.

Interview question: What structure would you use to represent shapes in a database?

OK, A few months ago, my friend told me of an interview question that I found interesting. The question that was given to him, paraphrasing: "If you had to set up a database to hold shapes, what would your table structure look like?". The point of the question was for him to convert the concept of a class shape that might be used in an object oriented language to be converted to a database structure. Which is why I was stuck on this question, with no simple straight forward solution.

I then started thinking about this question, again. The idea of the question was to see what tables and columns would you set up to make this feasible. I wanted an easy solution with minimal work, so I avoided the polymorphism concept and went to a simpler definition of a shape (polygon).

I figure I would make 1 table called SHAPE with 2 columns and make the assumption that the shapes are defined as "simple polygons":


ID int, COORDINATES varchar





The ID is obviously the primary key for each shape.


The Coordinates is a order list of Cartesian coordinates going counter-clockwise.

Now, given a shape, the most common questions asked would be what is the perimeter and area of said shape. I could easily make 2 store procedures to find the perimeter and area.

To figure out the perimeter, I just need to add up the distance from each point, simple enough.

To figure out the area of the polygon, I knew there had to be a formula that would do this for me, and thanks to wikipedia I found one:





"The formula was described by Meister in 1769 and by Gauss in 1795. It can be verified by dividing the polygon into triangles".


I was looking around, to see if others seen this question. I did find something similar at http://lists.mysql.com/mysql/207823

If I needed to query the table for certain shapes, then it would be good to add an additional column called: Points int

So, if I needed to query the table for triangles only then I can do a search on points = 3.

Changing Dynamic SQL to Static SQL

I've been on this project for a couple of weeks now, changing store procedures that use dynamic sql to a static sql. The reason for this change is too speed up the store procedures.

The store procedure might have a dynamic sql statement like:

SET @tsql = 'SELECT * FROM TABLE1
WHERE TABLE1.Name = ' + @Name

IF @ID <> ''
@tsql = @tsql + ' AND TABLE1.ID = ' + @ID

exec sp_executeSql @tSqlQuery

This would be changed to:

SELECT * FROM TABLE1 WHERE TABLE1.Name = @Name
AND( (@ID <> '' AND TABLE1.ID = @ID) OR
(@ID = '' ))

Now came the problem if they dynamically set a column to be sorted:

IF LTRIM(RTRIM(@sortColumn)) <> ''
@tsql = @tsql + 'ORDER BY ' + @sortColumn

This unfortunately had to be solved by making a case statement for all possible columns that are returned. So in this case this table returns only two columns (name and id):

SELECT NAME, ID FROM TABLE1
WHERE TABLE1.Name = @Name AND ((@ID <> ''
AND TABLE1.ID = @ID) OR (@ID = '' ))
ORDER BY
CASE @sortColumn WHEN 'ID' THEN ID ELSE NULL END,
CASE @sortColumn WHEN 'NAME' THEN NAME ELSE NULL END

The reason for the seperate case statements in the example is because it can only return one data type. If NAME is of varchar and ID is of int, then they have to be separated.

Well, that's a very basic and simple run down of what I've been doing. I do run into larger more complex store procedures and other situations. For example, when a dynamic sql statement is using a table name as a variable.

BizTalk Query: Find what schemas are deployed.

use biztalkmgmtdb
select msgtype, body_xpath, clr_namespace, clr_typename, clr_assemblyname, schema_root_name,
docspec_name
from bt_DocumentSpec
order by msgtype --(which is the schema-name)
--order by date_modified desc -- (probably the date deployed?)

A useful BizTalk Sql Query

Found this sql statement on another blog that shows you what ports are still referencing a mapping. This is useful if you need to undeploy the maps and you are having trouble finding what port might be still referencing.

select
'RcvPort' PortType,
r.nvcName Port,
item.name MapName,
assem.nvcName Assembly,
nSequence, indoc_docspec_name, outdoc_docspec_name
from bts_receiveport_transform rt
inner join bts_receiveport r
on rt.nReceivePortID = r.nID
inner join bt_mapspec ms
on ms.id = rt.uidTransformGUID
inner join bts_assembly assem
on ms.assemblyid = assem.nID
inner join bts_item item
on ms.itemid = item.id
--order by Port, nSequence

union

select
'SendPort' PortType,
r.nvcName Port,
item.name MapName,
assem.nvcName Assembly,
nSequence, indoc_docspec_name, outdoc_docspec_name
from bts_sendport_transform rt
inner join bts_sendport r
on rt.nSendPortID = r.nID
inner join bt_mapspec ms
on ms.id = rt.uidTransformGUID
inner join bts_assembly assem
on ms.assemblyid = assem.nID
inner join bts_item item
on ms.itemid = item.id

order by PortType, Port, nSequence

SQL Interview Questions: Definitions

Questions:
1) What is a SARGABLE predicate?
2) What is DAS? What is NAS? What is a SAN? Name the two main types of SAN networks. What is the common name for the device used to interface to a SAN?
3) What is a LUN? How is it used with a SAN?
4) What is a RAID? What RAID format is the recommended best practice for SQL Server, and why?
5) What is an INSTEAD-OF trigger used for?
6) What is a LOB?





Answers?:
1) Predicates that do searching, like Year > 2000.
2) Direct Attached Storage, Network Attached Storage, Storage Area Network, Fibre Channel and iSCSI, Host Bus Adapter.
3) Logical Unit Number , Each device(or LUN) on the storage area network (SAN) is "owned" by a single computer (or initiator)
4) RAID (Redundant Array of Independent Disks). A collection of disk drives that offers increased performance and fault tolerance. There are a number of different RAID levels. The three most commonly used are 0, 1, and 5. Microsoft recommends a RAID that would provide the best write performance. For each write request a RAID0 would write once, RAID1 or RAID10 would write twice, and RAID5 would write 4 times. RAID 0 is never recommended, so this leaves RAID10 as the recommended.

Here are the RAIDs:
Level 0: striping without parity (spreading out blocks of each file across multiple disks).
Level 1: disk mirroring or duplexing.
Level 2: bit-level striping with parity
Level 3: byte-level striping with dedicated parity. Same as Level 0, but also reserves one dedicated disk for error correction data. It provides good performance and some level of fault tolerance.
Level 4: block-level striping with dedicated parity
Level 5: block-level striping with distributed parity
Level 6: block-level striping with two sets of distributed parity for extra fault tolerance
Level 7: Asynchronous, cached striping with dedicated parity

5) There are 3 types of triggers: BEFORE, AFTER and INSTEAD OF. BEFORE is used to affect the row before the trigger event executes. AFTER is used to trigger actions are activiated for the affected rows. INSTEAD OF is used to triggers its action for each row in the affected row instead of using the trigger event.

6) Locator OBject.

SQL Interview Questions:

Questions:
1) What is the name of the SQL Server query language?
2) What is the difference between DML and DDL?
3) Name the four main types of DML Query operations.
4) Name three reasons to use a stored procedure.
5) What is the difference between a stored procedure and an extended stored procedure? Where is an extended stored procedure stored?
6) What is the difference between "Truncate" and "Delete From"?
7) What is a SQL Server Page, and how is it used? How big is a page? How much data space is available on each SQL Server Page?


Answers:
1) Transact-SQL (used by Microsoft and Sybase)
2)
DDL is Data Definition Language statements. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

SELECT - retrieve data from the/a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

3) From above most popular are Select, Insert, Update and Delete.

4)
Encapsulation - changing the inner implementation as often you like.
Security - secured at a database level, and help prevent SQL Injection attacks.
Performance - Improving network traffic, etc..

5)Extended store procedure is accessed as though it was a compiled SQL program. They are not written in Transact-SQL, they reside in DLLs.

6)
Truncate is DDL and drops all rows no ROLLBACK possible, no WHERE clauses possible. Truncate drops blocks created for the table without erasing the definition of the table, delete just the content of those blocks. Aditional to this, clustered tables cannot be truncated. DELETE is DML and uses various memory structures allowing ROLLBACK and WHERE clauses.

DELETE from table_name where... ;
TRUNCATE table table_name;

7) Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Pages are the basic unit of IO that SQL server uses. 8060-bytes are used for storing data.