MrDee

SSMS and Cascadia Ligatures

2024-09-25T14:25:00.004+12:00

TIL: Ligatures are a thing in code editors.

According to Google, "A ligature is a glyph that combines the shapes of certain sequences of characters into a new form that makes for a more harmonious reading experience."

The new (to me) font "Cascadia" has variants, some with and some without ligatures. (see below) It is an upgrade from Consolas, the previous Microsoft developers font.

Why are there multiple versions? This is because some people are "show me the actual characters" sort of people and others are "Make it pretty" sort of people. For prose I like ligatures, for programming I need precision. If you need to squint to tell the difference between >= and <= then it has gone too far. Also editing them is wierd, having the cursor/insertion point half way through a character feels wrong.

Final note, SQL is not C#. Take your non-ANSI != and keep it out of my SQL.

SQL Column Type , updated Sep 2024

2024-09-13T15:09:00.001+12:00

CREATE OR ALTER FUNCTION [ddt].[fn_ColumnType]
  ( @TableName  VARCHAR(50) NULL
  , @ColumnName VARCHAR(50) NULL
  )
RETURNS VARCHAR(100)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ColumnType VARCHAR(100)

    SELECT @ColumnType = 
       CASE WHEN C.system_type_id =  34 THEN 'IMAGE'
            WHEN C.system_type_id =  35 THEN 'TEXT'               -- deprecated, use VARCHAR(MAX)
            WHEN C.system_type_id =  36 THEN 'UNIQUEIDENTIFIER'
            WHEN C.system_type_id =  40 THEN 'DATE'
            WHEN C.system_type_id =  41 THEN 'TIME('              + CONVERT(VARCHAR(10),C.scale) + ')'
            WHEN C.system_type_id =  42 THEN 'DATETIME2('         + CONVERT(VARCHAR(10),C.scale) + ')'
            WHEN C.system_type_id =  48 THEN 'TINYINT'
            WHEN C.system_type_id =  52 THEN 'SMALLINT'
            WHEN C.system_type_id =  56 THEN 'INT'
            WHEN C.system_type_id =  58 THEN 'SMALLDATETIME'
            WHEN C.system_type_id =  59 THEN 'REAL'
            WHEN C.system_type_id =  60 THEN 'MONEY'
            WHEN C.system_type_id =  61 THEN 'DATETIME'
            WHEN C.system_type_id =  62 THEN 'FLOAT'
            WHEN C.system_type_id =  98 THEN 'SQL_VARIANT'
            WHEN C.system_type_id =  99 THEN 'NTEXT'              -- deprecated, use NVARCHAR(MAX)
            WHEN C.system_type_id = 104 THEN 'BIT'
            WHEN C.system_type_id = 106 THEN 'DECIMAL('           + CONVERT(VARCHAR(10),C.precision) + ','+ CONVERT(VARCHAR(10),C.scale)+ ')'
            WHEN C.system_type_id = 108 THEN 'NUMERIC('           + CONVERT(VARCHAR(10),C.precision) + ','+ CONVERT(VARCHAR(10),C.scale)+ ')'
            WHEN C.system_type_id = 122 THEN 'SMALLMONEY'
            WHEN C.system_type_id = 127 THEN 'BIGINT'
            WHEN C.system_type_id = 165 THEN 'VARBINARY'
            WHEN C.system_type_id = 167 THEN
                 CASE WHEN C.max_length>0
                                        THEN 'VARCHAR('           + CONVERT(VARCHAR(10),C.max_length)      + ')'
                                        ELSE 'VARCHAR(MAX)' END
            WHEN C.system_type_id = 173 THEN 'BINARY('            + CONVERT(VARCHAR(10),C.precision)       + ')'
            WHEN C.system_type_id = 175 THEN 'CHAR('              + CONVERT(VARCHAR(10),C.max_length)      + ')'
            WHEN C.system_type_id = 189 THEN 'TIMESTAMP'
            WHEN C.system_type_id = 231 THEN
                 CASE WHEN C.max_length>0
                                        THEN 'NVARCHAR('          + CONVERT(VARCHAR(10),C.max_length/2)    + ')' -- NVARCHAR has 2 byte length characters
                                        ELSE 'NVARCHAR(MAX)' END
     
            WHEN C.system_type_id = 239 THEN 'NCHAR('             + CONVERT( VARCHAR(10),C.max_length/2)   + ')' -- NCHAR has 2 byte length characters
            ELSE                             '???( '              + CONVERT( VARCHAR(10),C.system_type_id) + ')'
       END 

    FROM       sys.tables T
    INNER JOIN sys.columns C ON C.object_id = T.object_id

    WHERE  T.type = 'U'
    AND    T.name = @TableName
    AND    C.name = @ColumnName;

    RETURN @ColumnType
