SQL Server Table Design Rules of Thumb

These are not meant to be comprehensive (and don’t get into advanced normalization or intentional denormalization) but will result in a decent table design without too much thought in most situations. A case can be made for natural primary keys but going the surrogate route is easier and works well too.

1. Always have a primary key. Typically a surrogate key using an identity column and not necessarily clustered.
2. Almost always have at least one unique constraint if you used a surrogate primary key
• consider making this/one of these the clustered index as they may be the most common way rows are selected
• these will frequently save you from bugs/errors that would result in duplicate data
• name and abbreviation in a state table are a good example — only one state should have the abbreviation TX and only one should have the name Texas
3. Always include create date and created by columns. If rows can be updated, include updated date, and updated by columns as well.
4. Consider effective and expiration date columns in place of “active” flags.
5. Consider a timestamp (not datetime, but timestamp) field if multiple simultaneous edits are possible
6. Avoid nullable columns – they result in ISNULL/COALESCES — and use defaults instead – EG ‘’ (empty string) for varchar fields, 0 or -1 for ints, etc. A large number of necessarily nullable columns suggest the table may need to be split.
7. Avoid nullable foreign keys — they result in outer joins
8. Consider indexes on any foreign keys
9. Do not store compound values in one column.
10. If you know in advance how your table will be queried and none of the indexes created from rules 1, 2, or 7 meet requirements, consider creating indexes on those columns if they are selective (have a wide distribution of values).
11. You can break any rule except number 1 with good reason after very careful consideration

Check for Temp Table Existence in SQL Server

IF OBJECT_ID(‘tempdb..#sometable’) IS NOT NULL
DROP TABLE #sometable

Oracle (PL/SQL) Equivalents for MS SQL Server (T-SQL) Constructs

For the last several months, I have been working primarily with Oracle 8i and 10g databases.  Over the last 10 years, 95% of my work has been with MS SQL server.  The other 5% was with Oracle, but it was limited to simple SELECT statements or calling stored procedures that other people wrote.  You don’t realize how much basic knowledge you take for granted until you get outside of your comfort zone and have to stop and figure out how to do every little thing.  Here’s a list of the top 10 things I wish I had known about Oracle – PL/SQL coming from an MS SQL Server – T-SQL background.

1. Bind variables versus PL/SQL variables instead of just variables.  Oversimplifying, use “:” in place of “@” when you want a parameterized query.  This is the most awkward and annoying aspect of Oracle compared to MS SQL Server, IMO.  The bottom line is that you can’t very easily copy code from a stored procedure and run it in Toad or SQL Developer.  Nor can you directly assign values to bind variables — you get prompted for them.
2. You can’t do a SELECT without an INTO inside of a PL/SQL block.  This is really puzzling.  Especially since you can’t have anything but bind variables outside of a PL/SQL block and you can’t programmatically assign values to bind variables.  All of this adds up to being forced to be prompted for bind variable values every time you run a parameterized query in the SQL Worksheet or in Toad.
3. There’s no built-in Query Analyzer/SSMS Query tool.  There’s an extremely crippled but free version of Toad.  This what I use if I have to work with a pre-10g instance.  There’s Oracle SQL Developer which is what I use if I am working with a 10g instance.  They both are lacking compared to Query Analyzer/SSMS Query window.  You will press ctrl-E and ctrl-R repeatedly with no effect so many times it will make you sick.
4. DATEADD doesn’t exist.  You have to add fractional days to dates instead.  DATEADD(hour, 1, D) would be D + 1/24.
5. INT can’t be used for columns.  You use NUMBER instead.  This can be a real pain in ADO.NET because you have to explicitly convert all those NUMBERs to ints unless you want to treat them as decimals.
6. No IDENTITY columns.  Instead of typing that one word you have to create a completely separate object called a SEQUENCE and you also have to write a trigger.
7. You can’t have multiple databases on an Oracle instance.  There are just multiple schemas which are really owners.  Public synonyms are used to make objects from different schemas accessible without explicit schema specification. 
8. The default date format does not include the time part.  You have to “alter session set NLS_DATE_FORMAT=’MM/DD/YYYY HH24:MI’;” to see the time part.
9. You concatenate strings with the oh-so-intuitive || instead of the obscure + operation.  Alternatively you can use CONCAT.
10. You can’t PRINT.  There’s a print-like statement, but it only works in SQL*PLUS.  You don’t ever really want to use SQL*PLUS unless you are a massochistic, command-line-loving freak from the past that still uses VI or EMACS.  Actually, there’s a web-based version of SQL*PLUS that’s usable but I like to stay in the SQL Worksheet.

Those are the top 10 — the things I really struggled to understand coming from the MS SQL side of things.  Here are some more that I found useful:

• SP_TABLES = SELECT * FROM ALL_TABLES
• sp_help = DESCRIBE
• You can’t just SELECT without a FROM clause.  You always have to include FROM DUAL.  I still don’t know what “DUAL” is.
• GETDATE() = SYSDATE
• SUSER_SNAME() = USER
• Getting just the date part of a date value: CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101)) = trunc(SYSDATE)
• Just use VARCHAR2 and don’t ask about VARCHAR.
• You can’t return values from procedures using the RETURN statement.  Use an OUT param instead.
• In version 8i and earlier, there’s a “rule-based” optimizer.  The rule-based optimizer can suck badly and you may need to force the join order on some of your queries.  You do that with “SELECT /*+ ordered */” (yes, it really is in the comment).
• The Oracle compiler doesn’t do a good job of telling what’s wrong with your statement.  It can give obscure messages for missing commas and semi-colons.  Don’t forget that you must terminate each statement with a semi-colon.
• There’s no built-in support for UUIDs.
• Oracle does not distingish between an empty string and null (which really sucks)

Here are a couple of non-obvious things I learned about Oracle and .NET 2.0:

• You can’t send multiple SQL statements in a batch using ADO.NET like you can with MSSQL Server
• You can’t use the ODP.NET with the Enterprise Library
• You can’t use non-tnsnames.ora connection strings with System.Data.Oracle but you can with ODP

SQL Server only Accessible Locally

You can ping the server.  You can sign in locally using the computer name via both windows authentication and sql server authentication.  Surprisingly, you can’t even sign in locally using the computer’s own IP address or localhost.  Stop and restart sql server.  View the application log and filter for MSSQLSERVER.  Review the log entries and verify that the server is not just listening on shared memory.  If it is, there’s your problem.  Got the binn directory whereever sql server is installed and run SVRNETCN.exe, the Server Network Configuration Utility.  Enabled TCP/IP.