Tuesday, May 01, 2018

Explaining common join types


-- 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

Wednesday, September 21, 2016

Documenting Foreign Keys in SQL 2012

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


Tuesday, September 20, 2016

SQL documentation, updated

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

Tuesday, September 13, 2011

Remote Desktop (RDC) shortcut keys

Original Article
Shortcut keyDescription
ALT+PAGE UPSwitches between programs from left to right.
ALT+PAGE DOWNSwitches between programs from right to left.
ALT+INSERTCycles through the programs in the order they were started.
ALT+HOMEDisplays the Start menu.
CTRL+ALT+BREAKSwitches the client between a window and full screen.
CTRL+ALT+ENDBrings up the Windows Security dialog box.
ALT+DELETEDisplays the Windows menu.
CTRL+ALT+Minus (-) symbol on the numeric keypadPlaces 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

Instruction here

How to Restart Windows via Remote Desktop

CTRL+ALT+END

Friday, August 27, 2010

Wearable Desire

I am here

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

Friday, August 13, 2010

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

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.

Monday, August 09, 2010

And you think YOUR sysadmin is a control freak.

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.

Tuesday, July 27, 2010

Passing lists to a SPROC - excellent article

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.

Wednesday, July 21, 2010

SQL Date Elegance - Dan Guzman does it again

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

Monday, May 31, 2010

Google search - No region

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

Wednesday, April 07, 2010

SQL Column Types


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

Wednesday, February 24, 2010

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



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.

Thursday, February 11, 2010

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

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%');

Monday, January 25, 2010

Outer join shortcut? DO NOT USE

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!

Wednesday, December 23, 2009

This could be good or catastrophic

(Emphasis Mine)
Microsoft patents anatomically correct avatars: "Accurate avatars will facilitate online honesty"

"The new systems include provisions for sensing physical data (via pedometers, cameras, or health information repositories like Microsoft HealthVault) psychological characteristics (including levels of alertness or mood changes) and even demographic information like "education level, geographic location, age, sex, intelligence quotient, socioeconomic class, occupation, marital/relationship status, religious belief, political affiliation, etc."
"Physical data that reflect a degree of health of the real person can be linked to rewards of capabilities of a gaming avatar, an amount of time budgeted to play, or a visible indication," reads the summary. "Thereby, people are encouraged to exercise."

I think I am scared by the implications of this - only time will tell.
I can see evil lurking. Can You?

Monday, December 14, 2009

Finally we are catching up with plumbers and builders

Cash offer sees Citrix staff bring their own computers to work
The company initiated an employee-owned IT programme earlier this year. Citrix offers $US2,000 to employees to buy their own work/home PC under the scheme, dubbed ‘Bring Your Own Computer (BYOC)’. The stipulation: The computer must have a minimum three-year support and maintenance contract.

BTW yes I do think I can choose my own PC better than a head office IT person. As long as this is an annual 'tools allowance' I would be the first to sign up.

How the other half thinks (tune in next year)

Think Like a Developer & Designer Series
The “Think Like A...” series is a collection of topics presenting a developer's point of view and a designer's point of view on what goes into making a WEB 2.0 website. The purpose of this series is to help developers understand and appreciate a designer's viewpoint on creating a website, and on the flip side, to help designers understand and appreciate a developer’s viewpoint on creating a website.