END

SQL documentation, updated Jun 2024

2024-06-17T15:25:00.004+12:00

SELECT T.name AS TableName
    , C.colorder AS ColOrder
    , C.name AS ColName
    , CASE WHEN C.xtype =  34 THEN 'IMAGE'
           WHEN C.xtype =  35 THEN 'TEXT'
           WHEN C.xtype =  36 THEN 'UNIQUEIDENTIFIER'
           WHEN C.xtype =  40 THEN 'DATE'
           WHEN C.xtype =  41 THEN 'TIME('        

                 + CONVERT(VARCHAR,C.xscale) + ')'
           WHEN C.xtype =  42 THEN 'DATETIME2('   

                 + CONVERT(VARCHAR,C.xscale) + ')'
           WHEN C.xtype =  48 THEN 'TINYINT'
           WHEN C.xtype =  52 THEN 'SMALLINT'
           WHEN C.xtype =  56 THEN 'INT'
           WHEN C.xtype =  58 THEN 'SMALLDATETIME'
           WHEN C.xtype =  59 THEN 'REAL'
           WHEN C.xtype =  60 THEN 'MONEY'
           WHEN C.xtype =  61 THEN 'DATETIME'
           WHEN C.xtype =  62 THEN 'FLOAT'
           WHEN C.xtype =  98 THEN 'SQL_VARIANT'
           WHEN C.xtype =  99 THEN 'NTEXT'
           WHEN C.xtype = 104 THEN 'BIT'
           WHEN C.xtype = 106 THEN 'DECIMAL(' 

                 + CONVERT(VARCHAR,C.xprec) + ',' 

                 + CONVERT(VARCHAR,C.xscale)+ ')'
           WHEN C.xtype = 108 THEN 'NUMERIC(' 

                 + CONVERT(VARCHAR,C.xprec) + ',' 

                 + CONVERT(VARCHAR,C.xscale)+ ')'
           WHEN C.xtype = 122 THEN 'SMALLMONEY'
           WHEN C.xtype = 127 THEN 'BIGINT'
           WHEN C.xtype = 165 THEN 'VARBINARY'
           WHEN C.xtype = 167 THEN
                CASE WHEN C.prec>0
                              THEN 'VARCHAR('   

                 + CONVERT(VARCHAR,C.prec) + ')'
                              ELSE 'VARCHAR(MAX)' END
           WHEN C.xtype = 173 THEN 'BINARY('    

                 + CONVERT(VARCHAR,C.prec) + ')'
           WHEN C.xtype = 175 THEN 'CHAR('      

                 + CONVERT(VARCHAR,C.prec) + ')'
           WHEN C.xtype = 189 THEN 'TIMESTAMP'
           WHEN C.xtype = 231 THEN
                CASE WHEN C.prec>0
                              THEN 'NVARCHAR('   

                 + CONVERT(VARCHAR,C.prec) + ')'
                              ELSE 'NVARCHAR(MAX)' END

           WHEN C.xtype = 239 THEN 'NCHAR('     

                 + CONVERT(VARCHAR,C.prec) + ')'
           ELSE '??? ' + CONVERT(VARCHAR,C.xtype) + ' ???' END 

      AS ColType
    , C.isnullable AS ColNullable
FROM sysobjects T
INNER JOIN syscolumns C On C.ID = T.ID

WHERE T.xtype = 'U'
ORDER BY T.Name, C.ColOrder

Explaining common join types

2018-05-01T12:48:00.003+12:00

-- SQL Code to illustrate excellent, venn diagram, teaching aid

-- on SQL join types https://i.stack.imgur.com/UI25E.jpg


