tag:blogger.com,1999:blog-369141642024-03-13T03:06:47.463-07:00Persistence of VisionRob Garrison's writings on Data Architecture: Hadoop, SQL Server, performance, design, testing, best-practices, and automation.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.comBlogger84125tag:blogger.com,1999:blog-36914164.post-25334813230388787602019-03-18T17:50:00.001-07:002019-03-18T17:50:24.152-07:00<h2>
Showing Seconds in Excel Date/Time Columns</h2>
This is a very simple solution, but clearly it has been a <i>long</i> time since I posted.<br />
<br />
I was unable to post during the tenure at Nike. They are <i>very</i> obsessive about their brand, and as an employee, I was not allowed to post on my own blog.<br />
<br />
As of today, I have been at Dish Network for 18 months. My new role is as a Senior Data Architect in the Big Data team. I will be supporting both the Big Data and Media Sales teams.<br />
<br />
In this role, I am <i>finally</i> back to working directly with databases, this time Hadoop. Hadoop is completely new to me, so it's fun to learn.<br />
<br />
<h3>
Solution</h3>
Now to the solution, which is related to Excel, not Hadoop directly.<br />
<br />
My Hadoop (Hive) query is pulling date/time columns with this:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SELECT ... from_unixtime(start_epoch) ...</span><br />
<br />
It returns date/time values like this: "2018-12-14 17:05<span style="background-color: lime;">:15</span>". But when I cut-and-paste that output into Excel, it drops the seconds from date/time values.<br />
<br />
Here's how to show the seconds values in Excel.<br />
<br />
<ol>
<li>Select the data/time column</li>
<li>Click the drop-down above "Number" in the ribbon (it defaults to "General")</li>
<li>Select "More Number Formats..."</li>
<li>Select "Custom"</li>
<li>Under "Type:", enter "yyyy-mm-dd hh:mm:ss"</li>
<li>Click OK</li>
</ol>
<br />
<br />
The date/time values now show like this: “2018-12-14 17:05<span style="background-color: lime;">:15</span>”, exactly matching the output in Hive.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-54442120940829866972013-11-19T16:01:00.002-08:002013-11-19T16:01:42.075-08:00MongoDB and CAPJust read this great post, <a href="http://aphyr.com/posts/284-call-me-maybe-mongodb" target="_blank">Call me maybe: MongoDB</a> and the wrap-up post, <a href="http://aphyr.com/posts/286-call-me-maybe-final-thoughts" target="_blank">Call me maybe: final thoughts</a>. They're part of a series called <a href="http://aphyr.com/tags/jepsen" target="_blank">Jepsen</a> on behavior of various NoSQL databases in failure scenarios.<br />
<br />
Very geeky but very interesting.<br />
<br />Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-16805491604031576062013-11-19T10:46:00.003-08:002013-11-19T10:46:31.164-08:00Completed MongoDB M102As I <a href="http://persistencevision.blogspot.com/2013/10/mongodb-training.html" target="_blank">mentioned earlier</a>, I signed up for "<a href="https://education.mongodb.com/courses/" target="_blank">M102: MongoDB for DBAs</a>". Great class.<br />
<br />
I just completed it yesterday and got 100% on everything, homework and final.<br />
<br />
If you're interested in learning about MongoDB, this is a great way to get a broad exposure in a limited amount of time. Because it includes weekly lectures and homework, you <i>can't</i> procrastinate like you can with that technical book on your desk that you planned to read through.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-42418860775276334552013-11-19T08:51:00.001-08:002013-11-19T08:51:30.762-08:00Speaking at Data Modeling Zone 2014My boss, Ryan Smith, and I will co-present on modeling in NoSQL at <a href="http://www.datamodelingzone.com/index.aspx" target="_blank">Data Modeling Zone 2014</a> here in Portland.<br />
<br />
It's not until October of next year, so we don't have things nailed down yet, but it will be fun to work with Ryan on this.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-78832087146410512182013-11-19T08:48:00.001-08:002013-11-19T08:48:29.128-08:00RESTful Service Best PracticesI was recently looking for reference material to back up my preference for storing date/time values in MongoDB in UTC using MongoDB's ISODate type. I found this great reference published at amazonaws.com:<br />
<br />
<a href="https://s3.amazonaws.com/tfpearsonecollege/bestpractices/RESTful+Best+Practices.pdf" target="_blank"><b>RESTful Service Best Practices</b></a><br />
Recommendations for Creating Web Services<br />
<br />
It gives a solid recommendation for RESTful services:<br />
<blockquote class="tr_bq">
"... always use the same format, including the time portion (along with timezone information) in the string. ISO 8601 time point format is a good solution, using the fully-enhanced format that includes hours, minutes, seconds and a decimal fraction of seconds (e.g. yyyy-MM-dd'T'HH:mm:ss.SSS'Z')."</blockquote>
It doesn't directly address storage directly, but it's such a great resource, I wanted to share it.<br />
<br />Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-60950831997842366462013-10-30T08:01:00.000-07:002013-10-30T08:01:09.457-07:00Exploring SQL Server 2012's sys.dm_exec_describe_first_result_set_for_object()SQL Server 2012 introduces sys.dm_exec_describe_first_result_set_for_object(). The official documentation is <a href="http://technet.microsoft.com/en-us/library/ff878236%28SQL.110%29.aspx" target="_blank">here</a>.<br />
<br />
Let's work through some examples to discover what this new function delivers.<br />
<br />
First, build a table that will be used to illustrate the function:<br />
<blockquote>
<code style="font-size: 12px;"><span style="color: blue;">CREATE TABLE </span><span style="color: black;">dbo.TestTable </span><span style="color: grey;">(<br /> </span><span style="color: black;">ColID </span><span style="color: blue;">INT </span><span style="color: #434343;">IDENTITY</span><span style="color: grey;">,<br /> </span><span style="color: black;">ColStr </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">10</span><span style="color: grey;">) NOT NULL,<br /> </span><span style="color: black;">ColMod </span><span style="color: blue;">AS </span><span style="color: black;">ColID </span><span style="color: grey;">% </span><span style="color: black;">10</span><span style="color: grey;">,<br /> </span><span style="color: black;">ColDef bit </span><span style="color: grey;">NOT NULL </span><span style="color: blue;">DEFAULT </span><span style="color: black;">0</span><span style="color: grey;">,<br /> </span><span style="color: black;">ColCalc0 </span><span style="color: blue;">INT </span><span style="color: grey;">NULL,<br /> </span><span style="color: black;">ColDec </span><span style="color: blue;">DECIMAL</span><span style="color: grey;">(</span><span style="color: black;">3</span><span style="color: grey;">,</span><span style="color: black;">2</span><span style="color: grey;">) NULL,<br /> </span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">UIX_Test1_ColStr </span><span style="color: blue;">UNIQUE </span><span style="color: grey;">(</span><span style="color: black;">ColStr</span><span style="color: grey;">)<br />);</span></code></blockquote>
<br />
Then build the simplest possible stored procedure that references that table:<br />
<blockquote>
<code style="font-size: 12px;"><span style="color: blue;">CREATE PROCEDURE </span><span style="color: black;">dbo.SimpleProc</span><span style="color: blue;"> AS<br />SELECT </span><span style="color: black;">ColID </span><span style="color: blue;">FROM </span><span style="color: black;">dbo.TestTable</span><span style="color: grey;">;</span></code></blockquote>
<br />
Now call the function:<br />
<blockquote>
<code style="font-size: 12px;"><span style="color: blue;">SELECT </span><span style="color: grey;">*</span><span style="color: blue;"> </span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">FROM </span><span style="color: black;">sys.dm_exec_describe_first_result_set_for_object </span><span style="color: grey;">(<br /> </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'dbo.SimpleProc'</span><span style="color: grey;">),<br /> </span><span style="color: black;">0</span></code><code style="font-size: 12px;"><span style="color: grey;"><br />);</span></code></blockquote>
<br />
Remember that this stored procedures selects only one column. There are many columns in the result set. Here are some of the more interesting ones:<br />
<blockquote style="color: black;">
<code style="font-size: 12px;">is_hidden: 0<br />column_ordinal: 1<br />is_nullable: 0<br />system_type_name: INT<br />is_identity_column: 1<br />is_updateable: 0<br />is_computed_column: 0<br />ordinal_in_order_by_list: NULL<br />order_by_is_descending: NULL</code></blockquote>
asdf<br />
<br />
<br />
<br />
<br />
<br />
<br />Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-4011644186128153632013-10-30T07:56:00.002-07:002013-10-30T07:56:57.479-07:00MongoDB TrainingI am in the middle of taking a free on-line class: "<a href="https://education.mongodb.com/courses/" target="_blank">M102: MongoDB for DBAs</a>".<br />
<br />
Very interesting and educational so far. Also a bit frustrating. As I don't have a background in JavaScript, there are certain parts of the class that I struggle with.<br />
<br />
In general though, this is a <i>great</i> class and well worth your time if you are interested in MongoDB.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-13934651888644904132013-08-07T15:53:00.000-07:002013-10-30T07:57:15.138-07:00Just Do It!<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-JoReH1ge9VU/UgLPn-NObFI/AAAAAAAAFXQ/c9UbD5mkebk/s1600/NikeLogo2.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="180" src="http://1.bp.blogspot.com/-JoReH1ge9VU/UgLPn-NObFI/AAAAAAAAFXQ/c9UbD5mkebk/s320/NikeLogo2.jpg" width="320" /></a></div>
<br />
My last day at Fiserv (second round) was Thursday, August 1st. On Monday, I started as an Expert Data Architect at Nike.<br />
<br />
I am <i>so</i> excited about this change and really look forward to what I can learn and contribute at Nike.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-7914569902685809562013-06-28T14:13:00.002-07:002013-10-30T07:57:33.317-07:00New Simple-Talk Article on HekatonMy article on Hekaton:<br />
<br />
<a href="https://www.simple-talk.com/content/article.aspx?article=1825" target="_blank"><b>Exploring In-memory OLTP Engine (Hekaton) in SQL Server 2014 CTP1</b></a><br />
<br />
was published on Tuesday. This follows very closely the release of CTP1 of SQL Server 2014.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-1645867574455582072013-06-25T10:03:00.002-07:002013-10-30T07:57:50.340-07:00SQL Server 2014 CTP1CTP1 of SQL Server 2014 is now available! Go <a href="http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx" target="_blank">download it</a> and start using it.<br />
<br />
My first SQL 2014 article will be published at simple-talk soon. I will update this post with a link once the article is on-line.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-61110954870893948752013-03-26T10:53:00.000-07:002013-10-30T07:58:09.086-07:00My New Role at Fiserv: Patterns and Practices ManagerIn addition to the Data Architect role that I will continue to play, I am now the Patterns and Practices Manager for the Digital Channels Enterprise Architecture team.<br />
<br />
Here is the text right out of the March 12th e-mail that announced the change:<br />
<blockquote class="tr_bq">
Rob will take on responsibility for managing our processes and best practices within the Group. He will continue to lead our Database design discipline, but will also take on management for the Corillian [Professional Services] Architects. Michael Hallabrin, Tim Sarna, and Rocco Martin will report in to Rob as part of the CO-L Solutions Architecture team.</blockquote>
I am really excited about this new role. Michael, Tim, and Rocco are a bunch of <i>really</i> smart guys, and I am enjoying working more closely with them.<br />
<br />
RobRob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-84252298654159344902013-03-26T10:43:00.003-07:002013-10-30T07:59:02.550-07:00Presenting on SQL Server Change Data Capture in EugeneI will be speaking to the <a href="http://eugene.sqlpass.org/" target="_blank">Eugene Area SQL Server User Group</a> on CDC.<br />
<br />
<h3>
<b>Topic: Deconstructing SQL Server's Change Data Capture</b></h3>
<br />
SQL Server's Change Data Capture feature was introduced with SQL 2008, but how much do you know about how it works? After attending this session, you will understand the various parts involved in CDC, know how to set it up, and know the relative performance effect.<br />
<br />
Our focus here is not just showing how CDC works on the surface (there are many articles that cover that), but rather to see what we can find out about the various moving parts within SQL Server that actually make CDC function. Understanding the internals will help in an overall understanding of CDC.<br />
<br />
<h3>
<b>Location/Time</b></h3>
<br />
Peace Health, 123 International Way<br />
Springfield OR<br />
<br />
April 9th at 11:30amRob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-91853499587081274272013-01-28T11:53:00.002-08:002013-01-28T11:54:26.213-08:00A Creative Use of Computed ColumnsI love to see a creative solution to a technical problem.<br />
<br />
<a href="http://blogs.msdn.com/b/psssql/archive/2013/01/22/how-can-i-get-that-user-out-of-my-table-quickly.aspx" target="_blank"><b>How can I get that user out of my table quickly</b></a> by JackLi<br />
<br />
Great creative solution using indexed persisted computed columns.<br />
<br />
The beauty of indexed persisted computed columns is that, yes, you have to pay the cost of doing the computation, but you only pay that cost at the point where you INSERT or UPDATE that particular column. If the data is mostly static as it is in this case, the one-time cost is very small.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-41881335006963940982013-01-16T13:22:00.000-08:002013-01-28T11:55:07.841-08:00You Can’t Sacrifice Partition ToleranceJust finished reading this excellent article by Coda Hale:<br />
<div>
<br /></div>
<div>
<b><a href="http://codahale.com/you-cant-sacrifice-partition-tolerance/" target="_blank">You Can’t Sacrifice Partition Tolerance</a></b></div>
<div>
<br /></div>
<div>
I thought the bit about yield versus uptime was very interesting, as was the bit about harvest (harvest = data available/complete data). Historically, [the corporate] we have built systems that sacrifice yield in the event of a failure. I like the challenge/opportunity of building a system that reduces harvest but maintains yield.</div>
Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-21759392400343221772012-12-20T09:44:00.000-08:002013-10-30T07:59:23.245-07:00sys.dm_db_stats_properties() - Initial Results on SQL 2008 R2 SP2My <a href="http://persistencevision.blogspot.com/2012/12/sysdmdbstatsproperties-initial-results.html" target="_blank">first sys.dm_db_stats_properties() post</a> showed results for SQL Server 2012 SP1. Here, I will show how the results for filtered indexes on 2008 R2 SP2 are different.<br />
<br />
The test script is exactly the same as before, just running on a different version.<br />
<br />
<h3>
MSDN Docs</h3>
The 2008 R2 version of the docs is <a href="http://msdn.microsoft.com/en-us/library/jj553546(v=sql.105).aspx" target="_blank">here</a>. Because the results were different, I pulled the text for both the 2012 document and the 2008 R2 document and compared them. There are no substantive differences.<br />
<br />
Here are the only differences:<br />
<blockquote class="tr_bq">
<b>2008 R2 SP2</b>"Requires that the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."</blockquote>
<blockquote class="tr_bq">
<b>2012 SP1 adds the <span style="background-color: yellow;">highlighted</span> section</b>"Requires that the user <span style="background-color: yellow;">has select permissions on statistics columns or the user</span> owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."</blockquote>
<h3>
Results</h3>
<div>
<pre style="font-size: 12px;"><span style="color: black;">Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Beginning execution loop
Batch execution completed 10000 times.
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-20 09:10:09 10000 10000 132 10000 0
IX_Test1_Deleted NULL 2012-12-20 09:10:09 10000 10000 1 10000 0
IX_Test1_StatusId NULL 2012-12-20 09:10:09 10000 10000 1 10000 0
PK_Test1_ColId NULL 2012-12-20 09:10:09 10000 10000 3 10000 0
Update Count: 5000
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-20 09:10:09 10000 10000 132 10000 0
IX_Test1_Deleted NULL 2012-12-20 09:10:09 10000 10000 1 10000 5000
IX_Test1_StatusId NULL 2012-12-20 09:10:09 10000 10000 1 10000 5000
PK_Test1_ColId NULL 2012-12-20 09:10:09 10000 10000 3 10000 0</span></pre>
</div>
<h3>
Observations</h3>
<b>Filtered Index Results</b><br />
Filtered indexes are excluded from these results where they return NULLs in 2012.<br />
<br />
The difference appears to be in the fact that the function returns a row in 2012 for a filtered index and does not return a row in 2008 R2 for a filtered index.<br />
<br />
<br />Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-1959715193371736282012-12-19T16:46:00.000-08:002013-10-30T07:59:32.663-07:00sys.dm_db_stats_properties() - Initial Results on SQL 2012 SP1The SQL Server team just released a new feature, the DMF sys.dm_db_stats_properties(). This new feature is available in 2008 R2 SP2 and 2012 SP1.<br />
<br />
These tests were run on SQL Server <b>2012 SP1</b> and will illustrate what the function returns in the normal case.<br />
<br />
First, some references:<br />
<ul>
<li><a href="http://msdn.microsoft.com/en-us/library/jj553546.aspx" target="_blank">sys.dm_db_stats_properties</a> at MSDN</li>
<li><a href="http://www.jasonstrate.com/2012/10/what-happened-to-rowmodctr-from-sysindexes/" target="_blank">What Happened to ROWMODCTR from SYSINDEXES?</a> by Jason Strate</li>
<li><a href="http://www.sqlskills.com/blogs/erin/post/new-statistics-dmf-in-sql-server-2008r2-sp2.aspx" target="_blank"> New Statistics DMF in SQL Server 2008R2 SP2</a> by Erin Stellato of SQLskills</li>
</ul>
<div>
The test code below will</div>
<div>
<ol>
<li>create a new table with multiple indexes.</li>
<li>insert 10,000 rows, then UPDATE STATISTICS.</li>
<li>call sys.dm_db_stats_properties().</li>
<li>update the StatusId and Deleted columns of 50% of the rows.</li>
<li>call sys.dm_db_stats_properties().</li>
</ol>
</div>
<div>
<h3>
Test Code</h3>
</div>
<div>
<pre style="font-size: 11px;"><span style="color: black;">
</span><span style="color: blue;">USE </span><span style="color: black;">tempdb</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: blue;">PRINT </span><span style="color: #434343;">@@VERSION</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: blue;">IF </span><span style="color: grey;">EXISTS (</span><span style="color: blue;">SELECT </span><span style="color: grey;">* </span><span style="color: blue;">FROM </span><span style="color: black;">sys.tables </span><span style="color: blue;">WHERE </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'dbo.Test1'</span><span style="color: grey;">) </span><span style="color: blue;">IS </span><span style="color: grey;">NOT NULL)
</span><span style="color: blue;">DROP TABLE </span><span style="color: black;">dbo.Test1</span><span style="color: grey;">;</span><span style="color: blue;">CREATE TABLE </span><span style="color: black;">dbo.Test1 </span><span style="color: grey;">(
</span><span style="color: black;">ColId </span><span style="color: blue;">INT </span><span style="color: #434343;">IDENTITY</span><span style="color: grey;">,
</span><span style="color: black;">ColGuid </span><span style="color: blue;">UNIQUEIDENTIFIER </span><span style="color: grey;">NOT NULL </span><span style="color: blue;">DEFAULT </span><span style="color: magenta;">NEWID</span><span style="color: grey;">(),
</span><span style="color: black;">StatusId tinyint </span><span style="color: grey;">NOT NULL </span><span style="color: blue;">DEFAULT </span><span style="color: black;">1</span><span style="color: grey;">,
</span><span style="color: black;">Deleted bit </span><span style="color: grey;">NOT NULL </span><span style="color: blue;">DEFAULT </span><span style="color: black;">0</span><span style="color: grey;">,
</span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">PK_Test1_ColId </span><span style="color: blue;">PRIMARY KEY CLUSTERED </span><span style="color: grey;">(</span><span style="color: black;">ColId</span><span style="color: grey;">),
</span><span style="color: blue;">CONSTRAINT </span><span style="color: black;">AK_Test1_ColGuid </span><span style="color: blue;">UNIQUE </span><span style="color: grey;">(</span><span style="color: black;">ColGuid</span><span style="color: grey;">)
);</span><span style="color: black;">GO</span><span style="color: blue;">CREATE NONCLUSTERED INDEX </span><span style="color: black;">IX_Test1_ColId_WhereDeletedEq1 </span><span style="color: blue;">ON </span><span style="color: black;">dbo.Test1 </span><span style="color: grey;">(
</span><span style="color: black;">ColId</span><span style="color: grey;">) </span><span style="color: blue;">WHERE </span><span style="color: black;">Deleted </span><span style="color: blue;">= </span><span style="color: black;">1</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: blue;">CREATE NONCLUSTERED INDEX </span><span style="color: black;">IX_Test1_StatusId_WhereDeletedEq1 </span><span style="color: blue;">ON </span><span style="color: black;">dbo.Test1 </span><span style="color: grey;">(
</span><span style="color: black;">StatusId</span><span style="color: grey;">) </span><span style="color: blue;">WHERE </span><span style="color: black;">Deleted </span><span style="color: blue;">= </span><span style="color: black;">1</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: blue;">CREATE NONCLUSTERED INDEX </span><span style="color: black;">IX_Test1_Deleted </span><span style="color: blue;">ON </span><span style="color: black;">dbo.Test1 </span><span style="color: grey;">(
</span><span style="color: black;">Deleted</span><span style="color: grey;">);</span><span style="color: black;">GO</span><span style="color: blue;">CREATE NONCLUSTERED INDEX </span><span style="color: black;">IX_Test1_StatusId </span><span style="color: blue;">ON </span><span style="color: black;">dbo.Test1 </span><span style="color: grey;">(
</span><span style="color: black;">StatusId</span><span style="color: grey;">);</span><span style="color: black;">GO</span><span style="color: green;">-- ==================== Initial insert</span><span style="color: blue;">INSERT INTO </span><span style="color: black;">dbo.Test1 </span><span style="color: blue;">DEFAULT VALUES</span><span style="color: grey;">;</span><span style="color: black;">GO 10000</span><span style="color: green;">-- ==================== Call sys.dm_db_stats_properties()</span><span style="color: blue;">SELECT
</span><span style="color: black;">s.name</span><span style="color: grey;">,
</span><span style="color: black;">s.filter_definition</span><span style="color: grey;">,
</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">19</span><span style="color: grey;">), </span><span style="color: black;">p.last_updated</span><span style="color: grey;">, </span><span style="color: black;">120</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">last_updated</span><span style="color: grey;">,
</span><span style="color: black;">p.[rows]</span><span style="color: grey;">,
</span><span style="color: black;">p.rows_sampled</span><span style="color: grey;">,
</span><span style="color: black;">p.steps</span><span style="color: grey;">,
</span><span style="color: black;">p.unfiltered_rows</span><span style="color: grey;">,
</span><span style="color: black;">p.modification_counter</span><span style="color: blue;">FROM </span><span style="color: black;">sys.stats </span><span style="color: blue;">AS </span><span style="color: black;">s</span><span style="color: grey;">CROSS </span><span style="color: black;">APPLY sys.dm_db_stats_properties</span><span style="color: grey;">(</span><span style="color: black;">s.[object_id]</span><span style="color: grey;">, </span><span style="color: black;">s.stats_id</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">p</span><span style="color: blue;">WHERE </span><span style="color: black;">s.[object_id] </span><span style="color: blue;">= </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'dbo.Test1'</span><span style="color: grey;">)</span><span style="color: blue;">ORDER BY </span><span style="color: black;">s.has_filter</span><span style="color: grey;">, </span><span style="color: black;">s.name</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: green;">-- ==================== UPDATE STATISTICS</span><span style="color: blue;">UPDATE STATISTICS </span><span style="color: black;">dbo.Test1</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: green;">-- ==================== Call sys.dm_db_stats_properties()</span><span style="color: blue;">SELECT
</span><span style="color: black;">s.name</span><span style="color: grey;">,
</span><span style="color: black;">s.filter_definition</span><span style="color: grey;">,
</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">19</span><span style="color: grey;">), </span><span style="color: black;">p.last_updated</span><span style="color: grey;">, </span><span style="color: black;">120</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">last_updated</span><span style="color: grey;">,
</span><span style="color: black;">p.[rows]</span><span style="color: grey;">,
</span><span style="color: black;">p.rows_sampled</span><span style="color: grey;">,
</span><span style="color: black;">p.steps</span><span style="color: grey;">,
</span><span style="color: black;">p.unfiltered_rows</span><span style="color: grey;">,
</span><span style="color: black;">p.modification_counter</span><span style="color: blue;">FROM </span><span style="color: black;">sys.stats </span><span style="color: blue;">AS </span><span style="color: black;">s</span><span style="color: grey;">CROSS </span><span style="color: black;">APPLY sys.dm_db_stats_properties</span><span style="color: grey;">(</span><span style="color: black;">s.[object_id]</span><span style="color: grey;">, </span><span style="color: black;">s.stats_id</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">p</span><span style="color: blue;">WHERE </span><span style="color: black;">s.[object_id] </span><span style="color: blue;">= </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'dbo.Test1'</span><span style="color: grey;">)</span><span style="color: blue;">ORDER BY </span><span style="color: black;">s.has_filter</span><span style="color: grey;">, </span><span style="color: black;">s.name</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: green;">-- ==================== Update 50% of records (StatusId, Deleted)</span><span style="color: blue;">UPDATE </span><span style="color: black;">dbo.Test1</span><span style="color: blue;">SET
</span><span style="color: black;">StatusId </span><span style="color: blue;">= </span><span style="color: black;">2</span><span style="color: grey;">,
</span><span style="color: black;">Deleted </span><span style="color: blue;">= </span><span style="color: black;">1</span><span style="color: blue;">WHERE </span><span style="color: black;">ColId </span><span style="color: grey;">% </span><span style="color: black;">2 </span><span style="color: blue;">= </span><span style="color: black;">0</span><span style="color: grey;">;</span><span style="color: blue;">PRINT </span><span style="color: red;">'Update Count: ' </span><span style="color: grey;">+ </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">11</span><span style="color: grey;">), </span><span style="color: #434343;">@@ROWCOUNT</span><span style="color: grey;">);</span><span style="color: black;">GO</span><span style="color: green;">-- ==================== Call sys.dm_db_stats_properties()</span><span style="color: blue;">SELECT
</span><span style="color: black;">s.name</span><span style="color: grey;">,
</span><span style="color: black;">s.filter_definition</span><span style="color: grey;">,
</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">19</span><span style="color: grey;">), </span><span style="color: black;">p.last_updated</span><span style="color: grey;">, </span><span style="color: black;">120</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">last_updated</span><span style="color: grey;">,
</span><span style="color: black;">p.[rows]</span><span style="color: grey;">,
</span><span style="color: black;">p.rows_sampled</span><span style="color: grey;">,
</span><span style="color: black;">p.steps</span><span style="color: grey;">,
</span><span style="color: black;">p.unfiltered_rows</span><span style="color: grey;">,
</span><span style="color: black;">p.modification_counter</span><span style="color: blue;">FROM </span><span style="color: black;">sys.stats </span><span style="color: blue;">AS </span><span style="color: black;">s</span><span style="color: grey;">CROSS </span><span style="color: black;">APPLY sys.dm_db_stats_properties</span><span style="color: grey;">(</span><span style="color: black;">s.[object_id]</span><span style="color: grey;">, </span><span style="color: black;">s.stats_id</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">p</span><span style="color: blue;">WHERE </span><span style="color: black;">s.[object_id] </span><span style="color: blue;">= </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'dbo.Test1'</span><span style="color: grey;">)</span><span style="color: blue;">ORDER BY </span><span style="color: black;">s.has_filter</span><span style="color: grey;">, </span><span style="color: black;">s.name</span><span style="color: grey;">;</span><span style="color: black;">GO</span></pre>
</div>
<div>
<h3>
Results</h3>
</div>
<div>
<pre style="font-size: 11px;"><span style="color: black;">Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1)
Beginning execution loop
Batch execution completed 10000 times.
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------- ---- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL NULL NULL NULL NULL NULL NULL
IX_Test1_Deleted NULL NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId NULL NULL NULL NULL NULL NULL NULL
PK_Test1_ColId NULL NULL NULL NULL NULL NULL NULL
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-19 14:59:41 10000 10000 136 10000 0
IX_Test1_Deleted NULL 2012-12-19 14:59:41 10000 10000 1 10000 0
IX_Test1_StatusId NULL 2012-12-19 14:59:41 10000 10000 1 10000 0
PK_Test1_ColId NULL 2012-12-19 14:59:41 10000 10000 3 10000 0
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
Update Count: 5000
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------------- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-19 14:59:41 10000 10000 136 10000 0
IX_Test1_Deleted NULL 2012-12-19 14:59:41 10000 10000 1 10000 5000
IX_Test1_StatusId NULL 2012-12-19 14:59:41 10000 10000 1 10000 5000
PK_Test1_ColId NULL 2012-12-19 14:59:41 10000 10000 3 10000 0
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL</x64></span></pre>
</div>
<h3>
Observations
</h3>
<b>NULLs</b><br />
Not surprisingly, the result columns are all NULL before calling UPDATE STATISTICS.<br />
<br />
<b>Steps</b><br />
Steps is an interesting statistic. Steps indicates the "number of steps in the histogram." Note that "The maximum number of steps is 200." (<a href="http://msdn.microsoft.com/en-us/library/ms174384.aspx" target="_blank">ref</a>) You won't get 200 by simply adding more rows. Inserting 100,000 rows instead of 10,000 gives a steps value of 56 for the GUID index.<br />
<br />
<b>Filtered Indexes</b><br />
I was never able to get filtered rows to return any statistics even though the <a href="http://msdn.microsoft.com/en-us/library/jj553546.aspx" target="_blank">docs</a> say for the rows column, "If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table."<br />
<br />
Is this a bug in SQL Server or in my query?<br />
<br />
<b>modification_counter</b><br />
As expected, modification_counter is 0 before there are any modifications and after modifying 5,000 rows, the counts are 5,000 (except for the filtered indexes).<br />
<br />Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-66976559959728227002012-11-05T13:45:00.006-08:002012-11-05T13:46:46.279-08:00SQL Server Data Compression and TDEI had been looking for a definitive statement on how TDE and compression work together. I found it here:<br />
<br />
<a href="http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx" target="_blank">Data Compression: Strategy, Capacity Planning and Best Practices</a><br />
<blockquote class="tr_bq">
TDE encrypts the pages when they are written to disk and decrypts them when they are read from disk into memory. Because data compression (as well as decompression) is performed on in-memory pages, data compression always sees unencrypted data, and hence <span style="background-color: yellow;">the effectiveness and efficiency of data compression is not impacted by TDE</span>.</blockquote>
<br />
Most other resources I had found only address how TDE messes up backup compression.
Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-23320862587218189292012-08-23T11:22:00.001-07:002012-08-23T11:40:58.959-07:00SELECT INTO is faster than INSERT SELECTI had heard that SELECT INTO is faster than INSERT SELECT, so I wanted to prove it.<br />
<br />
Here are the results of testing in both FULL and SIMPLE recover models. Each test was run twice, once with INSERT-SELECT first and once with SELECT-INTO first.<br />
<br />
The <b style="color: #38761d;">net result</b> is that, at least for this test code on my hardware, using SELECT INTO is <b><span style="color: #38761d;">an order of magnitude faster</span></b> in <b><span style="color: #38761d;">SIMPLE</span></b> recover mode than INSERT SELECT. Using SELECT INTO is <b><span style="color: #38761d;">faster</span></b> in <b><span style="color: #38761d;">FULL</span></b> recover mode than INSERT SELECT, but the difference is less significant.<br />
<br />
<h2>
Test Results</h2>
<br />
Recovery Model: <b>Simple</b><br />
Order: INSERT-SELECT first<br />
Elapsed Mcs - INSERT-SELECT: 8,391,839<br />
Elapsed Mcs - SELECT-INTO: 962,096<br />
<br />
Order: SELECT-INTO first<br />
Elapsed Mcs - INSERT-SELECT:7,205,720<br />
Elapsed Mcs - SELECT-INTO: 918,092<br />
<br />
Recovery Model: <b>Full</b><br />
Order: INSERT-SELECT first<br />
Elapsed Mcs - INSERT-SELECT:7,095,709<br />
Elapsed Mcs - SELECT-INTO:1,289,129<br />
<br />
Order: SELECT-INTO first<br />
Elapsed Mcs - INSERT-SELECT: 6,274,628<br />
Elapsed Mcs - SELECT-INTO:1,902,190<br />
<br />
<h2>
Test Script</h2>
<br />
<code style="font-size: 12px;"><span style="color:blue">USE MASTER</span><span style="color:gray">;<br></span><span style="color:black">GO<br></span><span style="color:blue">IF </span><span style="color:gray">EXISTS (</span><span style="color:blue">SELECT </span><span style="color:gray">* </span><span style="color:blue">FROM </span><span style="color:black">sys.databases </span><span style="color:blue">WHERE </span><span style="color:black">name </span><span style="color:blue">= </span><span style="color:red">'SelectInto'</span><span style="color:gray">) </span><span style="color:blue">ALTER DATABASE </span><span style="color:black">SelectInto </span><span style="color:blue">SET </span><span style="color:black">SINGLE_USER </span><span style="color:blue">WITH ROLLBACK IMMEDIATE</span><span style="color:gray">;<br></span><span style="color:blue">IF </span><span style="color:gray">EXISTS (</span><span style="color:blue">SELECT </span><span style="color:gray">* </span><span style="color:blue">FROM </span><span style="color:black">sys.databases </span><span style="color:blue">WHERE </span><span style="color:black">name </span><span style="color:blue">= </span><span style="color:red">'SelectInto'</span><span style="color:gray">) </span><span style="color:blue">DROP DATABASE </span><span style="color:black">SelectInto</span><span style="color:gray">;<br></span><span style="color:black">GO<br></span><span style="color:blue">CREATE DATABASE </span><span style="color:black">SelectInto</span><span style="color:gray">;<br></span><span style="color:black">GO<br></span><span style="color:blue">ALTER DATABASE </span><span style="color:black">SelectInto </span><span style="color:blue">SET </span><span style="color:black">RECOVERY </span><span style="color:blue">FULL</span><span style="color:gray">;<br></span><span style="color:green">--ALTER DATABASE SelectInto SET RECOVERY SIMPLE;<br></span><span style="color:black">GO<br></span><span style="color:blue">USE </span><span style="color:black">SelectInto</span><span style="color:gray">;<br></span><span style="color:black">GO<br><br></span><span style="color:green">-- Source table: t1<br></span><span style="color:blue">CREATE TABLE </span><span style="color:black">dbo.t1 </span><span style="color:gray">(<br> </span><span style="color:black">ColID </span><span style="color:blue">INT </span><span style="color:gray">NOT NULL,<br> </span><span style="color:black">Col1 </span><span style="color:blue">CHAR</span><span style="color:gray">(</span><span style="color:black">30</span><span style="color:gray">) NOT NULL </span><span style="color:blue">DEFAULT </span><span style="color:red">'asdf'</span><span style="color:gray">,<br> </span><span style="color:blue">CONSTRAINT </span><span style="color:black">PK_t1 </span><span style="color:blue">PRIMARY KEY CLUSTERED </span><span style="color:gray">(</span><span style="color:black">ColID</span><span style="color:gray">)<br>);<br><br></span><span style="color:green">-- Populate t1<br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@MaxRows </span><span style="color:blue">INT = </span><span style="color:black">1000000</span><span style="color:gray">;<br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@i </span><span style="color:blue">INT = </span><span style="color:black">0</span><span style="color:gray">;<br></span><span style="color:blue">WHILE </span><span style="color:#434343">@i </span><span style="color:gray">< </span><span style="color:#434343">@MaxRows<br></span><span style="color:blue">BEGIN<br> SET </span><span style="color:#434343">@i </span><span style="color:gray">+</span><span style="color:blue">= </span><span style="color:black">1</span><span style="color:gray">;<br> </span><span style="color:blue">INSERT INTO </span><span style="color:black">dbo.t1 </span><span style="color:gray">(</span><span style="color:black">ColID</span><span style="color:gray">) </span><span style="color:blue">VALUES </span><span style="color:gray">(</span><span style="color:#434343">@i</span><span style="color:gray">);<br></span><span style="color:blue">END</span><span style="color:gray">;<br><br></span><span style="color:green">-- Destination heap: t2<br></span><span style="color:blue">CREATE TABLE </span><span style="color:black">dbo.t2 </span><span style="color:gray">(<br> </span><span style="color:black">ColID </span><span style="color:blue">INT </span><span style="color:gray">NOT NULL,<br> </span><span style="color:black">Col1 </span><span style="color:blue">CHAR</span><span style="color:gray">(</span><span style="color:black">30</span><span style="color:gray">) NOT NULL </span><span style="color:blue">DEFAULT </span><span style="color:red">'asdf'<br></span><span style="color:gray">);<br><br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@t1 </span><span style="color:black">time</span><span style="color:gray">(</span><span style="color:black">7</span><span style="color:gray">);<br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@t2 </span><span style="color:black">time</span><span style="color:gray">(</span><span style="color:black">7</span><span style="color:gray">);<br><br></span><span style="color:green">-- INSERT/SELECT<br></span><span style="color:blue">SET </span><span style="color:#434343">@t1 </span><span style="color:blue">= </span><span style="color:black">SYSUTCDATETIME</span><span style="color:gray">();<br><br></span><span style="color:blue">INSERT INTO </span><span style="color:black">dbo.t2 </span><span style="color:gray">(</span><span style="color:black">ColID</span><span style="color:gray">, </span><span style="color:black">Col1</span><span style="color:gray">)<br></span><span style="color:blue">SELECT </span><span style="color:black">ColID</span><span style="color:gray">, </span><span style="color:black">Col1 </span><span style="color:blue">FROM </span><span style="color:black">dbo.t1</span><span style="color:gray">;<br><br></span><span style="color:blue">SET </span><span style="color:#434343">@t2 </span><span style="color:blue">= </span><span style="color:black">SYSUTCDATETIME</span><span style="color:gray">();<br></span><span style="color:blue">SELECT </span><span style="color:magenta">DATEDIFF</span><span style="color:gray">(</span><span style="color:black">mcs</span><span style="color:gray">, </span><span style="color:#434343">@t1</span><span style="color:gray">, </span><span style="color:#434343">@t2</span><span style="color:gray">) </span><span style="color:blue">AS </span><span style="color:black">ElapsedMcs</span><span style="color:gray">;<br><br></span><span style="color:green">-- SELECT INTO<br></span><span style="color:blue">SET </span><span style="color:#434343">@t1 </span><span style="color:blue">= </span><span style="color:black">SYSUTCDATETIME</span><span style="color:gray">();<br><br></span><span style="color:blue">SELECT </span><span style="color:black">ColID</span><span style="color:gray">, </span><span style="color:black">Col1<br></span><span style="color:blue">INTO </span><span style="color:black">dbo.t3<br></span><span style="color:blue">FROM </span><span style="color:black">dbo.t1</span><span style="color:gray">;<br><br></span><span style="color:blue">SET </span><span style="color:#434343">@t2 </span><span style="color:blue">= </span><span style="color:black">SYSUTCDATETIME</span><span style="color:gray">();<br></span><span style="color:blue">SELECT </span><span style="color:magenta">DATEDIFF</span><span style="color:gray">(</span><span style="color:black">mcs</span><span style="color:gray">, </span><span style="color:#434343">@t1</span><span style="color:gray">, </span><span style="color:#434343">@t2</span><span style="color:gray">) </span><span style="color:blue">AS </span><span style="color:black">ElapsedMcs</span><span style="color:gray">;<br></span></code>
<br />
<h2>
References</h2>
<br />
<ul>
<li><a href="http://msdn.microsoft.com/en-us/library/ms188029.aspx" target="_blank">INTO Clause</a> at MSDN</li>
</ul>
Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com5tag:blogger.com,1999:blog-36914164.post-18887078954499312032012-08-20T14:59:00.001-07:002013-01-28T11:55:44.602-08:00SQL Server CDC: Remember It's Not FreeSomeone recently recommended that a developer use SQL Server's CDC (Change Data Capture) to create a history record for a transactional table because doing that work in a stored procedure "makes this stored procedure slow". Remember that there is no magic in CDC; it's not free.<br />
<br />
Based on the research I did for this article:<br />
<blockquote class="tr_bq">
<a href="http://www.databasejournal.com/features/mssql/article.php/3806001/Performance-Testing-SQL-Server-2008146s-Change-Data-Capture-functionality.htm" target="_blank">Performance Testing SQL Server 2008's Change Data Capture functionality</a></blockquote>
"The average penalty for CDC with full recovery is 10.51%."Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-81714164024210388482012-08-20T14:53:00.001-07:002013-01-28T11:56:02.383-08:00SQLskills' "IE1: Immersion Event on Internals and Performance"I just attended the most intense and valuable week of training I have ever experienced: SQLskills' "<a href="http://www.sqlskills.com/T_ImmersionInternalsDesign.asp" target="_blank">IE1: Immersion Event on Internals and Performance</a>". This is a full week of training given by <a href="http://www.sqlskills.com/AboutKimberlyLTripp.asp" target="_blank">Kimberly Tripp</a> and <a href="http://www.sqlskills.com/AboutPaulSRandal.asp" target="_blank">Paul S. Randal</a>.<br />
<br />
The whole week was great. These are two of the best trainers in the business, period. In this class, they teach everything, so you're getting great content every minute of every day.<br />
<br />
I <i>highly</i> recommend the class.Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-4352039381577762042012-06-29T08:19:00.000-07:002013-10-30T07:59:49.479-07:00Declare DataType When Creating Computed Column in SQL ServerWhen creating a computed column in SQL Server, be sure to use CONVERT (or CAST) to specify the datatype of the computed column, otherwise SQL Server will make the choice for you. It may not make the choice that you expected.<br />
<br />
Here is a very simple example that shows a computed column with two possible values: 0 and 1. With no covert, SQL Server defaults to int. By using convert, we can use bit. This simple example shows that using CONVERT drops the storage requirement from 4 bytes to a single bit.<br />
<br />
<h2>
Code</h2>
<code style="font-size: 12px;"><span style="color: blue;">SET NOCOUNT ON</span><span style="color: grey;">;</span><span style="color: blue;">USE </span><span style="color: black;">tempdb</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: blue;">IF </span><span style="color: grey;">EXISTS (</span><span style="color: blue;">SELECT </span><span style="color: grey;">* </span><span style="color: blue;">FROM </span><span style="color: black;">sys.tables </span><span style="color: blue;">WHERE </span><span style="color: black;">[object_id] </span><span style="color: blue;">= </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'dbo.TestTable'</span><span style="color: grey;">))<br /> </span><span style="color: blue;">DROP TABLE </span><span style="color: black;">dbo.TestTable</span><span style="color: grey;">;</span><span style="color: black;">GO</span><span style="color: blue;">CREATE TABLE </span><span style="color: black;">dbo.TestTable </span><span style="color: grey;">(<br /> </span><span style="color: black;">TestId </span><span style="color: blue;">INT </span><span style="color: #434343;">IDENTITY</span><span style="color: grey;">,<br /> </span><span style="color: black;">TestName </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">25</span><span style="color: grey;">) NOT NULL,<br /> </span><span style="color: black;">LamboNoConvert </span><span style="color: blue;">AS </span><span style="color: grey;">(<br /> </span><span style="color: magenta;">CASE<br /> </span><span style="color: blue;">WHEN </span><span style="color: black;">TestName </span><span style="color: grey;">LIKE </span><span style="color: red;">'Lamborghini%'<br /> </span><span style="color: blue;">THEN </span><span style="color: black;">1<br /> </span><span style="color: blue;">ELSE </span><span style="color: black;">0<br /> </span><span style="color: blue;">END<br /> </span><span style="color: grey;">),<br /> </span><span style="color: black;">LamboConvert </span><span style="color: blue;">AS </span><span style="color: grey;">(<br /> </span><span style="color: magenta;">CASE<br /> </span><span style="color: blue;">WHEN </span><span style="color: black;">TestName </span><span style="color: grey;">LIKE </span><span style="color: red;">'Lamborghini%'<br /> </span><span style="color: blue;">THEN </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: black;">bit</span><span style="color: grey;">, </span><span style="color: black;">1</span><span style="color: grey;">)<br /> </span><span style="color: blue;">ELSE </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: black;">bit</span><span style="color: grey;">, </span><span style="color: black;">0</span><span style="color: grey;">)<br /> </span><span style="color: blue;">END<br /> </span><span style="color: grey;">)<br />);</span><span style="color: black;">GO<br /></span><span style="color: blue;">INSERT INTO </span><span style="color: black;">dbo.TestTable </span><span style="color: grey;">(</span><span style="color: black;">TestName</span><span style="color: grey;">)</span><span style="color: blue;">VALUES<br /> </span><span style="color: grey;">(</span><span style="color: red;">'Lamborghini Aventador'</span><span style="color: grey;">),<br /> (</span><span style="color: red;">'McLaren F1'</span><span style="color: grey;">);<br /></span><span style="color: blue;">SELECT </span><span style="color: grey;">* </span><span style="color: blue;">FROM </span><span style="color: black;">dbo.TestTable</span><span style="color: grey;">;<br /></span><span style="color: blue;">SELECT<br /> </span><span style="color: black;">name </span><span style="color: blue;">AS </span><span style="color: black;">ColumnName</span><span style="color: grey;">,<br /> </span><span style="color: black;">TYPE_NAME</span><span style="color: grey;">(</span><span style="color: black;">system_type_id</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">TypeName</span><span style="color: blue;">FROM </span><span style="color: black;">sys.columns</span><span style="color: blue;">WHERE </span><span style="color: black;">[object_id] </span><span style="color: blue;">= </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'dbo.TestTable'</span><span style="color: grey;">);<br /></span></code>
<br />
<h2>
Results</h2>
<code>TestId TestName LamboNoConvert LamboConvert<br />----------- ------------------------- -------------- ------------<br />1 Lamborghini Aventador 1 1<br />2 McLaren F1 0 0<br /><br />ColumnName TypeName<br />--------------- --------<br />TestId INT<br />TestName VARCHAR<br />LamboNoConvert INT<br />LamboConvert BIT<br /></code>
<br />
Computed Column References:<br />
<ul>
<li><a href="http://www.databasejournal.com/features/mssql/article.php/3722696/SqlCredit---Part-12-Exploring-Indexed-Persisted-Computed-Columns.htm" target="_blank">Exploring Indexed Persisted Computed Columns</a></li>
<li><a href="http://www.databasejournal.com/features/mssql/article.php/3729221/SqlCredit-150-Part-13-More-on-Indexed-Persisted-Computed-Columns.htm" target="_blank">More on Indexed Persisted Computed Columns</a></li>
</ul>
Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-29753133329804761082012-06-22T08:40:00.002-07:002013-10-30T08:00:08.618-07:00List All Columns with MAX LengthTo get a list of all columns in a database with "MAX" length, use this query:<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">SELECT<br /> </span><span style="color: black;">SCHEMA_NAME</span><span style="color: grey;">(</span><span style="color: black;">t.[schema_id]</span><span style="color: grey;">) + </span><span style="color: red;">'.' </span><span style="color: grey;">+ </span><span style="color: black;">t.name </span><span style="color: grey;">+ </span><span style="color: red;">'.' </span><span style="color: grey;">+ </span><span style="color: black;">c.name </span><span style="color: blue;">AS </span><span style="color: black;">[Sch.Tbl.Col]</span><span style="color: grey;">,<br /> </span><span style="color: black;">TYPE_NAME</span><span style="color: grey;">(</span><span style="color: black;">c.user_type_id</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">[Type]</span><span style="color: blue;">FROM </span><span style="color: black;">sys.columns </span><span style="color: blue;">AS </span><span style="color: black;">c</span><span style="color: blue;">JOIN </span><span style="color: black;">sys.tables </span><span style="color: blue;">AS </span><span style="color: black;">t </span><span style="color: blue;">ON </span><span style="color: black;">c.[object_id] </span><span style="color: blue;">= </span><span style="color: black;">t.[object_id]</span><span style="color: blue;">WHERE </span><span style="color: black;">c.max_length </span><span style="color: blue;">= </span><span style="color: grey;">-</span><span style="color: black;">1<br /> </span><span style="color: grey;">AND </span><span style="color: black;">t.name </span><span style="color: grey;">NOT </span><span style="color: blue;">IN </span><span style="color: grey;">(</span><span style="color: red;">'sysdiagrams'</span><span style="color: grey;">)</span><span style="color: blue;">ORDER BY </span><span style="color: black;">SCHEMA_NAME</span><span style="color: grey;">(</span><span style="color: black;">t.[schema_id]</span><span style="color: grey;">) + </span><span style="color: red;">'.' </span><span style="color: grey;">+ </span><span style="color: black;">t.name </span><span style="color: grey;">+ </span><span style="color: red;">'.' </span><span style="color: grey;">+ </span><span style="color: black;">c.name<br /></span></code>Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-42380315166036961372012-06-08T16:51:00.000-07:002013-10-30T08:00:26.119-07:00SQL Server Date/Time Byte CountsHere is a table showing how many bytes each date/time datatype requires. The script to generate the table is after the table.<br />
<br />
<table border="1" cellpadding="1" cellspacing="0">
<tbody>
<tr><th>Type</th><th align="right">ByteCt</th><th>Example Precision</th></tr>
<tr><td>date</td><td align="right">3</td><td>2012-06-08</td></tr>
<tr><td>smalldatetime</td><td align="right">4</td><td>2012-06-08 16:27:00.000</td></tr>
<tr><td>datetime</td><td align="right">8</td><td>2012-06-08 16:27:12.187</td></tr>
<tr><td>time(0)</td><td align="right">3</td><td>16:27:12</td></tr>
<tr><td>time(1)</td><td align="right">3</td><td>16:27:12.2</td></tr>
<tr><td>time(2)</td><td align="right">3</td><td>16:27:12.19</td></tr>
<tr><td>time(3)</td><td align="right">4</td><td>16:27:12.188</td></tr>
<tr><td>time(4)</td><td align="right">4</td><td>16:27:12.1875</td></tr>
<tr><td>time(5)</td><td align="right">5</td><td>16:27:12.18751</td></tr>
<tr><td>time(6)</td><td align="right">5</td><td>16:27:12.187506</td></tr>
<tr><td>time(7)</td><td align="right">5</td><td>16:27:12.1875059</td></tr>
<tr><td>datetime2(0)</td><td align="right">6</td><td>2012-06-08 16:27:12</td></tr>
<tr><td>datetime2(1)</td><td align="right">6</td><td>2012-06-08 16:27:12.2</td></tr>
<tr><td>datetime2(2)</td><td align="right">6</td><td>2012-06-08 16:27:12.19</td></tr>
<tr><td>datetime2(3)</td><td align="right">7</td><td>2012-06-08 16:27:12.188</td></tr>
<tr><td>datetime2(4)</td><td align="right">7</td><td>2012-06-08 16:27:12.1875</td></tr>
<tr><td>datetime2(5)</td><td align="right">8</td><td>2012-06-08 16:27:12.18751</td></tr>
<tr><td>datetime2(6)</td><td align="right">8</td><td>2012-06-08 16:27:12.187506</td></tr>
<tr><td>datetime2(7)</td><td align="right">8</td><td>2012-06-08 16:27:12.1875059</td></tr>
<tr><td>datetimeoffset(0)</td><td align="right">8</td><td>2012-06-08 16:27:12 -07:00</td></tr>
<tr><td>datetimeoffset(1)</td><td align="right">8</td><td>2012-06-08 16:27:12.2 -07:00</td></tr>
<tr><td>datetimeoffset(2)</td><td align="right">8</td><td>2012-06-08 16:27:12.19 -07:00</td></tr>
<tr><td>datetimeoffset(3)</td><td align="right">9</td><td>2012-06-08 16:27:12.188 -07:00</td></tr>
<tr><td>datetimeoffset(4)</td><td align="right">9</td><td>2012-06-08 16:27:12.1875 -07:00</td></tr>
<tr><td>datetimeoffset(5)</td><td align="right">10</td><td>2012-06-08 16:27:12.18751 -07:00</td></tr>
<tr><td>datetimeoffset(6)</td><td align="right">10</td><td>2012-06-08 16:27:12.187506 -07:00</td></tr>
<tr><td>datetimeoffset(7)</td><td align="right">10</td><td>2012-06-08 16:27:12.1875059 -07:00</td></tr>
</tbody></table>
<br />
<b>Query</b><br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">SET NOCOUNT ON</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@now </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">7</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: black;">SYSDATETIME</span><span style="color: grey;">();</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@nowU </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">7</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: black;">SYSUTCDATETIME</span><span style="color: grey;">();</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@nowO </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">7</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: black;">SYSDATETIMEOFFSET</span><span style="color: grey;">();</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@d </span><span style="color: black;">date </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@s </span><span style="color: black;">smalldatetime </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt </span><span style="color: blue;">DATETIME = </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@t0 </span><span style="color: black;">time</span><span style="color: grey;">(</span><span style="color: black;">0</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@t1 </span><span style="color: black;">time</span><span style="color: grey;">(</span><span style="color: black;">1</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@t2 </span><span style="color: black;">time</span><span style="color: grey;">(</span><span style="color: black;">2</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@t3 </span><span style="color: black;">time</span><span style="color: grey;">(</span><span style="color: black;">3</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@t4 </span><span style="color: black;">time</span><span style="color: grey;">(</span><span style="color: black;">4</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@t5 </span><span style="color: black;">time</span><span style="color: grey;">(</span><span style="color: black;">5</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@t6 </span><span style="color: black;">time</span><span style="color: grey;">(</span><span style="color: black;">6</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@t7 </span><span style="color: black;">time</span><span style="color: grey;">(</span><span style="color: black;">7</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt0 </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">0</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt1 </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">1</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt2 </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">2</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt3 </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">3</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt4 </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">4</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt5 </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">5</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt6 </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">6</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dt7 </span><span style="color: black;">datetime2</span><span style="color: grey;">(</span><span style="color: black;">7</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@now</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dto0 </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">0</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@nowO</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dto1 </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">1</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@nowO</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dto2 </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">2</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@nowO</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dto3 </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">3</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@nowO</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dto4 </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">4</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@nowO</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dto5 </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">5</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@nowO</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dto6 </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">6</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@nowO</span><span style="color: grey;">;</span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@dto7 </span><span style="color: black;">datetimeoffset</span><span style="color: grey;">(</span><span style="color: black;">7</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: #434343;">@nowO</span><span style="color: grey;">;<br /></span><span style="color: blue;">SELECT </span><span style="color: red;">'date' </span><span style="color: blue;">AS </span><span style="color: red;">'Type'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@d</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: red;">'ByteCt'</span><span style="color: grey;">, </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@d</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: red;">'Example Precision'</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'smalldatetime'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@s</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@s</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'time(0)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@t0</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@t0</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'time(1)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@t1</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@t1</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'time(2)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@t2</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@t2</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'time(3)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@t3</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@t3</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'time(4)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@t4</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@t4</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'time(5)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@t5</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@t5</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'time(6)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@t6</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@t6</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'time(7)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@t7</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@t7</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime2(0)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt0</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt0</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime2(1)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt1</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt1</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime2(2)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt2</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt2</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime2(3)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt3</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt3</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime2(4)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt4</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt4</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime2(5)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt5</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt5</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime2(6)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt6</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt6</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetime2(7)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dt7</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dt7</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetimeoffset(0)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dto0</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dto0</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetimeoffset(1)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dto1</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dto1</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetimeoffset(2)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dto2</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dto2</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetimeoffset(3)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dto3</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dto3</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetimeoffset(4)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dto4</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dto4</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetimeoffset(5)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dto5</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dto5</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetimeoffset(6)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dto6</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dto6</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">)</span><span style="color: blue;">UNION </span><span style="color: grey;">ALL</span><span style="color: blue;">SELECT </span><span style="color: red;">'datetimeoffset(7)'</span><span style="color: grey;">, </span><span style="color: magenta;">DATALENGTH</span><span style="color: grey;">(</span><span style="color: #434343;">@dto7</span><span style="color: grey;">), </span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">100</span><span style="color: grey;">), </span><span style="color: #434343;">@dto7</span><span style="color: grey;">, </span><span style="color: black;">121</span><span style="color: grey;">);</span></code>Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-61904762652481435482012-05-25T07:59:00.003-07:002013-10-30T08:00:39.123-07:00SQL Fiddle<a href="http://www.sqlfiddle.com/" target="_blank">SQL Fiddle</a> is a new on-line tool that allows you to illustrate SQL DDL/DML. It currently defaults to SQL Server 2008 R2 but also supports 2012 and five other databases.<br />
<br />
Check out a simple example <a href="http://www.sqlfiddle.com/#%216/00d01/1" target="_blank">here</a>. This example shows the table and queries I built to answer <a href="http://stackoverflow.com/questions/10607195/sql-server-2008-difficulty-in-setting-date-and-time-timestamp" target="_blank">this Stack Overflow question</a>. When you build something in the tool, it automatically generates a unique URL for that schema/query pair.<br />
<br />
Bravo to <a href="http://stackoverflow.com/users/808921/jake-feasel" target="_blank">Jake Feasel</a> for creating such a great tool.<br />
<br />
RobRob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0tag:blogger.com,1999:blog-36914164.post-7357711530431562792012-04-18T14:52:00.000-07:002013-10-30T08:00:55.072-07:00UPDATE a Column While Simultaneously Setting a Local VariableI saw an interesting pattern in a Microsoft-supplied stored procedure today. They update a column and write a local variable at the same time.<br />
<br />
Here is an illustration of the technique.<br />
<br />
<b>Code:</b><br />
<code style="font-size: 12px;"><span style="color: blue;">USE </span><span style="color: black;">tempdb</span><span style="color: grey;">;</span><span style="color: blue;"> </span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">SET NOCOUNT ON</span><span style="color: grey;">;</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">GO</span></code><br />
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue;">CREATE TABLE </span><span style="color: black;">test1 </span><span style="color: grey;">(<br /> </span><span style="color: black;">ColId </span><span style="color: blue;">INT </span><span style="color: #434343;">IDENTITY</span><span style="color: grey;">,<br /> </span><span style="color: black;">ColValue1 </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">20</span><span style="color: grey;">),<br /> </span><span style="color: black;">ColValue2 </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">20</span><span style="color: grey;">)<br />);</span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"><br /></span><span style="color: blue;">INSERT INTO </span><span style="color: black;">test1 </span><span style="color: grey;">(</span><span style="color: black;">ColValue1</span><span style="color: grey;">)</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Col1-Initial'</span><span style="color: grey;">);</span><span style="color: blue;"> </span></code><br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">INSERT INTO </span><span style="color: black;">test1 </span><span style="color: grey;">(</span><span style="color: black;">ColValue2</span><span style="color: grey;">)</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Col2-Initial'</span><span style="color: grey;">);</span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"><br /></span><span style="color: blue;">DECLARE </span><span style="color: #434343;">@value </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">20</span><span style="color: grey;">);</span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"><br /></span></code><code style="font-size: 12px;"><span style="color: blue;">SELECT </span><span style="color: black;">ColValue1</span><span style="color: grey;">, </span><span style="color: black;">ColValue2 </span><span style="color: blue;">FROM </span><span style="color: black;">test1</span><span style="color: grey;">;</span></code>
<br />
<br />
<code style="font-size: 12px;"><span style="color: blue;">UPDATE </span><span style="color: black;">test1</span><span style="color: blue;"> </span></code><br />
<div style="background-color: yellow;">
<code style="font-size: 12px;"><span style="color: blue;">SET </span><span style="color: #434343;">@value </span><span style="color: blue;">= </span><span style="color: black;">ColValue2 </span><span style="color: blue;">= </span><span style="color: black;">ColValue1 </span><span style="color: grey;">+ </span><span style="color: red;">'-Updated'</span></code></div>
<code style="font-size: 12px;"><span style="color: blue;">WHERE </span><span style="color: black;">ColId </span><span style="color: blue;">= </span><span style="color: black;">1</span><span style="color: grey;">;</span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"><br /></span><span style="color: blue;">SELECT </span><span style="color: #434343;">@value </span><span style="color: blue;">AS </span><span style="color: red;">'@value'</span><span style="color: grey;">;</span></code><br />
<code style="font-size: 12px;"><span style="color: grey;"></span><span style="color: blue;">SELECT </span><span style="color: black;">ColValue1</span><span style="color: grey;">, </span><span style="color: black;">ColValue2 </span><span style="color: blue;">FROM </span><span style="color: black;">test1</span><span style="color: grey;">;</span></code>
<br />
<br />
<b>Results:</b><br />
<br />
<code><span style="color: black;">ColValue1 ColValue2<br />-------------------- --------------------<br />Col1-Initial NULL<br />NULL Col2-Initial<br /><br />@value<br />--------------------<br />Col1-Initial-Updated<br /><br />ColValue1 ColValue2<br />-------------------- --------------------<br />Col1-Initial Col1-Initial-Updated<br />NULL Col2-Initial</span></code>Rob Garrisonhttp://www.blogger.com/profile/09779841540941950357noreply@blogger.com0