Default value on a column

What is the default value on my column?  (Take out the criterion for a broader result set)

FROM col.name as col,   defaultvalue = c.text
FROM syscomments c
JOIN
     (SELECT name, cdefault 
      FROM syscolumns
      WHERE id = object_id(‘MYTABLE’)
                  and name = ‘MYCOLUMN’
                  AND cdefault > 0
     )  col on c.id = col.cdefault

 

How do you modify the default value on my column?  I didn’t know, but it is actually a constraint.  So it is a drop and a re-create for a constraint.

ALTER TABLE [dbo].[MYTABLE] drop  CONSTRAINT [MYCONSTRAINT]

ALTER TABLE [dbo].[MYTABLE] ADD  CONSTRAINT [CONSTRAINT]  DEFAULT (‘MYDEFAULTVALUE’) FOR [MYCOLUMN]‘

I would rather be in-sensitive

Somehow I missed the fine print and downloaded the AdventureWorks Case Sensitive Database.  Specifically, my collation is set to Latin1_General_CS_AS.  The CS apparently stands for Case Sensitive.  (To see the collation of your database right click, select properties, and go to the general tab…it’s at the bottom).  Unfortunately, being AdventurWorks I have put up with it…until now.  I’m tired of re-writing queries in the RiGht CaSe. 

According to this article you can run this command (in single user mode)

ALTER DATABASE AdventureWorks COLLATE Latin1_General_CI_AS

But Sql server will bleed all over your screen with all kinds of dependency errors.  Unfortunately collation can be set at the database, column, and table level.  So to fix it you would have to change the collation at every level. 

Or you can just download a new one from here.  Make sure you pick one that has CI at the end of it.

FIXED!

Breaking up is hard to do

There are a lot of great posts about splitting a table into a list or splitting a list into a table.  Do a search and you will be a little overwhelmed by the good and the bad advice.  Let me turn you on to a couple of excellent articles.  Anith wrote an excellent must read article titled Faking Arrays where he explores the various  sundry ways to do this.  Itzik Ben-Gan (my performance hero) has spent a lot of time determining the most efficient way to split data in a whitepaper titled Arrays and Lists in Sql Server 2005 (it still applies in 2008). 

After gleaning over these articles and countless others I’ve surmised the following four methods. 

1.

