sqlforge

Wednesday, February 15, 2012

varchar(MAX)

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/

Tuesday, February 14, 2012

restored database owner

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

Friday, January 27, 2012

Invalid Column Error When One Stored Proc Calls Another

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.

Friday, January 13, 2012

Enable CLR

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

Thursday, January 12, 2012

Vertical Text



<HTML>
<HEAD>
  <STYLE>
     .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;
     }
  </STYLE>
</HEAD>


<BODY>

<TABLE BORDER="1"><TR class="heading">
  <TH class="verticaltext">Column-A</TH>
  <TH class="verticaltext">Column-B</TH>
  <TH class="verticaltext">Column-C</TH>
</TR>
<TR>
  <TD>Row 1 Col A</TD>
  <TD>Row 1 Col B</TD>
  <TD>Row 1 Col C</TD>
</TR>
<TR>
  <TD>Row 2 Col A</TD>
  <TD>Row 2 Col B</TD>
  <TD>Row 2 Col C</TD>
</TR>
</TABLE>
</BODY>

</HTML>

Wednesday, January 4, 2012

DROP all VIEWS, FUNCTIONS, PROCEDURES

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.

Using Cursors Optimally

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/