CREATE TABLE #Dude
    ( DudeID INTEGER
    , DudeName VARCHAR(20)
    , JobID INTEGER
    )

CREATE TABLE #Jobs
    ( JobID INTEGER
    , JobName VARCHAR(20)
    )

-- Dummy Data
INSERT INTO #Jobs VALUES (1, 'Boss')
INSERT INTO #Jobs VALUES (2, 'Manager')
INSERT INTO #Jobs VALUES (3, 'Employee')
INSERT INTO #Jobs VALUES (5, 'Slave')

INSERT INTO #Dude VALUES (1, 'Alice'    , 1)
INSERT INTO #Dude VALUES (2, 'Bob'      , 2)
INSERT INTO #Dude VALUES (3, 'Charlie'  , 3)
INSERT INTO #Dude VALUES (4, 'Dave'     , 4)
INSERT INTO #Dude VALUES (5, 'Erin'     , 1)
INSERT INTO #Dude VALUES (6, 'Frank'    , 2)
INSERT INTO #Dude VALUES (7, 'Gertrude' , 3)
INSERT INTO #Dude VALUES (8, 'Heath'    , 4)
INSERT INTO #Dude VALUES (9, 'Irene'    , 4)

--==--==--==--==--==--==--==--==--==--==--==--==--==--==
-- INNER JOIN
SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName
FROM #Dude D
INNER JOIN #Jobs J On J.JobID = D.JobID
ORDER BY D.DudeID

--==--==--==--==--==--==--==--==--==--==--==--==--==--==
-- LEFT JOIN
SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName
FROM #Dude D
LEFT JOIN #Jobs J On J.JobID = D.JobID
ORDER BY D.DudeID

--==--==--==--==--==--==--==--==--==--==--==--==--==--==
-- RIGHT JOIN
SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName
FROM #Dude D
RIGHT JOIN #Jobs J On J.JobID = D.JobID
ORDER BY D.DudeID

--==--==--==--==--==--==--==--==--==--==--==--==--==--==
-- FULL OUTER JOIN
SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName
FROM #Dude D
FULL OUTER JOIN #Jobs J ON D.JobID = J.JobID
ORDER BY D.DudeID

--==--==--==--==--==--==--==--==--==--==--==--==--==--==
-- LEFT JOIN - with null check
SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName
FROM #Dude D
LEFT JOIN #Jobs J On J.JobID = D.JobID
WHERE J.JobID IS NULL
ORDER BY D.DudeID

--==--==--==--==--==--==--==--==--==--==--==--==--==--==
-- RIGHT JOIN - with null check
SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName
FROM #Dude D
RIGHT JOIN #Jobs J On J.JobID = D.JobID
WHERE D.DudeID IS NULL
ORDER BY D.DudeID

-- Tidy Up
DROP TABLE #Dude
DROP TABLE #Jobs

Documenting Foreign Keys in SQL 2012

2016-09-21T11:52:00.006+12:00

SELECT F.name
, PT.name AS FromTab
, PC.name AS FromCol
, CT.name AS ToTab
, CC.name AS ToCol
FROM sys.foreign_keys F
INNER JOIN sys.foreign_key_columns FK ON FK.constraint_object_id = F.object_id
inner join sys.tables PT ON PT.object_id = FK.parent_object_id
inner join sys.tables CT ON CT.object_id = FK.referenced_object_id

inner join sys.columns PC ON PC.object_id = FK.parent_object_id
AND PC.column_id = FK.parent_column_id
inner join sys.columns CC ON CC.object_id = FK.referenced_object_id
AND CC.column_id = FK.referenced_column_id

ORDER BY PT.name, PC.column_id

SQL documentation, updated

2016-09-20T18:42:00.002+12:00

