Author: sqldba

30 January 2012
by Rob Garrison
www.simple-talk.com

* Where appropriate, use VARCHAR(n) over VARCHAR(MAX) for reasons of good design if not performance benefits, and because VARCHAR(MAX) data does not compress

* Storing large strings takes longer than storing small strings.

* Updating an in-row VARCHAR(MAX) value from below 8,000 to over 8,000 will be relatively slow, but the difference for a single transaction will likely not be measurable.

* Updating an in-row VARCHAR(MAX) value from over 8,000 to below 8,000 will be faster than if the table is set to store data out-of-row.

* Using the out-of-row option for VARCHAR(MAX) will cause slower writes until the strings are very long.

Full article
http://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar%28n%29-anymore/

Ran into an interesting problem with a SQL2005 database being restored to a SQL2008R2 instance.

Turned out the database owner was correctly represented as the login that restored the database, however the login mapped to the “dbo” user was still mapped to the old login on the source server.

To fix this, ran the following:

alter authorization on database::[database_name] to [login_name]
go

I’ve run into a problem where one stored procedure calls another and I get an “Invalid Column” error. The child stored proc works fine when run separately.

Some similar errors could easily be cleaned up by using sp_recompile or sp_refreshsqlmodule.

But, turns out there is a little bug in the column name validation when there are temp tables in a child stored proc that have the same name as a temp table in the parent proc.

Solution: rename the temp table in one proc or another. Or, could use variable tables or some other structure in one or the other.

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

http://msdn.microsoft.com/en-us/library/ms131048.aspx

     .verticaltext

     {

        writing-mode:tb-rl;

        filter: flipv fliph;

        -webkit-transform:rotate(270deg);

        -moz-transform:rotate(270deg);

        -o-transform: rotate(270deg);

        white-space:nowrap;

     }
     .heading

     {

        background: #000000;

        color: #FFFFFF;

        font-weight: bold;

        height: 90px;

     }
















  
Column-A
  
Column-B
  
Column-C


  
Row 1 Col A
  
Row 1 Col B
  
Row 1 Col C


  
Row 2 Col A
  
Row 2 Col B
  
Row 2 Col C

DECLARE @obj TABLE ( id int identity, objname sysname, objtype sysname )

DECLARE @i int

      , @sql nvarchar(max)

INSERT INTO @obj ( objname, objtype )

          SELECT A.name

               , (case when A.type = 'V' then 'VIEW'

                       when A.type in ('FN','IF','TF') then 'FUNCTION'

                       when A.type = 'P' then 'PROCEDURE'

                       else '' end )

            FROM sysobjects A (NOLOCK)

           WHERE A.type IN ( 'V', 'FN', 'IF', 'TF', 'P' )

        ORDER BY (case A.type when 'V' then 3

                              when 'P' then 1

                              else 2 end )

               , A.name DESC
SELECT @i = max(id) FROM @obj
WHILE @i > 0

 BEGIN

   SELECT @sql = 'DROP ' + A.objtype + ' ' + A.objname

     FROM @obj A

    WHERE A.id = @i

   PRINT @sql

   SET @i = @i - 1

 END

This is one example where using a table variable and while loop on a relatively small set of data is preferred, simply because the "sysobjects" table will be mutating through the process of the loop. Could still get away with it in a cursor if locks and hints are properly applied, but I still prefer to avoid it when possible.

sqlwithmanoj demonstrated cases where using optimization hints such as FAST_FORWARD, FORWARD_ONLY, READ_ONLY can result in cursors that are more performant than while loops on table variables.

http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/

DECLARE @tabs TABLE (  id int identity(1,1) not null

                     , tabname sysname not null );

DECLARE @i int

      , @tabname sysname;
   INSERT INTO @tabs ( tabname )

        SELECT name

          FROM sysobjects

         WHERE type = ‘U’;
SELECT @i = max(id) FROM @tabs;
WHILE @i > 0

 BEGIN

   SELECT @tabname = tabname FROM @tabs WHERE id = @i

   PRINT ‘UPDATE STATISTICS ‘ + @tabname + ‘ WITH ALL’

   SET @i = @i – 1

 END;
GO

1. Data type determination of the resulting expression – ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence

2. The NULLability of result expression can be different for ISNULL and COALESCE. ISNULL return value is considered NOT NULLable if the return value is a non-nullable one (in the case when the argument that is returns is based on a non-null column or constant). Whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.

3. Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex:

ISNULL(NULL, NULL) — is int

COALESCE(NULL, NULL) — Will throw an error

COALESCE(CAST(NULL as int), NULL) — it valid and returns int

4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters. You have to have nested ISNULL to get the same effect as COALESCE.

5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL built-in function.

6. You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and some queries with COALESCE be sub-optimal.

The way to implement a “many-to-many” relationship requires an additional table be created consisting of the primary keys from each of the entities. This additional table goes by many different names depending on who you talk to.

Association table
Bridge table
Cross-reference (xref) table
Intersection tables
Join table (perhaps most common)
Junction table
Link table, link entity
Many-to-many relationship tables
Map table
Reference table
Relationship table
Swing table

Probably the most proper (being a mathematical model) but least used is “Gerund” — so named by E.F. Codd, creator of the relational model. This term applies because an entity is functioning as a relationship.