The first is splitting your table to a character list.  It’s pretty simple, really.  Just declare your variable and set it to an empty string.  Setting it to an empty string is required unless you want to mess with the ‘SET CONCAT_NULL_YIELDS_NULL’ setting which I don’t recommend…some settings actually modify the database and cause your stats to restart…not sure about all this particular setting though and I’m not really up on this little fact.  Why is it required?  Well, you can’t add a string to a null value, can you.  1 + null just doesn’t make any sense and will always return you a null (unless you mess with the setting above of course.  Anyway, this is how you do it.

declare @d varchar(100)=” 
select @d = @d + ‘ your delimeter ‘ +  field from yourtable
print @d

2. 

The second method is splitting your table into a character list utilizing the new 2005 ‘for xml’ command.  From what I understand this performs better than the first, but it’s not as readable:

select yourfield= STUFF(
             (SELECT  ‘ ‘ + yourfield AS [text()]
              from yourtable 
              for xml PATH(”)),1,1,’,')

3.

The third method we broach the discussion of splitting a list into a table.  The most efficient method of doing this is using a custom clr.  Text-parsing isn’t sql server’s forte, but it is for iterative-based languages like vb, c#, etc.  But this isn’t the way I do it because it requires modifying the database to allow clr operations and this means getting permission and jumping through hoops, etc.  So the next best method is using a numbers table with a table-valued function that joins to this table.  This is Itzik-tested because it comes from Itzik.  As far as creating a creating a numbers table, don’t waste your time searching, just use this:

CREATE TABLE [dbo].[Numbers](
    [Number] [int] NOT NULL
)
declare @fromNumber int =0, @toNumber int=100,
@byStep int=1

;

WITH CTE_NumbersTable AS (
SELECT @fromNumber AS i
UNION ALL
SELECT i + @byStep
FROM CTE_NumbersTable
WHERE
(i + @byStep) <= @toNumber
)
insert into numbers SELECT * FROM CTE_NumbersTable

Now that this is done here is the function that can be used to split a list into a table (which utilizes the numbers table):

CREATE FUNCTION [dbo].[split_to_table](@array AS VARCHAR(1000), @delimiter as varchar(6)) RETURNS TABLE
AS

RETURN
  SELECT
      ltrim(rtrim(SUBSTRING(@array, number,
      CHARINDEX(@delimiter, @array + @delimiter, number) – number))) AS element
  FROM dbo.Numbers
  WHERE number <= LEN(@array) AND SUBSTRING(@delimiter + @array, number, 1) = @delimiter

4.

Method number 4 applies when you don’t have access to create a numbers table.  Lots of ways to do this and this is one of them.  This was put together with the first article I mentioned by Anith.  I couldn’t get the max recursion option to work because I was in sql 2000 but I left it in.

CREATE FUNCTION [dbo].[Split]
(
@p VARCHAR(500), @delimeter varchar(3)
)
–select * from split(”,’|')
RETURNS @tblSplit TABLE (value varchar(100))
AS
BEGIN
IF ISNULL(@P,”) <> ”
    BEGIN
        WITH CTE ( pos, pos_begin, pos_end ) AS (
                SELECT 0, 1, CHARINDEX( @delimeter, @p + ‘,’ )
                 UNION ALL
                SELECT pos + 1, pos_end + 1, CHARINDEX( @delimeter, @p + @delimeter, pos_end + 1 )
                  FROM CTE
                 WHERE CHARINDEX( @delimeter, @p + @delimeter, pos_end + 1 ) > 0 )
            insert @tblSplit SELECT  SUBSTRING( @p, pos_begin , pos_end – pos_begin ) AS "value"
              FROM CTE
            –OPTION ( MAXRECURSION 0 ) ;
    END
RETURN
END

 

Lots of ways to do this….these are my top four.  If you have a better way let me know.

Server Objects…Owned!

I wanted to find out what objects I owned before I deleted myself as a user. Lucky for me I found RBarry Young who showed me that I didn’t own anything.  He showed me with a post to SqlServerCentral.com.  Thank you Mr.Young!  Excellent stuff!!!!

Is there an Identity seed on this table?

A couple of things I’ve discovered lately while working with the identity property of a column.

1.  How to check to see if an identity seed exists on a table and what the column is:

SELECT object_name(id) as table_name, name 
FROM syscolumns
WHERE columnproperty(id, name, ‘IsIdentity’) = 1
and object_name(id) = ‘mytable’ –table name (leave this line off just to show them all)

2.  How to check to see what the identity values are for that column (what is the base seed and what is the increment value):

select table_name, ident_seed(table_name) as seed, ident_incr(table_name) as incrment
from information_schema.tables where ident_seed(table_name) IS NOT NULL and table_name = ‘mytable’

3.  You can only have one identity column on a table.

4.  You can reseed it at any time with any new seed.

DBCC CHECKIDENT (tablename, reseed, 0)

5.  You can set your own number on an insert with Identity_Insert turned on (without the mess in #6). 

6.  You can just ‘turn off’ and ‘turn on’ that property in SSMS.  Easy. Pie. Simple.  Even FUN!  But what goes on behind the scene?  A lot of ugliness.  Don’t do it unless you really need to.  It creates a new table. inserts into your new table. drops your original, renames, re-creates indexes, etc.  Basically, it just throws up all over your server and data:

CREATE TABLE dbo.Tmp_seedtest
    (
    …
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_seedtest SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_seedtest ON
GO
IF EXISTS(SELECT * FROM dbo.seedtest)
     EXEC(’INSERT INTO dbo.Tmp_seedtest (year, id, projectname)
        SELECT year, id, projectname FROM dbo.seedtest WITH (HOLDLOCK TABLOCKX)’)
GO
SET IDENTITY_INSERT dbo.Tmp_seedtest OFF
GO
DROP TABLE dbo.seedtest
GO
EXECUTE sp_rename N’dbo.Tmp_seedtest’, N’seedtest’, ‘OBJECT’
GO
CREATE UNIQUE CLUSTERED INDEX ix_ucl_yrid ON dbo.seedtest
    (
    …
    )

Padding your numbers…just a little

I know padding your numbers in this economic crisis is a touchy issue.  But let’s put our morals to the side for a moment and discuss how to do it.  OK.  Actually by ‘padding’ I’m really just meaning adding something extra flair  to your number.  Example

       boring example: changing your number from 36 to 00036 
       exciting example: changing your number from 36 to WOW_LOOK_I_AM_36

Formatting in t-sql is limited.  It doesn’t really care about how you want your data to look.  What you wear to work isn’t the datacenter’s job.  That’s your mother’s job. 

The new function I discovered is STR.  Not sure why I never used it before.  The second argument for STR is how many characters you want your number to be…and it fills it with spaces.  So STR(3,2) gives us the number 3 represented as a 2 character string like so: ‘ 3’.  So throw in a replace (change spaces to 0s) and you have your pad.  I placed mine in a computed column using an id column:

replace(str(id,3),’ ‘,0)

if you want to copy and paste the code below you can test it out

declare @myage int
set @myage = 36 –enter your age
select replace(str(@myage,3),’ ‘,’WOW_LOOK_I_AM_’)

Access data within a linked server within a stored procedure within a job on Tuesdays from space

Setting up a linked server and accessing that server within a certain security context is easy.  Just add the server to the linked server collection (under server objects) and set your user/pass in the security section of the friendly GUI.  (Here’s the t-sql version:)

sp_addlinkedserver [ @server = ] ’server’
    [ , [ @srvproduct = ] ‘product_name’ ]
    [ , [ @provider = ] ‘provider_name’ ]
    [ , [ @datasrc = ] ‘data_source’ ]
    [ , [ @location = ] ‘location’ ]
    [ , [ @provstr = ] ‘provider_string’ ]
    [ , [ @catalog = ] ‘catalog’ ]

For the sake of our discussion I set the security on this linked server to MY_REMOTEUSER_NAME.

That works.  I can now execute select top 188 * from SERVERNAME.DATABASE.DBO.TABLE all day long (full qualification of server is necessary).  I can even run it from within a stored procedure.  The problem I had was when I wanted to run the procedure from a job.  When I did I received the error:

The job failed.  Unable to determine if the owner (DOMAIN\MCLINGAN) of job JOBNAME has server access (reason: Could not obtain information about Windows NT group/user ‘DOMAIN\MCLINGAN’, error code 0×5. [SQLSTATE 42000] (Error 15404)).

This is obviously a security problem.  I tried Execute AS in my stored proc…..didn’t work.  Recognizing that jobs run under the server agent login I tried adding it to my linked server, but my linked server security already stated that for a login not defined in the list connections will be made using MY_REMOTEUSER_NAME.  Adding any of the mssql defined logins to my linked server proved pointless. 

The following also didn’t work.  It seemed like a reasonable solution to me…enabling you to run a query specifying a linked server

select * from OPENQUERY(LINKEDSERVERNAME, ’select top 188 * SERVERNAME.DATABASE.DBO.TABLE’ )

The solution seemed strange to me:

DID YOU KNOW that the owner of the package was more than just informational?  it has direct security implications to the steps therein.  My solution:

1.  Add MY_REMOTEUSER_NAME to the database server and the database with the correct permissions of course.

2.  Modify the job owner to MY_REMOTEUSER_NAME that I added in step one.

BONUS:  When you use a linked server and operate under your database to connect to it you can use all the bells and whistles of sql serer 2008 on a 2000 database.  I have a linked server to a 2000 box and I can use my 2008 instance to query it with merge, rank, etc.

Middle Click to end your day

If you use your middle click button to close tabs, don’t do it in ssms for the tab you aren’t on.  It will close the tab you are on. 

Explanation: I use the middle click to close tabs.  It works in ie, firefox, chrome, ssms, visual studio, and coworkers.  Anytime you want to end a conversation or a window just middle-click with the mouse.  It quickly solves all your juggling-in-memory-tired-of-talking-to-this-person tasks.  It works brilliantly on active windows that you are currently conversing in…. as well as the in-active, behind the scene tabs.  (the ones that are still slowing down your performance but you might need in two days but you don’t want to add to your ever expanding favorites that you never look at)

The exception to this middle click wonderfulness? SQL Server Management Studio.

Follow me: Open a new query window and write something like ‘leave me.close me’.  Now open another one and write ‘don’t leave me. leave me open.’  And leave that one up staring back at you.  With your mouse middle clicker button wheelie thing punch it on the inactive window (the one you can’t see that says ‘leave me.close me’).  What happens?  What is looking back at you?  The one that says ‘leave me.close me.’    See?  It closes the one you are on. 

It only takes one time to learn this silly little exception…if this happens on a query that you have put your soul into.  Yes.  You are one middle wheelie click thingie away from having a BAD DAY.

(this applies to ssms 2005 and 2008)

New photography site

I like to keep my personal and professional life somewhat separate, but there is this area that falls in the gray between those two lives.  So I set up another blog to exploit it a little. 

I like to take pictures and have started up another site called http://mattclingan.com.  Check it out.  I will be keeping this one technical, but I just thought I’d throw up a post about it.  Thanks for placating.

 

I found it! Reg expressions for searching my t-sql

I’m always looking for this and can never seem to find it in the 45 second search allotment of time I give it to non-important but important stuff.  So….I like to find and replace in my sql and I can never remember how to do a search for a line break or replace with a line break.  And guess what.  It’s \n.  See below for all the others.  Found this on books online. 

I’m so happy!  My life is now complete.

Expression	Syntax	Description
Any character	.	Matches any single character except a line break.
Zero or more	*	Matches zero or more occurrences of the preceding expression, making all possible matches.
One or more	+	Matches at least one occurrence of the preceding expression.
Beginning of line	^	Anchors the match string to the beginning of a line.
End of line	$	Anchors the match string to the end of a line.
Beginning of word	<	Matches only when a word begins at this point in the text.
End of word	>	Matches only when a word ends at this point in the text.
Line break	\n	Matches a platform-independent line break. In a Replace expression, inserts a line break.
Any one character in the set	[]	Matches any one of the characters within the []. To specify a range of characters, list the starting and ending character separated by a dash (-), as in [a-z].
Any one character not in the set	[^...]	Matches any character not in the set of characters following the ^.
Or	|	Matches either the expression before or the one after the OR symbol (|). Mostly used within a group. For example, (sponge|mud) bath matches "sponge bath" and "mud bath."
Escape	\	Matches the character that follows the backslash (\) as a literal. This allows you to find the characters used in regular expression notation, such as { and ^. For example, \^ Searches for the ^ character.
Tagged expression	{}	Matches text tagged with the enclosed expression.
C/C++ Identifier	:i	Matches the expression ([a-zA-Z_$][a-zA-Z0-9_$]*).
Quoted string	:q	Matches the expression (("[^"]*")|(’[^']*’)).
Space or Tab	:b	Matches either space or tab characters.
Integer	:z	Matches the expression ([0-9]+).

The list of all regular expressions that are valid in Find and Replace operations is longer than can be displayed in the Reference List. You can also insert any of the following regular expressions into a Find what string:

Expression	Syntax	Description
Minimal — zero or more	@	Matches zero or more occurrences of the preceding expression, matching as few characters as possible.
Minimal — one or more	#	Matches one or more occurrences of the preceding expression, matching as few characters as possible.
Repeat n times	^n	Matches n occurrences of the preceding expression. For example, [0-9]^4 matches any 4-digit sequence.
Grouping	()	Groups a subexpression.
nth tagged text	\n	In a Find or Replace expression, indicates the text matched by the nth tagged expression, where n is a number from 1 to 9. In a Replace expression, inserts the entire matched text.
Right-justified field	\(w,n)	In a Replace expression, right-justifies the nth tagged expression in a field at least w characters wide.
Left-justified field	\(-w,n)	In a Replace expression, left-justifies the nth tagged expression in a field at least w characters wide.
Prevent match	~(X)	Prevents a match when X appears at this point in the expression. For example, real~(ity) matches the "real" in "realty" and "really," but not the "real" in "reality."
Alphanumeric character	:a	Matches the expression ([a-zA-Z0-9]).
Alphabetic character	:c	Matches the expression ([a-zA-Z]).
Decimal digit	:d	Matches the expression ([0-9]).
Hexadecimal digit	:h	Matches the expression ([0-9a-fA-F]+).
Rational number	:n	Matches the expression (([0-9]+.[0-9]*)|([0-9]*.[0-9]+)|([0-9]+)).
Alphabetic string	:w	Matches the expression ([a-zA-Z]+).
Escape	\e	Unicode U+001B.
Bell	\g	Unicode U+0007.
Backspace	\h	Unicode U+0008.
Tab	\t	Matches a tab character, Unicode U+0009.
Unicode character	\x#### or \u####	Matches a character given by Unicode value where #### is hexadecimal digits. You can specify a character outside the Basic Multilingual Plane (that is, a surrogate) with the ISO 10646 code point or with two Unicode code points giving the values of the surrogate pair.

The following table lists the syntax for matching by standard Unicode character properties. The two-letter abbreviation is the same as listed in the Unicode character properties database. These may be specified as part of a character set. For example, the expression [:Nd:Nl:No] matches any kind of digit.

Expression	Syntax	Description
Uppercase letter	:Lu	Matches any one capital letter. For example, :Luhe matches "The" but not "the".
Lowercase letter	:Ll	Matches any one lower case letter. For example, :Llhe matches "the" but not "The".
Title case letter	:Lt	Matches characters that combine an uppercase letter with a lowercase letter, such as Nj and Dz.
Modifier letter	:Lm	Matches letters or punctuation, such as commas, cross accents, and double prime, used to indicate modifications to the preceding letter.
Other letter	:Lo	Matches other letters, such as gothic letter ahsa.
Decimal digit	:Nd	Matches decimal digits such as 0-9 and their full-width equivalents.
Letter digit	:Nl	Matches letter digits such as roman numerals and ideographic number zero.
Other digit	:No	Matches other digits such as old italic number one.
Open punctuation	:Ps	Matches opening punctuation such as open brackets and braces.
Close punctuation	:Pe	Matches closing punctuation such as closing brackets and braces.
Initial quote punctuation	:Pi	Matches initial double quotation marks.
Final quote punctuation	:Pf	Matches single quotation marks and ending double quotation marks.
Dash punctuation	:Pd	Matches the dash mark.
Connector punctuation	:Pc	Matches the underscore or underline mark.
Other punctuation	:Po	Matches (,), ?, ", !, @, #, %, &, *, \, (:), (;), ‘, and /.
Space separator	:Zs	Matches blanks.
Line separator	:Zl	Matches the Unicode character U+2028.
Paragraph separator	:Zp	Matches the Unicode character U+2029.
Non-spacing mark	:Mn	Matches non-spacing marks.
Combining mark	:Mc	Matches combining marks.
Enclosing mark	:Me	Matches enclosing marks.
Math symbol	:Sm	Matches +, =, ~, |, <, and >.
Currency symbol	:Sc	Matches $ and other currency symbols.
Modifier symbol	:Sk	Matches modifier symbols such as circumflex accent, grave accent, and macron.
Other symbol	:So	Matches other symbols, such as the copyright sign, the pilcrow sign, and the degree sign.
Other control	:Cc	Matches end of line.
Other format	:Cf	Formatting control character such as the bi-directional control characters.
Surrogate	:Cs	Matches one half of a surrogate pair.
Other private-use	:Co	Matches any character from the private-use area.
Other not assigned	:Cn	Matches characters that do not map to a Unicode character.

In addition to the standard Unicode character properties, the following additional properties may be specified as part of a character set.

Expression	Syntax	Description
Alpha	:Al	Matches any one character. For example, :Alhe matches words such as "The", "then", and "reached".
Numeric	:Nu	Matches any one number or digit.
Punctuation	:Pu	Matches any one punctuation mark, such as ?, @, ‘, and so on.
White space	:Wh	Matches all types of white space, including publishing and ideographic spaces.
Bidi	:Bi	Matches characters from right-to-left scripts such as Arabic and Hebrew.
Hangul	:Ha	Matches Korean Hangul and combining Jamos.
Hiragana	:Hi	Matches hiragana characters.
Katakana	:Ka	Matches katakana characters.
Ideographic/Han/Kanji	:Id	Matches ideographic characters, such as Han and Kanji.