SELECT T.name AS TableName
, C.colorder AS ColOrder
, C.name AS ColName
, CASE WHEN C.xtype = 34 THEN 'IMAGE'
WHEN C.xtype = 35 THEN 'TEXT'
WHEN C.xtype = 36 THEN 'UNIQUEIDENTIFIER'
WHEN C.xtype = 40 THEN 'DATE'
WHEN C.xtype = 48 THEN 'TINYINT'
WHEN C.xtype = 52 THEN 'SMALLINT'
WHEN C.xtype = 56 THEN 'INT'
WHEN C.xtype = 58 THEN 'SMALLDATETIME'
WHEN C.xtype = 59 THEN 'REAL'
WHEN C.xtype = 60 THEN 'MONEY'
WHEN C.xtype = 61 THEN 'DATETIME'
WHEN C.xtype = 62 THEN 'FLOAT'
WHEN C.xtype = 98 THEN 'SQL_VARIANT'
WHEN C.xtype = 99 THEN 'NTEXT'
WHEN C.xtype = 104 THEN 'BIT'
WHEN C.xtype = 106 THEN 'DECIMAL(' + CONVERT(VARCHAR,C.xprec) + ',' + CONVERT(VARCHAR,C.xscale)+ ')'
WHEN C.xtype = 108 THEN 'NUMERIC(' + CONVERT(VARCHAR,C.xprec) + ',' + CONVERT(VARCHAR,C.xscale)+ ')'
WHEN C.xtype = 122 THEN 'SMALLMONEY'
WHEN C.xtype = 127 THEN 'BIGINT'
WHEN C.xtype = 165 THEN 'VARBINARY'
WHEN C.xtype = 167 THEN
CASE WHEN C.prec>0
THEN 'VARCHAR(' + CONVERT(VARCHAR,C.prec) + ')'
ELSE 'VARCHAR(MAX)' END
WHEN C.xtype = 173 THEN 'BINARY(' + CONVERT(VARCHAR,C.prec) + ')'
WHEN C.xtype = 175 THEN 'CHAR(' + CONVERT(VARCHAR,C.prec) + ')'
WHEN C.xtype = 189 THEN 'TIMESTAMP'
WHEN C.xtype = 231 THEN
CASE WHEN C.prec>0
THEN 'NVARCHAR(' + CONVERT(VARCHAR,C.prec) + ')'
ELSE 'NVARCHAR(MAX)' END

WHEN C.xtype = 239 THEN 'NCHAR(' + CONVERT(VARCHAR,C.prec) + ')'
ELSE '??? ' + CONVERT(VARCHAR,C.xtype) + ' ???' END AS ColType
, C.isnullable AS ColNullable
FROM sysobjects T
INNER JOIN syscolumns C On C.ID = T.ID

WHERE T.xtype = 'U'
ORDER BY T.Name, C.ColOrder

Remote Desktop (RDC) shortcut keys

2011-09-13T13:17:00.001+12:00

Original Article

Shortcut key	Description
ALT+PAGE UP	Switches between programs from left to right.
ALT+PAGE DOWN	Switches between programs from right to left.
ALT+INSERT	Cycles through the programs in the order they were started.
ALT+HOME	Displays the Start menu.
CTRL+ALT+BREAK	Switches the client between a window and full screen.
CTRL+ALT+END	Brings up the Windows Security dialog box.
ALT+DELETE	Displays the Windows menu.
CTRL+ALT+Minus (-) symbol on the numeric keypad	Places a snapshot of the active window, within the client, on the Terminal server clipboard (provides the same functionality as pressing PrintScrn on a local computer.)
CTRL+ALT+Plus (+) symbol on the numeric keypad	Places a snapshot of the entire client window area on the Terminal server clipboard (provides the same functionality as pressing ALT+PrintScrn on a local computer.)

Disaster Recovery: What to do when the SA account password is lost in SQL Server

2011-09-13T12:44:00.002+12:00

Instruction here

How to Restart Windows via Remote Desktop

2011-09-13T12:43:00.000+12:00

CTRL+ALT+END

Debunking the 12 Key Myths of EHR Implementation

2010-11-09T09:21:00.001+13:00

Click here

SQL date table script

2010-10-07T17:04:00.001+13:00

Script to create a date table function

SQL builds

2010-09-14T09:27:00.000+12:00

Microsoft SQL Server 2008, 2005, 2000 and 7.0 Builds

Shocking news - nicely displayed

2010-09-09T16:09:00.000+12:00

Animated Christchurch Quake Map

Wearable Desire

2010-08-27T14:06:00.000+12:00

I am here

Remember: Nothing says 'thank you for your blog' like buying me a present. ;)

