RTFM365, Week 5

I’m trying to plan out this week better, and come at the RTFM readings with a sense of direction an purpose.  I’ve decided to dig deeper into the T-SQL sections, top-down. I think this is going to be more profitable, knowledge-wise, because I’ll be reading about related subjects.

Short list of what I read:

• Writing, Analyzing, and Editing Scripts with SQL Server Management Studio [R2]
• Accessing and Changing Database Data [R2]
• Elements of Transact-SQL [R2]
• Identifiers[2008]
  • Using Identifiers As Object Names [2008]
  • Object Visibility and Qualification Rules [2008] 
  • Delimited Identifiers [R2]

Boilerplate: 

For the next year (-about a month now), I will read from SQL Server Books Online a little every day, and blog about it weekly. See my RTFM365 post for the full details on this project, and follow along with the series via the RTFM365 tag. 

Reading this week was in SQL Server 2008 R2 Books Online unless otherwise noted. Please note: I’m exploring BOL and learning a little each day; I’m not here to read it to you or review it.  

Here’s what I read this week:

DB Engine > Development > Building Database Projects by Using SQL Server Management Studio > Writing, Analyzing, and Editing Scripts with SQL Server Management Studio

DB Engine > Development > Querying and Changing Data > Accessing and Changing Database Data > Elements of Transact-SQL >

• Identifiers
  • Using Identifiers As Object Names [2008]
  • Object Visibility and Qualification Rules [2008] – This has a weird rule in it: “Schema_name – Default is the username in the specified database that is associated with the login ID of the current connection.”  We’re jimmying around with it to check out the behavior…this doesn’t seem to work this way in practice. (Sean explained it to me this morning, and I said – and I quote – “Huh?”)  The moral, however, is always schema-qualify your objects, especially when you create them.
    
    You should also note, webkinder: “The visibility for stored procedures that start with sp_ differs from the visibility for regular stored procedures. … see CREATE PROCEDURE (Transact-SQL).” …which only says, “We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures. For more information, see Creating Stored Procedures (Database Engine).”…which finally explains it to you. Sort of. What it boils down to is (a) sp_ makes SQL look for that SP in the Resource database first, and only if it doesn’t find it there does it look in the local database. And (b) “Using an explicit schema qualifier also provides a slight performance advantage. Name resolution is slightly faster…”
  • Delimited Identifiers [R2] – Note that variables and SP parameters must follow the rules for identifiers, because you can’t delimit them. This article also discusses the different delimiters (brackets and quotes), and the behavior of QUOTED_IDENTIFIER on/off.  (I now have the strangest compulsion to architect a database made up entirely of rude words. I shall resist.)

    (Edit: Thanks to jonmcrawford for the link to Phil Factor’s (blog, Twitter)  Evil Code blog.  I’ve never read anything more hilarious using [GetDate.GetDate.GetDate.GetDate.GetDate.GetDate.GetDate.GetDate.GetDate], or the poetry of Odgen Nash.)

Example from Delimited Identifiers, which I like to call Worst….Table….EVAR…:

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "table"
(
tablename char(128) NOT NULL,
"USER" char(128) NOT NULL,
"SELECT" char(128) NOT NULL,
"INSERT" char(128) NOT NULL,
"UPDATE" char(128) NOT NULL,
"DELETE" char(128) NOT NULL
);
GO

Next week, I’ll read as much as I can of the Data Types section (DB Engine > Development > Querying and Changing Data > Accessing and Changing Database Data > Elements of Transact-SQL >Data Types).

Happy Days,
Jen McCown
http://www.MidnightDBA.com/Jen

Join in our live DBAs at Midnightwebshow
Fridays at 11pmCST on our Ustream channel!

Watch all past shows at MidnightDBA.com