SQL Server Reference Sheet

  • (2 Pages)
  • +
  • 1
  • 2

23 Replies - 5419 Views - Last Post: 03 November 2007 - 07:28 AM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

SQL Server Reference Sheet

Posted 18 October 2007 - 09:27 PM

OK y'all, I'm writing a reference sheet for MSSQL, I have posted the text version below and I was wondering if you could look through it and make any suggestions on items to add, or items that need to be edited.. If so just make your comments here and Ill add your ideas.

Thanks,
PsychoCoder :)

Attached File  sql_reference_sheet.txt (4.78K)
Number of downloads: 508

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Server Reference Sheet

#2 nav33n   User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 71
  • Joined: 02-October 07

Re: SQL Server Reference Sheet

Posted 18 October 2007 - 11:52 PM

Good job! I think it would be much much better if you add an example,so that any starter can look at this reference sheet and start coding !
Was This Post Helpful? 0
  • +
  • -

#3 Louisda16th   User is offline

  • dream.in.assembly.code
  • member icon

Reputation: 15
  • View blog
  • Posts: 1,967
  • Joined: 03-August 06

Re: SQL Server Reference Sheet

Posted 19 October 2007 - 12:58 AM

Relief! Looks the same as Oracle SQL to me till now :).
Im not sure, but if this belongs to MSSQL also, could you add joins, subqueries, Create Table statement, Create View statment, Synonyms, sequences and indexes.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: SQL Server Reference Sheet

Posted 19 October 2007 - 02:15 AM

Looks good.

You could add some of the @@vars.

Also, second the example request. I usually just want to see something that works, not the spec for something that could work. Particularly the joy of joins. A nice SQL Server style join update could probably brighten someone's day. e.g.

update person
   set status='Fired'
   from person a
	  inner join department b
		  on a.deptno=b.deptno
   where b.name='IT'

Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL Server Reference Sheet

Posted 19 October 2007 - 05:11 AM

If you look at the other reference sheets we've created they are all in the same format, with no specific examples, as these are simply reference (syntax) sheets. I will however add some of the more common @@vars, joins and other stuff suggested :)
Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL Server Reference Sheet

Posted 19 October 2007 - 06:32 AM

Here is an updated version as of this morning, I didn't have time to add JOINS this morning but they will be in the sheet

Attached File  sql_reference_sheet.txt (6.86K)
Number of downloads: 244
Was This Post Helpful? 0
  • +
  • -

#7 Louisda16th   User is offline

  • dream.in.assembly.code
  • member icon

Reputation: 15
  • View blog
  • Posts: 1,967
  • Joined: 03-August 06

Re: SQL Server Reference Sheet

Posted 19 October 2007 - 07:37 AM

Accessing the data dictionary (Im not sure if its there in MSSQL :(). I think that is important. :)
Was This Post Helpful? 0
  • +
  • -

#8 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL Server Reference Sheet

Posted 19 October 2007 - 08:51 AM

If it is then Ive never heard of it
Was This Post Helpful? 0
  • +
  • -

#9 Louisda16th   User is offline

  • dream.in.assembly.code
  • member icon

Reputation: 15
  • View blog
  • Posts: 1,967
  • Joined: 03-August 06

Re: SQL Server Reference Sheet

Posted 19 October 2007 - 10:19 AM

Its only in Oracle then. Sorry :(.
Was This Post Helpful? 0
  • +
  • -

#10 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: SQL Server Reference Sheet

Posted 23 October 2007 - 12:22 PM

View PostLouisda16th, on 19 Oct, 2007 - 01:19 PM, said:

Its only in Oracle then. Sorry :(.


Nope, SQL Server has it. Maybe not as many views as Oracle, nor as well documented, but it's all there.

Each database has system tables that can be queried as well as views and also procedures.

There are tables dbname.dbo.sysobjects, dbname.dbo.sysusers, etc.

There's also a user / schema called information_schema. This user is in master, and the view results change based on the current database context. Try select * from information_schema.tables.

Also, sp_help. Kind of a kitchen sink of metadata. Try exec sp_help tablename.
Was This Post Helpful? 0
  • +
  • -

#11 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL Server Reference Sheet

Posted 23 October 2007 - 01:00 PM

Ooooooh system tables....I have never heard of it called data dictionary before though. I use sp_help and sp_who/sp_who2 all the time at work (to see what idiit is blocking the database when I'm trying to work lol).

Sorry Louisda16th, I had never heard of it referred to as data dictionary before.
Was This Post Helpful? 0
  • +
  • -

#12 Louisda16th   User is offline

  • dream.in.assembly.code
  • member icon

Reputation: 15
  • View blog
  • Posts: 1,967
  • Joined: 03-August 06

Re: SQL Server Reference Sheet

Posted 24 October 2007 - 06:54 AM

LOL! I really have to do a LOT of work to get back to databases :P!!
Was This Post Helpful? 0
  • +
  • -

#13 Trogdor   User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: SQL Server Reference Sheet

Posted 25 October 2007 - 03:21 AM

some things that i often have to look up how to do that are not on the sheet are:
scope_indentity (quite important to use it instead of the normal @@indentity !)
insert from a select statement
selectting non-matching rows (select * from A left outer join B on A.x = B.x where B.id is null)
transactions and the locking-levels
Was This Post Helpful? 0
  • +
  • -

#14 Trogdor   User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: SQL Server Reference Sheet

Posted 25 October 2007 - 04:51 AM

and the Case Statement is missing the END.

Great initiative btw!
Was This Post Helpful? 0
  • +
  • -

#15 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL Server Reference Sheet

Posted 25 October 2007 - 06:25 AM

Thanks for the ideas Trogdor, after I add information on JOIN's if I have room (can only have a certain number of lines) I will try to squeeze your suggestions in. SCOPE_IDENTITY and @@IDENTITY will be the first
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2