You don't have to be insane to be a progammer...

2010-08-13T10:50:00.000+12:00

Excellent cautionary tale with very useful pointers.
If you code professionally I reccomend this post.

Common Programmer Health Problems

I find many programmers seem to ignore their body's physical state when they're coding, most likely due to the intense concentration required. I'm hoping other people could benefit by simply understanding a few health related problems programming has almost caused me or caused many other people I know, and how I avoided them.

What I do want to cover are a set of particular problems programmers have from their daily profession. These are just simple really obvious things that for some reason programmers don't realize aren't supposed to be happening:

Pain in your wrists from Repetitive Strain Injury (RSI).

Problems with your eyes from staring at moving print for extended periods.

Back problems from poor posture, especially in the lower back and upper shoulders.

Bowel and urinary issues from not crapping and pissing when you should.

Dehydration from drinking too much caffeine and not enough water.

Problems with hemorrhoids and the prostate for guys from sitting too much. Yep, I'm gonna go there.

Vitamin D deficiency from lack of sunshine.

Sleeping disorders from staying up late and drinking too much coffee.

General stiffness and soreness from a lack of stretching in general.

And you think YOUR sysadmin is a control freak.

2010-08-09T12:45:00.000+12:00

Ex-San Francisco network admin gets four-year sentence

Childs defended his actions during a long court trial, saying that he was only doing his job, and that his supervisor, Department of Technology and Information Services chief operations officer Richard Robinson, was unqualified to have access to the passwords. Childs eventually handed over the passwords to San Francisco Mayor Gavin Newsom.

Because the mayor is more technically qualified???

Prosecutors characterised the former network administrator as a power hungry control freak who couldn't be managed.

You think?

Childs may also have to cover the city's US$900,000 bill, spent on trying to regain control of its network.

One wonders at the policy of allowing one employee to power to 'own' an entire network. Does San Francisco lack a bus service? (as in what if they were run over by a bus)
Either way, this super-ego is better off not controlling something do important.

SQL injection fun

2010-08-05T15:26:00.000+12:00

Barcode Attacks

Why not try it at you favorite supermarket today.

Passing lists to a SPROC - excellent article

2010-07-27T10:19:00.000+12:00

Arrays and Lists in SQL Server 2005 and Beyond

In the public forums for SQL Server, you often see people asking How do I use arrays in SQL Server? Or Why does SELECT * FROM tbl WHERE col IN (@list) not work? The short answer to the first question is that SQL Server does not have arrays – SQL Server has tables. However, upto SQL 2005 you could not specify a table as input to SQL Server from a client, but you had to pass a string with the values and unpack it into a table on the SQL Server end.

This article describes a number of different ways to do this, both good and bad. I first give a background to the problem (including a quick solution that is good enough in many cases). I then give a brief overview over the methods, whereupon I discuss general issues that apply, no matter which method you use. Having dealt with these introductory topics, I devote the rest of the article to detailed descriptions of all methods, and I discuss their strengths and weaknesses.

SQL Date Elegance - Dan Guzman does it again

2010-07-21T09:19:00.000+12:00

Calendar Table and Date/Time Functions

I frequently see questions in the forums and newsgroups about how to best query date/time data and perform date manipulation. Let me first say that a permanent calendar table that materializes commonly used DATEPART values along with time periods you frequently use is invaluable. I’ve used such a table for over a decade with great success and strongly recommend you implement one on all of your database servers. I’ve included a sample calendar table (and numbers table) later in this post and you can find other variations of such a table via an internet search.

Removing the Time Portion

First and Last Day of Period

Calendar and Numbers Table

Google search - No region

2010-05-31T10:03:00.000+12:00

My machine (in NZ) is connected via proxy in Australia or on occaison in the UK.
By default going to google.com will redirect (based on my current proxy) to the regional sites google.com.au or google.com.uk. by using the following URL I am redirected to google.com without redirection.

www.google.com/ncr

NCR = no country redirect

I discovered this here

SQL Column Types

2010-04-07T16:54:00.000+12:00

