I always like going to the new product launches…SQL Server 2000 was a big one, as well as SQL Server 2005. The latter was a the Dallas Convention Center if I remember correctly, and had a huge turnout. Always enjoy getting the goodies (pens, paper, hats, etc.) and meeting up with old friends. The list of cities: Atlanta March 5: Irvine March 7: Denver March 13: Columbus March 14: Jacksonville March 15: Chicago March 20: Dallas March 21: Philadelphia March 22: New York March 28: Boston March 28: Minneapolis April 5: San Francisco Here’s the skinny on the get-together for the latest version, SQL Server 2012, so check it out and get registered. I’ll be going to the one in Dallas over at LC1, so looking forward to seeing you there. Thanks, Lee --------------------------- SQLServer 2012 Special Ops Tour - Dallas, TX Tuesday, March 20, 2012 1:30 PM - 5:15 PM (Central Time) http://specialops.sqlpass.org/#fbid=b8Wx1gVVlvQ?prod=SQL&type=EV http://specialops.sqlpass.org/FreeRegistration.aspx#fbid=b8Wx1gVVlvQ
Posted in:
SQL Server 2012 Tags:
711af63a-7e10-4c63-b645-5d1a27f34ed3|0|.0
I’m seeing more and more information on SQL Server 2012 coming, including the Virtual Launch Event which is on March 7, 2012. Check out this MSDN post for the latest stuff. And, if you haven’t downloaded it yet, I suggest that you go grab it and start dorking with it. It’s pretty nice! I suggest as first priority…you need to go out to the virtual labs and do some. They have improved these dramatically over the last batch, and they actually work with no errors now. Great environment, clean, and fast. Obviously made this a priority for us… Lee ----------------------- http://blogs.msdn.com/b/mssmallbiz/archive/2012/01/24/sql-server-2012-virtual-launch-event-information-resources-and-much-more.aspx
Posted in:
SQL Server 2012 Tags:
b0253037-b9cb-48f7-a7a8-b03134272fc3|0|.0
SQL Server 2012 has given us a new team and a new blog – The SQL Server AlwaysOn Team Blog – to support one of the coolest features (I believe) in SQL Server 2012. If you haven’t read about AlwaysOn, or haven’t had a chance to do the kick ass SQL Server 2012 AlwaysOn Availability Groups (SQL 142) Hands on Lab, you really need to do both because it’s going to be a really strong new addition to our product and I really like it.
Check out their new blog and all of the current posts (like 16 of them so far for Jan 2012) and get started on High Availability. You might also want to read Brent Ozar’s most excellent effort on “AlwaysOn”.
Thanks for reading, Lee
----------------
http://blogs.msdn.com/b/sqlalwayson/archive/2012/01.aspx
SQL Server 2012: AlwaysOn Availability Groups (SQL 142)
http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/
12c54ee6-e040-4bdd-a1b5-b688a22e3b8a|0|.0
Sequences in SQL Server 2012 are new, but sequences have been an ANSI standard for a long time. I first worked with sequences in Oracle 8.0 back in 2001. Applications back then in SQL Server 6.5 and 7 oftentimes created a sequence table; each time you wanted an ‘identity’, you went to a homespun sequence table and added the value 1, for instance, to the current value, and then inserted. Oftentimes you had to do it in a certain way so that another transaction didn’t insert the same sequence number, or attempt to any way. So for instance, you fetch a sequence from a table, insert the value, and then go back and update the sequence table with the value that was inserted. Not terribly efficient or stealthy-sounding, huh? So enter Identity and all of its wonder and problems.
And, for SQL Server 2012, back to the future with Sequences. I’ve been dorking with them this morning, please follow along. I’ve created a database called Company:
CREATE SCHEMA Orders;
GO
DROP TABLE Orders.TblOrders;
GO
CREATE TABLE Orders.TblOrders
(PKOrderID int PRIMARY KEY
,FKCustomerID varchar(40) NOT NULL
,OrderQuantity int
,OrderDate datetime2 NOT NULL DEFAULT
GETDATE()
);
GO
CREATE SEQUENCE Orders.SqnceTblOrders
START WITH 1
INCREMENT
BY 1;
GO
I’ve created a schema, table, and a sequence with the above. Check Books Online for all of the sequence options. The essentials for making this thing work appear to be START WITH and INCREMENT BY.
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity )
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 345);
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 111);
GO
SELECT * FROM Orders.TblOrders;
GO
First thing that I thought of was…remove the NEXT VALUE and simply throw it in a declare:
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
GO
SELECT * FROM Orders.TblOrders; GO
One thing that I don’t like is that sequence works like IDENTITY for values in a transaction; if it’s rolled back, when you insert again, it picks back up with the next value. Nope, don’t like that behavior. Run the following:
BEGIN TRANSACTION
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
ROLLBACK
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (NEXT VALUE FOR Orders.SqnceTblOrders, 1, 133);
SELECT * FROM Orders.TblOrders;
GO
If, however, you implement the above declare you could possibly save the value if some sort of retry logic is maintained:
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders
BEGIN TRANSACTION trnOrderInsert
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
SELECT * FROM Orders.TblOrders;
ROLLBACK TRAN trnOrderInsert
INSERT INTO
Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity)
VALUES (@seqValue, 1, 111);
SELECT * FROM Orders.TblOrders;
GO
How about more than one on a table? Looks like it works…
ALTER TABLE Orders.TblOrders
ADD RowNumber INT;
CREATE SEQUENCE Orders.SqnceRowNumber
START WITH 1
INCREMENT BY 1;
GO
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders;
DECLARE @RowNumber int = NEXT VALUE FOR Orders.SqnceRowNumber;
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity, RowNumber)
VALUES (@seqValue, 1, 111, @RowNumber);
GO
SELECT * FROM Orders.TblOrders;
GO
Maybe as a constraint:
CREATE SEQUENCE Orders.SqnceSequence3
START WITH 1
INCREMENT
BY 1;
GO
ALTER TABLE Orders.TblOrders
ADD SequenceWithConstraint INT DEFAULT NEXT VALUE FOR Orders.SqnceSequence3;
GO
Now insert some data…looks like as a default constraint might be a cool way to throw a sequence in, but again this will lose a bit of control if you are going to use it with multiple tables via a variable as shown above.
DECLARE @seqValue int = NEXT VALUE FOR Orders.SqnceTblOrders;
DECLARE @RowNumber int = NEXT VALUE FOR Orders.SqnceRowNumber;
INSERT Orders.TblOrders (PKOrderID, FKCustomerID, OrderQuantity, RowNumber)
VALUES (@seqValue, 1, 111, @RowNumber);
GO
SELECT * FROM Orders.TblOrders;
GO
One thing to consider maybe…it appears that there is no checking or pairing for a sequence – Books online states the following:
Sequences, unlike identity columns, are not associated with specific tables.
This means that outside of an application you better darn sure be careful which sequence you’re calling, because you can call whichever sequence that you may have previously created:
DROP TABLE #Test
GO
CREATE TABLE #Test
(ID int)
GO
INSERT INTO #Test (ID) VALUES (NEXT VALUE FOR Orders.SqnceTblOrders);
GO
SELECT * FROM #Test;
GO
There’s a big section in BOL with caching. Maybe we’ll look at that one next week.
Thanks for reading,
Lee
-----------------------------
Sequences and IDENTITY…have to keep giving Joe Celko something to bitch about, right?
Posted in:
SQL Server 2012 ,
TSQL Tags:
fc9b15d0-4063-4d89-aaec-13ea74539d11|0|.0
Check out this link for a bunch of SQL Server 2012 Hands-on labs. Great stuff! Now I have something to do this weekend :) Lee ------------------ SQL Server Virtual Labs http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Posted in:
SQL Server 2012 Tags:
f982424d-b0da-479c-b68a-15f97d20e5e6|1|5.0
Column Store indexes are particularly interesting in SQL Server 2012, so I thought I would take a look at them. The first thing that you notice in BOL is the following: They’re for read-only tables at this time…this may change in the future. If you insert or change the data, you get the following: Msg 35330, Level 15, State 1, Line 2 INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete. They build a view on top of the existing table The view fetches only those columns needed Performance is 10x to 100x. (Really 100x? Wow). There are a couple of things that I want to check out with these indexes, so I’ll look at one here and save the others for later. I want to find out if column store indexes on a bit field actually helps since in BOL is states that bit columns are legit columns to index with Column Store. Indexing a bit column may or may not help with a traditional nonclustered index as you might know, depending on the particular situation and a host of other variables, so let’s see if one of these bad boys helps in my example. I’m expanding here on a vid that I saw today over on Channel 9; check it out for yourself to see where I’m getting my ideas. An interesting point in BOL: The columnstore index does not support SEEK If the query is expected to return a small fraction of the rows, the optimizer is unlikely to select the columnstore index. For example: needle-in-the-haystack type queries. If the table hint FORCESEEK is used, the optimizer will not consider the columnstore index. So I expect here that I will probably get a seek with a traditional nonclustered index, but a scan with column store. Mine here is not of the ‘needle-in-the-haystack’ variety. Run the following to get a table with 150 million rows: BEGIN TRY
DROP TABLE BitTable
END TRY
BEGIN CATCH
END CATCH
SELECT CAST(CASE WHEN x.id%2=0 THEN 1 ELSE 0 END AS bit) AS ID
,CASE WHEN x.id%2=1 then 'b'
WHEN x.id%3=1 then 'c'
ELSE 'd'
END AS charVal
INTO BitTable
FROM
(SELECT TOP 150000000 a.id from sys.syscolumns a
,sys.syscolumns b
,sys.syscolumns c) AS x
.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; }
Create the indexes. This will take a few minutes.
CREATE NONCLUSTERED INDEX IDX_x ON BitTable (ID)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX IDX_csi ON BitTable (ID);
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; }
Run the following:
SET STATISTICS TIME ON
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
.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; }
Make sure and run both DBCC statements before running each query. Now let’s run the query, first with the traditional NC index. The execution plan does show an index seek, btw.
SELECT COUNT(1)
FROM BitTable WITH (INDEX(IDX_x))
WHERE ID=1
.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; }
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 22 ms.
(1 row(s) affected)
Table 'BitTable'. Scan count 3, logical reads 151900, physical reads 0, read-ahead reads 151732, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5437 ms, elapsed time = 20817 ms.
Now let’s check out column store:
SELECT COUNT(1)
FROM BitTable WITH (INDEX(idx_csi))
WHERE ID=1
.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; }
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 23 ms.
(1 row(s) affected)
Table 'BitTable'. Scan count 2, logical reads 728, physical reads 1, read-ahead reads 290, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 7829 ms, elapsed time = 5983 ms.
Some interesting things here. Quite a few less physical/logical reads as well as 1 more scan count in the nonclustered index (along with ORDERED FORWARD in the showplan text). The traditional nc index is definitely doing some more work, even though the CPU time is less. CPU time for both shown in task mgr:
I note that the column store index hit the CPUs much harder than the traditional NCI; both are in this view with the column store indicated by the large spike. CS pegged them at 100% while the traditional NCI hit them at roughly 10%.
Just for fun, let’s look at the query without an index:
-----------
81560849
(1 row(s) affected)
Table 'BitTable'. Scan count 3, logical reads 278294, physical reads 0, read-ahead reads 278294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 14500 ms, elapsed time = 24880 ms.
No index is almost as fast as the large NC index on the table.
Thanks,
Lee
------------------------
http://channel9.msdn.com/posts/SQLShorts-SQL-2012-Apollo-Column-Store
Posted in:
SQL Server 2012 Tags:
65399bf3-29bc-498f-b055-406cdc6a7a8b|0|.0
Just perusing the MSDN blogs looking for some SQL Server 2012 blogs, and lo and behold, I can’t really find any over the past week or two. I guess the team is leaving the demos of the new syntax and other features up to bloggers or others, no idea. I did manage to find one entitled Microsoft SQL Server 2012 - 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012 on the SSIS Team Blog. Here’s the skinny: Summary: Microsoft SQL Server 2012 Integration Services (SSIS) provides significant improvements in both the developer and administration experience. This article provides tips that can help to make the upgrade to Microsoft SQL Server 2012 Integration Services successful. The tips address editing package configurations and specifically connection strings, converting configurations to parameters, converting packages to the project deployment model, updating Execute Package tasks to use project references and parameterizing the PackageName property. Download the white paper and check out the two wizards that are available to ensure a smooth upgrade of your packages and project as you move it to SQL Server 2012. Within the doc, there are several other links that you also might want to read. Lee ----------------------------- http://blogs.msdn.com/b/mattm/archive/2011/11/30/5-tips-for-a-smooth-ssis-upgrade-to-sql-server-2012.aspx http://msdn.microsoft.com/en-us/library/hh667275(d=lightweight).aspx
Posted in:
SQL Server 2012 Tags:
fab9ce5b-bf9e-494b-9f39-a8dfd9ff5d93|0|.0
In the Installation Rules of SQL Server 2012 RC0 setup, you may get the following: It says: This computer does not have the Microsoft .Net Framerwork 3.5 Service Pack 1 installed. If the operating system is Windows Vista or Windows Server 2008, download and install Microsoft .Net Framework 3.5 SP1 from … No need to do this if running Windows Server 2008 R2 sp1; simply go to Server Manager >> Features >> Add Features, and click on the .Net Framework 3.5.1 Features link. You also get this: Click through without adding anything else, and continue the install. No need to back out and start over again. Now hit the Re-run button, and your install is once again blingin! Thanks, Lee -----------------------
Posted in:
SQL Server 2012 Tags:
0d4cc144-b7f3-4c7d-9806-a02bff04b2f6|0|.0
Roger Doherty posted a blog “Looking for a Killer SQL Server 2012 demo?” last Wednesday that included info on the PASS keynote, semantic search and mysemanticsearch.codeplex.com and some other cool stuff that looks pretty good. The codeplex tool: allows a user to upload Word documents into a SQL Server 2012 table, then visualize the semantic content of the documents in it using tag clouds that are automatically built by semantic search. The user can then find similar documents by comparing their tag clouds. Pretty interesting for collecting disparate data via an app and then analyzing it. Other links include FILESTREAM, FileTables, Full-text search, and semantic search. This is all really neat stuff and sort of related; a scenario might be that you can continue storing data in SQL Server as blobs, but now you have a FileTable to store the hierarchical relationship in SQL Server and interact with the Windows file system, and with semantic search, actually use TSQL in an expanded way beyond full-text search to query documents stored in SQL Server. Lee ------------------------ Why is there a Lillian Vernon add on my blog? http://blogs.msdn.com/b/rdoherty/archive/2011/10/27/looking-for-a-killer-sql-server-2012-demo.aspx
Posted in:
SQL Server 2012 Tags:
429a68b5-b376-4ff0-a30b-bda535fb0f9c|0|.0