Dealing with Dates (Part 2)

---

"We're trained professionals. (Well, we're semi-trained quasi-professionals, at any rate.)" -Roy Greenhilt, Order of the Stick webcomic

---

Last time I wrote about converting to and from temporal data types in Oracle. Being how every time I do something for Oracle I have to do the same thing for SQL Server, it's time to delve into temporal data type manipulations in SQL Server.

SQL Server really only has the one function of note that does it all: CONVERT. Personally, I don't find the conversion modifier for convert to be anywhere near as intuitive as to_date or to_char with the format strings that Oracle does - without the chart from the documenation, I'd never be able to remember any of it.

My personal feelings aside, let's hit the base syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

(HTML for this table from SQL Server 2008 Books Online, see link at bottom of post)

---

Date and Time Styles

When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

Without century (yy)1	With century (yyyy)	Standard	Input/Output 3
-	0 or 100 (1,2)	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yyyy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106 (1)	-	dd mon yy
7	107 (1)	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 or 109 (1,2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 or 113 (1, 2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
-	130 (1, 2)	Hijri ((5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM

¹ These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

² The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

³ Input when you convert to datetime; output when you convert to character data.

⁴ Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.

⁵ Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.

Important

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.

⁶ Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

⁷The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.

When you convert to datetimeoffset from character data with a style that includes a time, a time zone offset is appended to the result.

---

So, example time!

select convert(char(8), getdate(), 112)

returns

20110929

Shortening the char(8) to char(4) would get us:

select convert(char(4), getdate(), 112)

returns

2011

Changing the convert code from 112 to 12, shortens this to a 2 digit year:

select convert(char(8), getdate(), 12)

returns

110929

References:
SQL Server Books Online: CAST and CONVERT

Dealing with Dates (Part 1)

---

"I suggest a new strategy, R2. Let the Wookiee win" - C-3PO, Star Wars Episode IV: A New Hope

---

Doesn't matter what the DB is that you're working on, working with Temporal data types is a royal pain in the rear end.

I can see this being an exceptionally long post, so I think I'm going to break it up into multiple parts. Let's start with Oracle today and how to transform Date data types into Text for display and the reverse, taking a text representation of a temporal value and placing it into a Date data type.

Oracle provides a long list of built in functions to deal with the conversion of data from one data type to another. You can see them in this list in the last column - they all start with "to_".

Oracle has two data types dealing with date and time. The first is simply DATE: a representation of a date and time to the second. The second is TIMESTAMP: a representation of date and time to fractions of a second. The default has a precision of 6 decimal places, but can go as high as 9. (There is a third option, TIMESTAMP WITH {LOCAL} TIMEZONE which is a timestamp with a built in time zone offset, but for the sake of what we're talking about here, this is just a TIMESTAMP).

So, this gives us three functions to play with:

• to_date
• to_timestamp (there's also to_timestamp_tz, but I'm not touching that here)
• to_char

All three functions take a formatted string and converts it to a date or timestamp or character representation. The format of the function is:

function(string1, [format_string], [nls_parameter])

The format_string is optional. If it is not specified, the string needs to be in the default date format as defined implicitly by the NLS_TERRITORY initialization parameter or explicitly set by the NLS_DATE_FORMAT parameter. Best practice dictates that you always specify the format string. The length of the format string may not exceed 22 characters. Capitalization of a Format Element carries over to the output. A Format Element on "DAY" would output "MONDAY", where as a Format Element of "Day" would output "Monday".

The nls_parameter specifies the language the date is bring formatted to. It must be of the format 'NLS_DATE_LANGUAGE = language'

The to_char function can also be used to convert numbers to character or hex representations of ASCII codes to characters, but we're only talking about dates here.

The format string parameter can be any of the following values: (HTML for this table from Oracle Documentation, see references link at end of document)

Element	Description
/ , . ; : "text"	Punctuation and quoted text is reproduced in the result.
AD A.D.	AD indicator with or without periods.
AM A.M.	Meridian indicator with or without periods.
BC B.C.	BC indicator with or without periods.
CC SCC	Century. If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year. If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year. For example, 2002 returns 21; 2000 returns 20.
D	Day of week (1-7). This element depends on the NLS territory of the session.
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DL	Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format 'fmDay, dd. Month yyyy'. Restriction: You can specify this format only with the TS element, separated by white space.
DS	Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'MM/DD/RRRR'. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format 'DD/MM/RRRR'. Restriction: You can specify this format only with the TS element, separated by white space.
DY	Abbreviated name of day.
E	Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE	Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]	Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in DATE formats. Examples: 'HH:MI:SS.FF' SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM	Returns a value with no leading or trailing blanks.
FX	Requires exact matching between the character data and the format model.
HH HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
IW	Week of year (1-52 or 1-53) based on the ISO standard.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard.
J	Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI	Minute (0-59).
MM	Month (01-12; January = 01).
MON	Abbreviated name of month.
MONTH	Name of month.
PM P.M.	Meridian indicator with or without periods.
Q	Quarter of year (1, 2, 3, 4; January - March = 1).
RM	Roman numeral month (I-XII; January = I).
RR	Lets you store 20th century dates in the 21st century using only two digits.
RRRR	Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
TS	Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters. Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD	Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats. Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH	Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats. Example: 'HH:MI:SS.FFTZH:TZM'.
TZM	Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats. Example: 'HH:MI:SS.FFTZH:TZM'.
TZR	Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats. Example: US/Pacific
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
X	Local radix character. Example: 'HH:MI:SSXFF'.
Y,YYY	Year with comma in this position.
YEAR SYEAR	Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY SYYYY	4-digit year; S prefixes BC dates with a minus sign.
YYY YY Y	Last 3, 2, or 1 digit(s) of year.

All of this is great, what about examples?

select to_char(sysdate, 'Day, DD Month, YYYY "@" HH24:MI:SS') from DUAL;

returns an error because the format string is over 22 characters. The elements are all valid, but combined they are too long.

select to_char(sysdate, "Day, DD Month, YYYY @ HH24:MI:SS") from DUAL;

returns

Tuesday, 27 September, 2011

Next: The SQL Server CONVERT function

References:
Oracle 11g SQL Language Reference: to_date function
Oracle 11g SQL Language Reference: Format Models

Passing a parameter to a SQL Script and parsing the output

---

"I have a bad feeling about this..." - Lots of people, over 6 Star Wars movies

---

Not so long ago, I had a requirement to be able to have a scheduled job that ran a SQL script, took the output and did something with it in Windows CMD shell. Not only that, I had to feed a parameter in.

I'd done this many times in my previous life, taking a value from a SQL script in Oracle, passing it to the Unix shell and running with it.

Having to do it in Windows was a bit more of a challenge.

Took quite a bit of research to find out how to do it, so it's worthy of preserving here.

First, we need the SQL we're going to execute:

set NOCOUNT ON
select column1
from table1
where column2 = '$(var1)'

Next, we need to call it and pass the variable in: (let's assume I've setup a user called 'ro' with a password of 'read' that only has select access on the table I'm selecting from. The script (above) is sitting in 'c:\sql' and I'm writing the output to 'c:\sql\out'. Finally, the process that calls this script is passing the parameter. (In the real world this is all on one line. A blog is only so wide ...). Also, in my example this script is called by another process passing in a job number as the parameter - so the %1 is the job number parameter that is being passed in.

sqlcmd -S SRV01 -d myprd1 -U ro -P read -h-1 -i c:\sql\query1.sql 
    -o c:\sql\out\query1.txt -v var1="%1"

End result is that I now have the value of column1 from table1 sitting in a text file.

Next up, we need to be able to interrogate that value in order to be able to do something else with it.

for /f "usebackq delims=" %%i in (`cat c:\sql\out\query1.txt`) do set H001=%%i

At the end of that, we now have the value from the text file sitting in variable H001, that we can use for whatever.

Of course a clean system is a good system, and you should always clean up after yourself:

if exist c:\sql\out\query1.txt (erase c:\sql\out\query1.txt)

So far, so good. But, what is done on SQL Server in Windows should also be done in Unix and Oracle, right ? Second verse, same as the first (except for the whole change of OS / RDBMS thing).

Going to change this one up just a little. Let's say we need two values, the first being 8 characters and the second being 4.

set verify off
set echo off
set feedback off
set heading off
spool /tmp/query1.out
select substr(column1,1,8)||substr(column3,1,4)
from table1
where column2 = '&1';
spool off
exit

The same requirements for calling the script. And again, this is called by another processing passing in a parameter in the first position.

export ORACLE_SID=myprd1
$ORACLE_HOME/bin/sqlplus ro/read @/scripts/query1.sql $1

Alternatively, and actually my preferred way of doing it is to put the username and password as the first line of the script and to remove the user/pass from the invocation line. This way, in the event of someone trying to execute 'ps -ef | grep sqlplus' at just the magic moment, won't see your database credentials in the process list.

Once we have our output, we need to get it into the environment variable.

H001=`cat /tmp/query1.txt | grep -v ^$ | cut -c1-8`
H002=`cat /tmp/query1.txt | grep -v ^$ | cut -c9-12`

There's other ways to get the same information too, I'll list just one more here.

H000=`$ORACLE_HOME/bin/sqlplus -s @/scripts/query1.sql`
H001=`echo $H000 | cut -c1-8`
H002=`echo $H000 | cut -c9-12`

That would assign the whole 12 character string to H000 and you could extract the other two variables from it.

Setting up a new SQL Server

---

"Don't be too proud of this technological terror you've constructed" - Darth Vader, Star Wars Episode IV: A New Hope

---

A couple of years ago, I was leaving my former employer. I could see the writing on the wall that the Oracle work I was doing was going to be diminishing and SQL Server was the way of the future. I didn't want to work with SQL Server, so I went and found another job with my current employer.

One of the first things they told me was that I was going to be looking after a SQL Server for a new customer. Argh! Note to self: next time ask more questions about responsibilities and duties, I suppose :)

Almost 4 years down the road, I've learned quite a bit about this database - but I still have a lot to learn.

One of the things I still need to learn a lot about is best configuration options for SQL Server and the Windows box that it sits on. One great guide that's collected a lot of the information I'd picked up in bits and pieces around the place is collected neatly in two posts on the site of Brent Ozar PLF.

These fine people have put together two blog posts discussing setup of a new SQL Server. Rather than steal their work, I'll just link directly to their pages. (This post falls squarely in the area of "Posts I make mainly as a personal reference for my own benefit").

• Part 1 - Before the Install
  
• Part 2 - After the Install
  

Since I'm singing the praises of the information on this site, there's one more thing I just have to list. In addition to having to set up new servers, sometimes you just inherit them. Brent has a one hour blitz script to have a look at the setup of a database server you've just inherited.

It's sitting right here.

I learned a lot from watching that presentation and crunching through the script. Definitely worth the price of admission ...

Scripting users and their permissions (part 2)

---

"A man walks down the street in that hat, people know he's not afraid of anything." - Wash, to Jayne - Firefly, "The Message"

---

In my last post I talked about scripting out users and their permissions in Oracle.

Since his is a blog about things both Oracle and SQL Server, it only stands to reason that the following post should be about replicating this for SQL Server.

For those more familiar with Oracle than SQL Server, there's two layers of objects between you and your data. Since a SQL Server instance has multiple databases in the one instance (which is completely different to the way a lot of Oracle databases are set up), you first need a login name to log in to the instance. This can be a local SQL Server login, or a Windows Account.

Once authenticated to the SQL Server instance, there are then users in each database that are linked back to logins. So if you have Prod, Test and Dev databases on the one server, you could login as "myuser" and possibly have full database ownership on Dev, Read and Update to Test and Read Only to Prod. The one login gets you into the instance, but the users connected to the login in each database have varying permissions.

So, with that out of the way, the first thing we need to get is a list of logins. Login information is stored in the master database of the instance.

The easiest way to get this information is to go straight to the source. Microsoft provides a script to extract this information from you database using this Knowledge Base article. The script is too long to reproduce here, but run it to create two stored procedures in master and then "exec sp_help_revlogin" to generate a list of logins on your instance.

Once we have the list of logins, the next thing we're going to need is a list of users in the database. You run this out of the database you're extracting the information from

use ellprd
go
select 'create user ' + QUOTENAME(a.name) +
CASE WHEN SUSER_SNAME(a.sid) is NULL 
     then '' 
     ELSE ' for login ' + QUOTENAME(SUSER_SNAME(a.sid)) 
END +
CASE WHEN default_schema_name is NULL 
     then '' 
     ELSE ' with default_schema = ' + QUOTENAME(a.default_schema_name) 
END
from sys.database_principals a, sys.sysusers b
where (type_desc like '%USER' or type_desc = 'WINDOWS_GROUP')
and a.name = b.name
and b.altuid is null
and b.sid is not null

Finally, we need to get a list of all the object permissions in the database.

For this, the magic of Google comes to my aid yet again. Found this post which has a lovely script that generates user permissions. There are two variables at the top - @OldUser and @NewUser - these are set when you want to replicate permissions from one user to another. To just list what you have, make them both the same value.

That covers basic SQL Server users. But there's one thing missing. What if you have someone connecting via their Windows identification token ?

The following script was developed (to the best of my knowledge) by a site DBA at one of my customer's sites. The variable at the top, @DatabaseFilter needs to be set to the name of the database you're running this against.

/* Security Audit Script - SQL Server 2005 & 2008 Compatible */
USE master
GO

SET NOCOUNT ON;

/***************SPECIFY DATABASE HERE*****************/
DECLARE @DatabaseFilter varchar(255)
SET @DatabaseFilter = 'myprod1'

/* Report Date & Time */
SELECT CONVERT(varchar, GETDATE(), 107)+' @ '+
       LEFT(CONVERT(varchar, GETDATE(), 108),5) AS [Report Run Date-Time]


/* Windows Logins - ALL */
SELECT a.name, a.sid, a.type_desc 
INTO #server_logins
FROM sys.server_principals a
LEFT JOIN sys.sql_logins b ON b.sid = a.sid
WHERE a.type IN ('U','G')

/* Find Windows Logins w/ Server-Wide Roles */
SELECT d.name AS [Server Role], a.name AS [Server Login], 
       a.type_desc AS [Logon Type]
FROM #server_logins a
LEFT JOIN sys.server_principals b 
       ON b.sid = a.sid
LEFT JOIN sys.server_role_members c 
       ON c.member_principal_id = b.principal_id
LEFT JOIN sys.server_principals d 
       ON d.principal_id = c.role_principal_id
WHERE d.name IS NOT NULL
ORDER BY a.name

/* Database List */
CREATE TABLE #databases 
             (row_id int identity(1,1), [name] varchar(255), id int)
INSERT INTO #databases ([name], id)
SELECT DISTINCT a.name, a.database_id
FROM sys.databases a
WHERE a.database_id > 4 --Skip System Databases
AND DATABASEPROPERTYEX(a.[name],'Status') = 'ONLINE' --Online Only
AND a.name = @DatabaseFilter --Single Database Filter

/* Looking for Database Users */
CREATE TABLE #database_logins 
             ([Database] varchar(255), 
              [Database User] varchar(255), 
              [Database Role] varchar(255), 
              [Server Login] varchar(255))

DECLARE @counter int, @max_rows int, @database varchar(255)
SELECT @counter = 1, @max_rows = COUNT(*) FROM #databases

WHILE @counter <= @max_rows
BEGIN

 SELECT @database = [name] FROM #databases WHERE row_id = @counter

 EXEC('INSERT INTO #database_logins
  SELECT '''+@database+''' [Database], 
         b.name AS [Database User], 
         c.name AS [Permission], 
         IsNULL(d.name,''ORPHANED'') AS [Server Login]
  FROM ['+@database+'].sys.database_role_members a
  LEFT JOIN ['+@database+']..sysusers b 
         ON b.uid = a.member_principal_id
  LEFT JOIN ['+@database+'].sys.database_principals c 
         ON c.principal_id = a.role_principal_id
  LEFT JOIN #server_logins d ON d.sid = b.sid
  WHERE b.name <> ''dbo'' AND b.issqlrole = 0')

 SET @counter = @counter + 1
END

SET @counter = 1

WHILE @counter <= @max_rows
BEGIN

 SELECT @database = [name] FROM #databases WHERE row_id = @counter
 EXEC('INSERT INTO #database_logins
  SELECT '''+@database+''' [Database], a.name AS [Database User], 
         ''Object-Based'' AS [Database Role], 
         ISNULL(b.name,''ORPHANED'') AS [Server Login]
  FROM ['+@database+']..sysusers a
  LEFT JOIN #server_logins b 
         ON b.sid = a.sid 
         AND b.name COLLATE DATABASE_DEFAULT = 
             a.name COLLATE DATABASE_DEFAULT 
  WHERE a.name NOT IN (''public'',''dbo'',''guest'',
  ''INFORMATION_SCHEMA'',''sys'',''db_owner'',''db_accessadmin'',
  ''db_securityadmin'',''db_ddladmin'',''db_backupoperator'',
  ''db_datareader'',''db_datawriter'',''db_denydatareader'',
  ''db_denydatawriter'')
  AND a.issqlrole = 0 AND a.name NOT IN (
   SELECT b.name
   FROM ['+@database+'].sys.database_role_members a
   LEFT JOIN ['+@database+']..sysusers b 
          ON b.uid = a.member_principal_id
   LEFT JOIN ['+@database+'].sys.database_principals c 
          ON c.principal_id = a.role_principal_id
   LEFT JOIN #server_logins d  
          ON d.sid = b.sid
   WHERE b.name <> ''dbo'')')

 SET @counter = @counter + 1
END

SELECT a.[Database], a.[Database User], a.[Database Role]
FROM #database_logins a
ORDER BY a.[Database], a.[Database User], a.[Database Role]

/* Taking out the trash */
DROP TABLE #server_logins
DROP TABLE #databases
DROP TABLE #database_logins

Try it out, see what you think.

Scripting users and their permissions (Part 1)

---

"Ten percent of nothing is... lemme do the math, here ...nothing, and then nothing.. carry the nothing." - Jayne Cobb, Firefly Pilot

---

From time to time, I am tasked with presenting a list of all users and their associated permissions. This is usually from some internal or external auditor who wants to make sure what access levels assorted users have in the database

So, while nothing special, the following bits of code would get me the information I needed. I guess we'll start with Oracle.

Authentication into Oracle (for the most part) is done via a username and a password. There are a few exceptions but not going to cover that here.

In the event that you want to be able to recreate the users in your database the following SQL script will help:

set linesize 132
set pagesize 100
set heading off
select 'create user '||username||' identified by values '''||password||
       ''' default tablespace '||default_tablespace||
       ' temporary tablespace '||temporary_tablespace||';'
from dba_users
order by username;

Placing the keyword "values" after "identified by" is telling Oracle to create a user with the same encrypted password as the source user. This is an effective way to recreate users on a different system without actually knowing the current password of the user. If you want to assign the new user's password, change the script to something like this:

select 'create user '||username||' identified by newpass '||
       'default tablespace '||default_tablespace||
       ' temporary tablespace '||temporary_tablespace||';'
from dba_users
order by username;

Now that we have the users created, they'll need permissions.

There are three types of Privileges in Oracle: Role Privileges, Table Privileges and System Privileges.

A role in Oracle is a group of privileges that can be assigned to users or to other roles. They greatly ease security administration in my opinion because every time a new user is set up, you don't need to grant him a whole swag of individual permissions, you could just grant the WAREHOUSE_USER role or the POWER_USER role and a predefined set of privileges is assigned to that user.

Recreating the roles in your database can be done via a select from DBA_ROLES along the line of:

select 'create role '||role||';'
from dba_roles
order by role;

This over simplifies is a little since there are sometimes passwords associated with the role, but we'll keep it simple for now.

If you were to run this script there would be a lot of errors indicating that the role already exists since the roles that come pre-installed with Oracle are also included in the output from this statement. It would be up to the DBA running this script to really determine what was missing and cull down the output accordingly.

So now that we have users and we have roles, the first thing we should look at is what roles a user has access to. This is defined in DBA_ROLE_PRIVS.

We generate the statements once again by selecting from this table.

select 'grant '||granted_role||' to '||grantee||
       decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
order by grantee, granted_role;

Again, there's a thing or two missing here, but this should cover the lion's share/

Users (and roles) can also be granted levels of access to user or system objects (tables, stored procedures and packages etc). The following query gets us that information, stored in DBA_TAB_PRIVS:

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||
       grantee||
       decode(GRANTABLE, 'YES', ' WITH GRANT OPTION', ' ') ||
       decode(HIERARCHY, 'YES', ' WITH HIERARCHY OPTION', ' ') || ';'
from dba_tab_privs
order by grantee, owner, table_name;

The output from this can be long. Be warned. Again, the explanation of these options will not be covered here, the details of that can be found in the Oracle documentation at http://tahiti.oracle.com.

Finally, the System Privileges (creating objects, dropping objects etc) need to be mapped out. These are found in DBA_SYS_PRIVS.

select 'grant '||privilege||' to '||grantee||
       decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
order by grantee, privilege

With the above steps, you've effectively mapped out your database security model. If you don't want the create statements in there, it's easy enough to select from the specific views to get the information you need.

The views starting with "DBA_" are for DBA's to use. A user can look at equivalent views being prefaced by "USER_" instead of "DBA_". These are all the objects owned by the current user. Further, there's a set of views starting "ALL_". These represent all of the objects that the current user has access to.

There's a lot of useful information in the system catalog views in Oracle - every object ever created should have entries in one of the DBA_ views.

As a final note, there's a web page here that has the above data (without the create statements) in a hierarchical view. Interesting way of looking at it.

Quick and Easy Command and Syntax Reference

---

"Tell me, Mr. Anderson... what good is a phone call... if you're unable to speak?" - Agent Smith, The Matrix

---

Ever had a time when you needed the exact syntax of a command and couldn't remember it ?

Or there's a command that has multiple options and you can't remember the exact one you need ?

Thought I'd list a couple of my favorite such places here.

1. SS64.com
   
2. Tech On The Net
   

SS64 has nifty things like:

• SQL*Plus Set Commands
  
• SQL Server DBCC commands (among others)
  
• Powershell Reference
  
• Windows Command Shell Reference
  

What's not to love ?

Tech On The Net has similar goodies for Oracle and SQL Server and assorted other tech goodies

• Need exact syntax for a CURSOR FOR loop in Oracle?
  
• Can't remember the exact format for a to_date function?
  
• Quick clarification on what INNER and OUTER joins do?
  

Two great quick reference sites.

DMV sys.dm_db_index_physical_stats is broken ?

---

"Damage Report!" - Assorted Star Fleet Captains in just about every form of Star Trek

---

There's this little database I have that sits in a corner and is mostly forgotten. It runs and does its job and rarely causes me any problems - and consequently tends to get overlooked sometimes.

This month, during my monthly maintenance window I thought "Hmmm, maybe I should do a little index maintenance on this sucker".

So I pull out my Index Defragmentation Scripts and throw it into SSMS and hit Execute and it says:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.

What the heck is that ?

It's complaining about the DB_ID() parameter in the call to the DM function.

So I went and talked to my best buddy Google and found this gem.

What I didn't realize is that the version of the software for the application this database supports is pretty old and out of date. It requires that SQL Server's compatibility mode on this instance be 80. This function doesn't like DB_ID() as a parameter in that compatibility mode.

I'm sure that the solutions posted on that thread work, but I wasn't going to toy with it. I executed a quick SELECT DB_ID(), found the database_id, put that integer in place as the parameter and away we went!

How to identify my constraints

---

"Hello. My name is Inigo Montoya. You killed my father. Prepare to die." - Inigo Montoya, The Princess Bride

---

I recently had to work out what constraints I had on one of my databases so I thought I may as well post something about identifying what constraints are in a database, in particular, what types of constraints you have.

Oracle provides the view sys.dba_constraints where all constraints are documented in the system catalog.

The constraint types (identified by the CONSTRAINT_TYPE column in the catalog view are:

• C - Check constraint on a table
  
• P - Primary Key
  
• U - Unique Key
  
• R - Referential Integrity constraint
  
• V - With check option, on a view
  
• O - With Read Only, on a view
  

Easy!

SQL Server 2000 had a view sys.sysconstraints that is still in the system as of SQL Server 2008, but has been deprecated and Microsoft could remove it at any time.

There is a column "status" that marked the kind of constraint by an integer value.

• 1 - PRIMARY KEY constraint
  
• 2 - UNIQUE KEY constraint
  
• 3 - FOREIGN KEY constraint
  
• 4 - CHECK constraint
  
• 5 - DEFAULT constraint
  
• 16 - column level constraint
  
• 32 - table level constraint
  

The column colid contains the id of the column that the constraint applies to. The value is 0 if it's a table constraint.

In SQL Server 2005 and onwards, this table changes to 4 catalog views. Each constraint exists in sys.objects. As already mentioned in a previous post, every object in the database has an entry in sys.objects and the types are well defined there. (This is documented in Books Online).

The 4 views in question are now:

• sys.check_constraints - this view contains one row for each object that is a check constraint with sys.objects.type = 'C'.
  
• sys.default_constraints - this view contains one row for each object that is a default constraint (from create or alter table as opposed to create default) with sys.objects.type = 'D'.
  
• sys.key_constraints - this view contains one row for each object that is a primary key constraint or unique constraint with sys.objects.type = 'PK' or 'UQ'.
  
• sys.foreign_keys - this view contains one row for each object that is a foreign key constraint with sys.objects.type = 'F'.
  

References: http://msdn.microsoft.com/en-us/library/ms190365.aspx http://msdn.microsoft.com/en-us/library/ms187997.aspx http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022

So what do my tables look like anyway (Part 2)

---

I'm a doctor, not an engineer." - Doctor Leonard McCoy, Star Trek, The Original Series

---

I've used the script from the last entry on and off over the years and have not yet had the need to do this in SQL Server.

I started trying to write my own code to perform the same task and thought to do a Google search and found the following:

http://devio.wordpress.com/2008/01/08/documenting-sql-server-tables-and-columns/

So, I'm replicating that code here, just so should I ever need it, I'll have it at my fingertips.

The one thing that this does that my Oracle variant does not, is list constraints as well.

SELECT 
  obj.NAME AS TableName
 ,col.NAME AS ColumnName
 ,sys.systypes.NAME + 
     CASE 
       WHEN systypes.NAME LIKE 'n%char' 
         THEN ' (' + CONVERT(NVARCHAR, col.length / 2) + ')' 
       WHEN systypes.NAME LIKE '%char%' 
         THEN ' (' + CONVERT(NVARCHAR, col.length) + ')' 
       ELSE '' 
     END AS DataType
 ,CASE col.isnullable 
    WHEN 0 
      THEN 'NOT NULL' 
    ELSE '' 
      END AS Nullable
 ,CASE 
    WHEN COLUMNPROPERTY(obj.id, col.NAME, 'IsIdentity') = 1 
      THEN 'IDENTITY ' 
    WHEN conobj.type = 'D' 
      THEN 'DEFAULT ' + syscomments.TEXT 
    WHEN conobj.type = 'F' 
      THEN 'REFERENCES ' + fkobj.NAME + '.' + fkcol.NAME 
    ELSE '' 
      END AS Constraints
FROM       sys.sysobjects AS obj
INNER JOIN sys.syscolumns AS col 
        ON obj.id = col.id
INNER JOIN sys.systypes 
        ON sys.systypes.xtype = col.xtype
LEFT JOIN  sys.sysconstraints AS con
INNER JOIN sys.sysobjects AS conobj 
        ON con.constid = conobj.id 
        ON con.id = obj.id AND con.colid = col.colid
LEFT JOIN  sys.syscomments ON conobj.id = sys.syscomments.id
LEFT JOIN  sys.sysforeignkeys AS fk
INNER JOIN sys.syscolumns AS fkcol 
        ON fk.rkeyid = fkcol.id AND fk.rkey = fkcol.colid
INNER JOIN sys.sysobjects AS fkobj 
        ON fkcol.id = fkobj.id 
        ON conobj.id = fk.constid 
       AND obj.id = fk.fkeyid 
       AND col.colid = fk.fkey
WHERE (obj.type = 'U') AND (sys.systypes.NAME <> 'sysname')
ORDER BY obj.NAME, col.colid

So what do my tables look like anyway (Part 1)

---

"Where're you gonna find Spock's brain?" - Doctor Leonard McCoy, Star Trek, The Original Series

---

I recently had a developer come to me and ask me for the table structures of every table in this particular schema created by a previous employee. Of course, there's no DDL script anywhere to be found.

So, you could actually extract a layout of the tables from the data dictionary in Oracle.

The following script (which I don't claim to be exactly perfect would be a way to extract your table layout.

First, let's create a couple of dummy tables:

create table Data_Type_Table
( col01 varchar2(100),
  col02 nvarchar2(200),
  col03 char(50) not null,
  col04 nchar(80),
  col05 number(12),
  col06 number(12,5),
  col07 number,
  col08 long,
  col09 date not null,
  col10 timestamp,
  col11 timestamp(6),
  col12 timestamp with time zone,
  col13 timestamp with local time zone,
  col14 interval year(5) to month,
  col15 interval day(4) to second(3),
  col16 raw(12),
  col17 rowid,
  col18 urowid,
  col19 mlslabel,
  col20 clob,
  col21 nclob,
  col22 blob,
  col23 bfile,
  col24 XMLType);

create table data_table
( mycol1 number,
  mycol2 varchar2(400) not null);

The the following script will get you your information (You can tweak the size of the width of the columns to fit your own tables too)

set lines 132
set pages 10000
column table_name format a30
column column_name format a30
column data_type format a40
column nullable format a8

break on table_name skip 2;

select 
  table_name, 
  column_name,
  decode(data_type, 
   'VARCHAR2',data_type||'('||data_length||')',
   'CHAR'    ,data_type||'('||data_length||')',
   'NVARCHAR2'    ,data_type||'('||data_length||')',
   'NCHAR'    ,data_type||'('||data_length||')',
   'NUMBER' ,decode(data_precision, 
                    null, data_type, 
                    data_type||'('||data_precision||','||data_scale||')'),
   data_type) as DATA_TYPE,
  decode(nullable, 'Y', ' ', 'NOT NULL') as NULLABLE
from dba_tab_columns
where owner = 'MYSCHEMA'
order by table_name, column_id

And the output looks a little something like this:

TABLE_NAME      COLUMN_NAME DATA_TYPE                           NULLABLE
--------------- ----------- ----------------------------------- --------
DATA_TABLE      MYCOL1      NUMBER
                MYCOL2      VARCHAR2(400)                       NOT NULL


DATA_TYPE_TABLE COL01       VARCHAR2(100)
                COL02       NVARCHAR2(400)
                COL03       CHAR(50)                            NOT NULL
                COL04       NCHAR(160)
                COL05       NUMBER(12,0)
                COL06       NUMBER(12,5)
                COL07       NUMBER
                COL08       LONG
                COL09       DATE                                NOT NULL
                COL10       TIMESTAMP(6)
                COL11       TIMESTAMP(6)
                COL12       TIMESTAMP(6) WITH TIME ZONE
                COL13       TIMESTAMP(6) WITH LOCAL TIME ZONE
                COL14       INTERVAL YEAR(5) TO MONTH
                COL15       INTERVAL DAY(4) TO SECOND(3)
                COL16       RAW
                COL17       ROWID
                COL18       UROWID
                COL19       MLSLABEL
                COL20       CLOB
                COL21       NCLOB
                COL22       BLOB
                COL23       BFILE
                COL24       XMLTYPE

Object Types in SQL Server

---

"Fascinating!" - Mr Spock, Star Trek, The Original Series

---

The sysobjects table in SQL Server 2000 contains information about the objects in a database. For every constraint, default, role, log, rule, stored procedure etc, there is one row in the sysobjects table.

On of the column in this table is XTYPE. This is the code for the object type of the record. Unlike in Oracle's dictionary view DBA_OBJECTS, where the OBJECT_TYPE column is spelled out in full ('TABLE','VIEW' etc), SQL Server's XTYPE column is just a code, and while some of them may be obvious, others may not be so clear.

If you were to issue the following, you would get back a list of all the user tables in the database:

select * from sysobjects where xtype='U'

So, presented below, is the list.

• C = CHECK Constraint
  
• D = Default or DEFAULT constraint
  
• F = FOREIGN KEY constraint
  
• L = Log
  
• P = Stored Procedure
  
• PK = PRIMARY KEY constraint (type is K)
  
• RF = Replication filter stored procedure
  
• S = System table
  
• TR = Trigger
  
• U = User Table
  
• UQ = UNIQUE constraint (type is K)
  
• V = View
  
• X = Extended stored procedure
  

The sysobjects table is documented here.

In SQL Server 2005 and onwards, the sysobjects view was replaced by sys.objects - this view contains a row for each user-defined, schema-scoped object that is created within a database.

The xtype column is no more - there is now a type column, that still contains the one or two character codes. However, there's also now a type_desc column which gives an associated description. For example, type F has a type_desc value of "FOREIGN_KEY_CONSTRAINT".

The sys.objects table is fully documented in Books Online.

Defragmenting SQL Server Indexes

---

"As of this moment, we are at war!" - Admiral William Adama, Reimagined Battlestar Galactica Mini-Series

---

Kind of a basic tip but it's something that I needed to store here for my reference anyway.

How do you identify your fragmented indexes and then fix them?

The following query will identify fragmented indexes where the fragmentation percent is greater than 10% and there's more than 100 pages in the index. Conventional wisdom is to pretty much exclude indexes from

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent, b.name, ps.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
and ps.avg_fragmentation_in_percent > 10
and ps.page_count > 100
ORDER BY b.name

I have the fortunate ability to be able to rebuild my indexes during monthly maintenance windows. The below script would not be wise to run during system uptime because it's going to cause locks while indexes are being rebuilt.

However, since I have the luxury of a regular maintenance window, I do my rebuilds then.

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
-- conditionally select from the function, converting object and 
-- index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT
   FROM partitions
   INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0
    BEGIN;
        SELECT @objectname = o.name, @schemaname = s.name
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;

        SELECT @indexname = name 
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;

        SELECT @partitioncount = count (*) 
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between 
-- reorganizing and rebuilding

IF @frag < 30.0
    BEGIN;
    SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname +
                  '.' + @objectname + ' REORGANIZE';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + 
                      CONVERT (CHAR, @partitionnum);
    EXEC (@command);
    END;

IF @frag >= 30.0
    BEGIN;
    SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname +
                  '.' + @objectname + ' REBUILD';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + 
                     CONVERT (CHAR, @partitionnum);
    EXEC (@command);
    END;
PRINT 'Executed ' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
GO

The actual "how and why" behind why an index gets fragmented is something for another time.

Oh No, my database went down during a hot backup!

---

"IT'S A TRAP!" - Admiral Ackbar, Star Wars Episode VI: Return of the Jedi

---

So you're doing a hot database backup in Oracle when doing a user managed backup.

You're set up so that you have a script that sets all of your tablespaces into backup mode via "ALTER TABLESPACE ... BEGIN BACKUP", copies your data files to the backup location and then does "ALTER TABLESPACE ... END BACKUP".

While a tablespace is in backup mode, Oracle copies whole data blocks into the redo logs instead of just changed rows and the SCN's in the data file headers are not updated. When a tablespace is taken out of backup mode, Oracle updates the headers of the affected data files to the latest database checkpoint.

So, for whatever reason, your system goes down mid backup. When you try to startup the database, you'll get the following error:

ORA-01113: file 1 needs media recovery

Why this error? Since during the hot backup Oracle isn't updating data file headers with SCN's every checkpoint, the data file headers are out of sync.

So, having issued a "startup" command, the database is mounted but not open (the error occurred during the open phase).

From here you can issue "recover database until cancel".

The database will prompt you for the next archive log file in the sequence, but it will likely not be in the archive log destination. Feed it the destination of the most recent online redo log and you should see a message indicating that media recovery is complete.

Finish it off with an "alter database open noresetlogs" and you have your database back.

UPDATE 11/06/11 - There's an addendum to this post, noting another method to correct this problem here.

References:
Oracle 10g backup documentation

Clustered Indexes and Primary Keys and Unique Constraints, Oh My!

---

"Captain, can you stop her from being cheerful please." - Jayne Cobb, Firefly Pilot

---

At a presentation at SQL Saturday #94 today, the presenter brought up a point I hadn't thought about.  The differences between a primary key, a unique index and a clustered index.

I admit, I wasn't up to snuff on the intricate details between the three so I did a little research into the matter to clarify for myself.

Primary Key

A Primary Key enforces entity integrity on a table, that is, uniqueness.  A Primary Key is one or more columns that uniquely identify a row in a table.  Each table may have one and only one Primary Key.  Any column declared in a Primary Key cannot contain NULL values.  Obviously, because a primary key enforces uniqueness, there can be no duplicate values in the index.

When a Primary Key constraint is created for a table, the database engine creates a unique index for the columns defined in the Primary Key (Clustered, by default, if the table doesn't already have one).  Columns of PRIMARY KEY constraints that are specified when a table is created are implicitly converted to NOT NULL.

If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.  We'll get to clustered indexes in a moment.

A Primary Key can also be used in conjunction with with a Foreign Key of another table to enforce referential integrity.

A Primary Key constraint cannot be dropped if it is referenced by a Foreign Key constraint in another table.  The Foreign Key constraint must be deleted first.

Unique

Unique can apply in two ways - Unique Constraint and Unique Index.

A Unique Index will enforce uniqueness on one or more columns in a database.  There may be more than one Unique Index on a database.  Unique Indexes do not have to be the Primary Key of the table. 

A Unique Constraint can be applied to a column in a table.  When a column is defined with a Unique constraint, the database engine automatically creates a unique nonclustered index covering those columns.

Unique keys and indexes may also be used as the reference point in a Foreign Key Constraint.

Unique Indexes / Constraints allow nulls (where Primary Keys do not).  But, like any value appearing in a unique column, only one null is allowed - uniqueness must be maintained. There could be more than one null in a unique index if the rest of the columns in the index are still unique even with the null.

Example:

create table tab1 (col1 char(2), col2 char(2));
insert into tab1 values ('1', null);
insert into tab1 values ('2', null);

Executing the following fails (duplicate nulls):

create unique nonclustered index idx1 on tab1 (col2);

Executing the following succeeds (Even though there are duplicate nulls, the indexes columns still remain unique):

create unique nonclustered index idx1 on tab1 (col1, col2);

Clustered Indexes

A Clustered Index is created to physically order the data on disk.  The leaf nodes of the index actually contain the data rows of the table.  A table may have only one Clustered Index (since the Clustered Index represents the physical ordering of the data, having more than one would be impossible).

A Clustered Index is not required to be Unique.

The Clustered Index should be built before the Nonclustered Indexes because creating a Clustered Index causes all existing Nonclustered Indexes to be rebuilt.

If not specifically specified to be Clustered, the default setting for creating an Index is Nonclustered.

It is important to note that if "ON FILEGROUP" is specified in creating a Clustered Index, since the table's data pages are stored in the index leaf pages, if a different filegroup is specified on the index that was used in the CREATE TABLE statement, then the table is moved to the filegroup specified in the CREATE INDEX statement.

The Unique Clustered Index on a table does not necessarily have to be the Primary Key of the table.

Non Clustered Index

The leaf layer of a Nonclustered Index contains index pages instead of data pages.

(OK stealing the following couple of paragraphs from the Nonclustered Index Structures page from Books Online since they cover it so very well):

[Begin Steal]

The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

For a table with no Clustered Index (called a Heap), the index page contains a row locator (pointer) to the row.  This pointer is the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).

For a table that uses a Clustered Index, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

[End Steal]

So, a Primary Key without a Clustered Index on the table would use a RID on a related Nonclustered Index to find a row in the table where as a Clustered Index on a table uses the Clustered Index Key on a related Nonclustered Index lookup.

The end result of the fetch is the same, but the how it does it is different.

Now, after reading all this, we get to the difference between a Primary Key and a Clustered Index.  Both can be used to specify uniqueness on a table (if the Clustered Index is defined as Unique of course).  The Primary Key just specifies Uniqueness.  The Clustered Index can define how the Nonclustered Indexes behave.

In order to prevent page splits, it is wise to have the primary index on a table be an ever increasing value.  If rows are inserted into the middle of the existing primary index values, inevitably page splits will occur that fragment the index.

As Kimberly Tripp notes on her blog (see below for the complete article):

In summary, the clustering key really has all of these purposes:

1) It defines the lookup value used by the nonclustered indexes (should be unique, narrow and static)

2) It defines the table's order (physically at creation and logically maintained through a linked list after that) - so we need to be careful of fragmentation

3) It can be used to answer a query (either as a table scan - or, if the query wants a subset of data (a range query) and the clustering key supports that range, then yes, the clustering key can be used to reduce the cost of the scan (it can seek with a partial scan)

This certainly become longer than I had intended, but I believe this covers the major aspects of Clustered and Nonclustered Indexing (with and without Uniqueness) in SQL Server.

References:
http://msdn.microsoft.com/en-us/library/ms191236.aspx
http://msdn.microsoft.com/en-us/library/ms181043.aspx
http://msdn.microsoft.com/en-us/library/ms191166.aspx
http://msdn.microsoft.com/en-us/library/ms177420.aspx
http://msdn.microsoft.com/en-us/library/ms175132.aspx
http://msdn.microsoft.com/en-us/library/ms177443.aspx
http://msdn.microsoft.com/en-us/library/ms177484.aspx
http://sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx

SQL Saturday #94, Salt Lake City

---

"We've done the impossible and that makes us mighty." - Captain Malcolm Reynolds, Firefly Pilot

---

I've only recently discovered PASS - the Professional Association for SQL Server.

This past week I attended 24 Hours of PASS, 24 x 1 hour free training sessions offered via Microsoft Live Meeting.  They were split over 2 days, 12 hours each.  Managed to attend a little over half of them and picked up some interesting tips.

Through following the #sqlpass hash tag on Twitter I also managed to find out about SQL Saturday #94 today in the southern suburbs of Salt Lake City.

So I thought to myself: "Self, it's free training, it's local, why not go?" - so I did.

Huge thanks need to go out to all the speakers but especially to:

Mitchell Bottel (blog | twitter) - Easily manage your servers with CMS and PMB

Christopher Shaw (blog | twitter) - Utility Database

Tjay Belt (blog | twitter) - Data File Sizes - How much is enough when you don't have a lot

Chad Crawford - Table Fission - An Introduction to Partition|ing

Martin Miller (blog | twitter) - Index Management

All were extremely useful sessions.

I guess I should also throw a shout out to Idera Software - since I managed to win the drawing for a free copy of their SQL Admin Toolset.

Pretty cool day overall.

Looking forward to the PASS Summit in Seattle in October!

And off we go

---

"I forgot how much I hate space travel" - C-3PO, Star Wars Episode IV: A New Hope

---

This blog is primarily for my my edification, a place where I can record interesting pieces of information about the life of a DBA, code snippets, tips and tricks.

If anyone finds things here that can help them out in their day to day work, great, but the initial intent is mainly for my use.

Why the name ? I am an Australian who has been living in the United States for almost 20 years. In my early days on the internet, I used to put "Aussie In Exile" at the bottom of my Usenet postings.

Since this is going to be a place to record DBA musings, "DBA In Exile" seemed to fit the theme.

Oh, and since I'm a bit of a Science Fiction nutjob, I may throw some of my favorite movie quotes at the top of each post, just for giggles.