SELECT T.name AS TableName
, C.colorder AS ColOrder
, C.name AS ColName
, CASE WHEN C.xtype = 34 THEN 'IMAGE'
  WHEN C.xtype = 35 THEN 'TEXT'
  WHEN C.xtype = 36 THEN 'UNIQUEIDENTIFIER'
  WHEN C.xtype = 48 THEN 'TINYINT'
  WHEN C.xtype = 52 THEN 'SMALLINT'
  WHEN C.xtype = 56 THEN 'INT'
  WHEN C.xtype = 58 THEN 'SMALLDATETIME'
  WHEN C.xtype = 59 THEN 'REAL'
  WHEN C.xtype = 60 THEN 'MONEY'
  WHEN C.xtype = 61 THEN 'DATETIME'
  WHEN C.xtype = 62 THEN 'FLOAT'
  WHEN C.xtype = 98 THEN 'SQL_VARIANT'
  WHEN C.xtype = 99 THEN 'NTEXT'
  WHEN C.xtype = 104 THEN 'BIT'
  WHEN C.xtype = 106 THEN 'DECIMAL(' + CONVERT(VARCHAR,C.xprec) + ',' + CONVERT(VARCHAR,C.xscale)+ ')'
  WHEN C.xtype = 108 THEN 'NUMERIC(' + CONVERT(VARCHAR,C.xprec) + ',' + CONVERT(VARCHAR,C.xscale)+ ')'
  WHEN C.xtype = 122 THEN 'SMALLMONEY'
  WHEN C.xtype = 127 THEN 'BIGINT'
  WHEN C.xtype = 165 THEN 'VARBINARY'
  WHEN C.xtype = 167 THEN 'VARCHAR(' + CONVERT(VARCHAR,C.length) + ')'
  WHEN C.xtype = 173 THEN 'BINARY(' + CONVERT(VARCHAR,C.length) + ')'
  WHEN C.xtype = 175 THEN 'CHAR(' + CONVERT(VARCHAR,C.length) + ')'
  WHEN C.xtype = 189 THEN 'TIMESTAMP'
  WHEN C.xtype = 231 THEN 'NVARCHAR(' + CONVERT(VARCHAR,C.prec) + ')'
  WHEN C.xtype = 239 THEN 'NCHAR(' + CONVERT(VARCHAR,C.prec) + ')'
  ELSE '???' END AS ColType
, C.isnullable AS ColNullable

FROM sysobjects T
INNER JOIN syscolumns C On C.ID = T.ID

WHERE T.xtype = 'U'
ORDER BY T.Name, C.ColOrder

When the IE8 Developer Toolbar get confused while docked...

2010-02-24T13:47:00.000+13:00

Discovering Internet Explorer Developer Tools

Getting Started

Getting started with the tools is simple: press F12 or click Developer Tools from the Tools menu.

Once open, the tools exist in their own window, each one connected to a single tab in Internet Explorer. If you prefer to decrease the number of open windows, pin the tools to a tab by clicking the Pin button or pressing CTRL+P.

Some features of the tools do not need the complete tools interface. In that case, click the Minimize button or press CTRL+M when the tools are pinned. The tools become a row at the bottom of the window, providing access to just theCommand Menu bar.

Things in SQL Oracle Developer I LIKE (and wish I had in MS SSMS)

2010-02-11T11:31:00.001+13:00

1) Ability to filter objects (SPROC/functions/tables) with a LIKE (not a contains) and have multiple filters on at the same time

LIKE 'usp_hv%'
OR 'xsp_hv%'
OR '%maint'

2) Ability to search within CODE using SQL without opening a separate tool

SELECT type, name, line, text
FROM user_source
WHERE UPPER(text) LIKE UPPER('%Text to search for%');

Outer join shortcut? DO NOT USE

2010-01-25T16:50:00.001+13:00

Every now and again I find this awful SQL notation. (this is a note so I don't have to google it again)
Terse code is fine as long as you know what all the squiggly bits mean!

outer join shortcut? - dBforums:

*= is a LEFT JOIN
=* is a RIGHT JOIN.
It is a T_SQL extension and was valid up thru SQL 2000 It is no longer available in SQL 2005.
It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!

Nice overview for creating Realistic looking objects

2010-01-18T09:06:00.000+13:00

Crafting Subtle & Realistic User Interfaces