Another good tip for beginners - remember that if you make a simple change in SQL Server Management Studio (SSMS) by opening up the table in design mode, you might see your entire IDE hang; the reason is because SQL Server could be doing a complete DML operation in the background, moving all of your rows into another table. Notice that I take a very large table – many rows – and alter a column to make it “NULLable”: In profiler you can see that SQL Server escalated locking, created a new table, and the inserted rows from the existing table using HOLDLOCK and TABLOCKX hints. You’ll probably never be able to select out of, in this case, Tmp_tblCallLog, by the way. SSMS has chosen to move the data out into a new table, which may take a considerable time, my table here with about 50 million rows. I know that there’s a better way of doing this, so I kill the process…SQL Server will roll back whatever it tried to do of course, so be prepared for this if you happen to go down this path: The best way to change this column here is to simply do it in TSQL; you won’t get the huge background operation by SQL Server…profiler shows a simple change to the entire column without moving all of the data around, renaming temp tables, etc. Thanks, Lee ------------------------
Posted in:
Beginner Tags:
6e44ff78-a10b-44f5-be23-084918671b16|0|.0
Someone asked me the other day what blogs I follow. I guess I don’t follow too many, but if I had to recommend some, Aaron Bertrand has a really good blog and covers some good stuff. Tim Mitchell is a good friend and someone I consider a cool dude, and Jamie Thomson always posts some great things for the community. I guess I also go over to SQLSkills.com every so often…otherwise I don’t follow too many blogs. I use them as references like most folks, so I don’t get up in the morning and peruse their pages per se. Blogs that contain notes from the field, or other development experiences are good references, but if I want information, I still recommend MSDN or TechNet for the definitive and authoritative word on SQL Server.
Anyway, I happened to catch one of a little series on Aaron’s blog on “Bad Habits to Kick” this morning. It’s a good series and I recommend that you check it out. I add one that he did not mention. Here is his code:
USE test
GO
SELECT 1 AS x; -- #1
SELECT x = 1; -- #2
SELECT 1 'x'; -- #3
SELECT 1 x; -- #4
Here’s one that I really don’t care to see:
SELECT 1 "x"
Mostly, though, #4 is one I really have a problem with. I really don’t like alias just sort of stuck out there; it makes the code really hard to read without an AS. This convention screams the 2-4 year TSQL guy in my opinion. I used to do little ‘short cuts’ like these, but that was back in the day. A seasoned TSQL coder a more strict set of coding standards to avoid little errors later on down the road. And, enjoy less-frequent errors in his or her code as well.
Here is an argument to add an AS anytime a column is referenced, even if the alias is the same name as the column. Consider the following:
DROP VIEW vwMyView
GO
CREATE VIEW vwMyView
AS
SELECT TABLE_CATALOG
,TABLE_NAME
,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
GO
Looks pretty good, but what if someone added a column but did not add aliases:
ALTER VIEW vwMyView
AS
SELECT TABLE_CATALOG
,TABLE_NAME
,ORDINAL_POSITION
COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
GO
SELECT * FROM vwMyView
Notice that it accidentally thinks that the third column, ORIDINAL_POSITION, alias is actually a column that was added to the SELECT statement. The programmer forgot to add a comma so it aliases the column as COLUMN_DEFAULT. I have run into this before (just last week) with a view that included many columns. If AS was used for each, it probably would not have happened:
Little things like this keep the phone from ringing at 2:00 am in the morning, and because as data folks we have to deal with problems all of the time, creating even more problems is something I try to avoid.
Thanks for reading, Lee
----------------------------
Reference: http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/23/bad-habits-to-kick-using-as-instead-of-for-column-aliases.aspx
Posted in:
Beginner ,
TSQL Tags:
804026e4-6ab6-4834-8e15-5b74e7363c7b|0|.0
Making changes to an existing table – in this case NULLability – with indexes on the table and you may get the following error:
Msg 5074, Level 16, State 1, Line 2 The index 'idx_test' is dependent on column 'isDeleted'. Msg 4922, Level 16, State 9, Line 2 ALTER TABLE ALTER COLUMN isDeleted failed because one or more objects access this column.
In this instance, you’ll have to drop the index(es) referencing the column before you can alter the column. If you try to disable the index via ALTER INDEX indexname on table DISABLE, you’ll get the same error…no can do. Have to get rid of it first. Here’s an example:
DROP TABLE idxTest
GO
CREATE TABLE idxTest
(ID INT
,isDeleted BIT NULL
)
GO
INSERT idxTest VALUES (1,0)
GO
CREATE INDEX idx_test ON idxTest (ID, isdeleted)
GO
--ALTER TABLE idxTest
-- ALTER COLUMN isDeleted BIT NOT null
GO
DROP INDEX idxTest.idx_test
GO
ALTER TABLE idxTest
ALTER COLUMN isDeleted BIT NOT null
GO
CREATE INDEX idx_test ON idxTest (ID, isdeleted)
GO
Notice that I’ve commented out the ALTER TABLE, but you can uncomment and give it a try.
Thanks for reading, Lee
----------------------
40c699d0-a99d-4bec-b880-0b1d3be3cfb9|0|.0
Dropping indexes when bulk loading a table is a rule #1 kind of deal, beginner stuff – if you remove the indexes, you’ll be better off nine times out of ten. Dropping a clustered index makes sense on a natural key, because worst case SQL Server will have to deal with reordering the key on the fly. For nonclustered indexes, well, this is one that a lot of folks don’t consider; sometimes, the results are dramatic. Myself and fellow associate Harry Doan ran into this the other day, where the developer left indexes on an empty table in an SSIS process. In this blog I’m going to demo a scenario where I keep the nonclustered indexes in place, and then drop them for a data load. Next time I will test the scenario when the table has n number of rows already in the table. One consideration for dropping nonclustered indexes on an empty table is the answer to the question…”What’s faster – loading a table with the indexes, or loading the table without the indexes, and then rebuilding indexes after the data has been landed?” For my test, I have a super-slammin’ box (Dell R900 with 200GB RAM). Run the following code: USE tempdb
GO
DROP TABLE TestNC
GO
CREATE TABLE TestNC(
ID int IDENTITY (0,1) NOT NULL
,Col1 varchar (4)
,Col2 varchar (10)
,Col3 int
,Col4 int
,Col5 varchar (7)
)
GO
CREATE NONCLUSTERED INDEX idx1 ON TestNC (Col1, Col2, Col3)
GO
CREATE NONCLUSTERED INDEX idx2 on TestNC (Col4, Col5)
GO
DROP TABLE #data
GO
SELECT x.C1, x.C2, ROW_NUMBER() OVER (ORDER BY C3) AS C3, ROW_NUMBER() OVER (ORDER BY c4) as C4, c5
INTO #data
FROM
(SELECT TOP 10000000 SPACE(4) AS C1
,'AAAAAAAAAA' AS C2
,0 AS C3
,1 AS C4
,'AAAAAAA' AS C5
FROM sys.syscolumns AS a, sys.syscolumns AS b, sys.syscolumns as c
)x
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
So now the table is ready, with indexes built and data staged to load. Run this script now:
INSERT INTO TestNC WITH (TABLOCKX)
SELECT TOP 6000000 * FROM #data
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Now drop the table, and recreate without the indexes, then load the table. Finally, go rebuild the indexes. Which is faster? A typical scenario such as this one with a decent-sized box will – most of the time – work as such. The increase here is pretty dramatic because I’m using a strong SQL Server.
By the way, just the other day, someone else blogged about this, but really didn’t run a test like we have here. One thing that they mentioned is disabling rather than dropping indexes “is better than dropping indexes.” Of course, when I read something like this, I have to go find out for myself, because more often than not proclaiming absolutes in SQL Server will more often than not come back and bite you in the behind. This is not trying to call someone out – we have too many SQL commandos and ninjas in world already trying to show someone up and flex their ego. Ego, man I left mine behind many years ago, and I’m much happier today. If you have one, my advice to you is lose it. Egos are for classless losers and punks.
Additional code:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TestNC]') AND name = N'idx1')
DROP INDEX [idx1] ON [dbo].[TestNC] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [idx1] ON [dbo].[TestNC]
(
[Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [tempdb]
GO
/****** Object: Index [idx2] Script Date: 10/04/2011 16:43:06 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TestNC]') AND name = N'idx2')
DROP INDEX [idx2] ON [dbo].[TestNC] WITH ( ONLINE = OFF )
GO
USE [tempdb]
GO
/****** Object: Index [idx2] Script Date: 10/04/2011 16:43:06 ******/
CREATE NONCLUSTERED INDEX [idx2] ON [dbo].[TestNC]
(
[Col4] ASC,
[Col5] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER INDEX idx1
ON TestNC DISABLE;
ALTER INDEX idx2
ON TestNC DISABLE;
ALTER INDEX idx1
ON TestNC REBUILD
ALTER INDEX idx2
ON TestNC REBUILD
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Here are my results, including the ALTERs rather than drops. Try for yourself…don’t believe me like I didn’t believe that dude.
TEST RUN
Indexes insert time index time (idx1/2) index action total
---------------------------------- ----------------------- ---------------------- ------ ------
Indexes on table 2:28 n/a 2:28
Not on table 0:06 :31+:01 drop 0:38
Not on table 0:06 :32+:02 disable 0:40
So based on this minimal test, a load with indexes too two additional minutes, and the disable as opposed to the drop index too two seconds longer. Try some tests for yourself and see what you can come up with. Next time I’m going to look at the loading chart in Book Online and “see for myself”.
Lee
-------------------------
Refs and other readings
http://blogs.technet.com/b/sql_server_isv/archive/2011/10/04/redprairie-removing-unneeded-table-indices.aspx
http://msdn.microsoft.com/en-us/library/dd425070(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/ms177445.aspx
Posted in:
TSQL ,
Beginner Tags:
843c8f38-cdf8-40fb-a03b-9b132aed03ef|3|5.0
Someone just asked me if there's a built-in function in TSQL that takes a string and changes the first letter of each word to upper case. I told them hell I don't know but I think not; here's a way to do it without a bunch of char functions...use a CLR UDF. One line and all works pretty well:
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Globalization;
public partial class UserDefinedFunctions
{ [Microsoft.SqlServer.Server.SqlFunction]
public static SqlString UpperFirst(string value) {
return new SqlString(CultureInfo.CurrentCulture.TextInfo.ToTitleCase(value));
}};
sp_configure 'clr enable',1GORECONFIGUREGOSELECT dbo.UpperFirst('TSQL upper case first letter of each word')GO
Posted in:
.Net ,
Beginner ,
TSQL Tags:
3f2f2e00-057b-4ade-8943-a7e629fa9c8f|0|.0
We had a failure today and it brought up a couple of interesting points. I'll put this into my beginner category, but you might take something away from this post regardless. Run the following script:
USE tempdb;GODROP TABLE tbl_TestGOCREATE TABLE tbl_Test (ID int IDENTITY(1,1) NOT NULL PRIMARY KEY ,charCol char(2) )GO
INSERT INTO tbl_Test (charCol) VALUES (1)GOINSERT INTO tbl_Test (charCol) VALUES (2)GOINSERT INTO tbl_Test (charCol) VALUES ('3a')GO
This is all fine - love row constructors - but go ahead and try this one:
INSERT INTO tbl_Test (charCol) VALUES (1), (2), ('3a')GO
Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '3a' to data type int.
How about this one?
INSERT INTO tbl_Test (charCol) VALUES ('a')GOINSERT INTO tbl_Test (charCol) VALUES ('b')GOINSERT INTO tbl_Test (charCol) VALUES (3)GOINSERT INTO tbl_Test (charCol) VALUES ('a'), ('b'), (3)GO
Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'a' to data type int.
I wouldn't swear to it, but I'm guess that what is happening here is that data type precedence is causing this failure. Despite the order of INSERT and types, the higher precedence integer causes this to fail when inserting via a row constructor.
Which brings up another point - check this one out:
USE tempdb;GODROP TABLE tbl_TestGOCREATE TABLE tbl_Test (ID int IDENTITY(1,1) NOT NULL PRIMARY KEY ,charCol char(2) )GOINSERT INTO tbl_Test (charCol) VALUES (11)GOINSERT INTO tbl_Test (charCol) VALUES (22)GOINSERT INTO tbl_Test (charCol) VALUES ('AA')GO
SELECT MAX(ID) as charColFROM tbl_TestWHERE charCol=11GROUP BY ID
Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'AA' to data type int.
In this case, we needed to use test the data type in the WHERE clause beforehand, or checking the type before inserting into the table. Also, it's good practice to not let the implict conversion from int to char kick in; if you are inserting an integer into a char field, consider putting the tick marks '' around the integer, depening on the action that you want the process to take downstream.
Lee
----------------------
http://msdn.microsoft.com/en-us/library/ms174335.aspx
Posted in:
Beginner ,
TSQL Tags:
43e39ba3-bdea-4850-bb0c-bb2df0c73600|0|.0
I’ve added a beginner category to my blog, so here’s the first post. Hope it helps someone out along the way…
SELECT INTO moves a certain number of things to the newly created table from the source table; some things it doesn’t move, and these are well known. NULL value specifications for a given column get moved, but what if you don’t want the NULL? The following example illustrates:
USE _;
GO
IF OBJECT_ID('testTable') IS NOT NULL
DROP TABLE testTable
GO
SELECT DISTINCT StateProvinceCode
INTO testTable
FROM AdventureWorksDW2008R2.dbo.DimGeography
GO
ALTER TABLE testTable
ADD CONSTRAINT pk_testTable PRIMARY KEY (StateProvinceCode)
GO
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'testTable'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How about ISNULL or COALESCE?
SELECT DISTINCT ISNULL(StateProvinceCode,'î') AS StateProvinceCode
INTO testTable
FROM AdventureWorksDW2008R2.dbo.DimGeography
WHERE StateProvinceCode!='î'
GO
Add some weird character or combination of characters that fall outside of the set of values from your SELECT list so that you know you won’t miss a record for your new column.
Thanks,
Lee
-----------------------------
Posted in:
Beginner Tags:
d572c86a-121a-4b0d-8aa6-2174b9a9d2b3|0|.0