Change Data Capture (CDC) is a powerful feature included in SQL Server 2008. Using change data capture, it is possible to determine which values have been added, changed, or deleted within a table. Setting up CDC is done by specifying tables to monitor. Under the hood, CDC is written using the same repl logreader function that transactional replication uses. Don’t worry too much though. If you are like me, then when you hear the word replication, you start running for the hills. Well, CDC is not as much maintenance. The biggest thing you have to worry about with CDC, is that the disk that contains the log never gets full. When that does happen, and it should be rarely, then yes it is a pain. What you have to do in that case is flip the do not truncate bit for the log file to ‘no’. That aside, let’s get started. First thing you want to do is enable change data capture. This is done at the database level. [cc lang=”sql”] USE AdventureWorks GO DECLARE @ReturnCode int EXEC @ReturnCode = sys.sp_cdc_enable_db SELECT @ReturnCode SELECT name ,is_cdc_enabled FROM sys.databases [/cc] Even though we performed the above action, nothing will happen until we define the tables and columns that it should monitor. When defining a table to monitor, there are a few parameters that need to be passed: CDC Parameters Parameter Description @source_schema The schema name of the table to monitor (ex: dbo) @source_name The name of the table to monitor @role_name […]
Continue reading ...
Luckily, two of the internal Microsoft tools used in order to diagnose slowdowns is also available for the publics use. These tools are SQLDiag (previously PSSDiag), and SQL Nexus. SQLDiag (short for SQL Diagnostics) monitors all aspects of the system that SQL Server is running on and generates all output needed in order to determine the bottleneck. The difficult challenge that follows is how to interpret that data. This is where SQL Nexus comes in. SQL Nexus is an application written on the .NET framework, that utilizes a client distribution of Reporting Services in order to give us insight into the output generated from SQLDiag. While it is relatively hopping along on 3 legs, we are still very lucky to have it. This video walks you through the initial process of setting up the traces and gives a brief intro in the data you can expect to find. In order to download, following this link and read the installation carefully. [code] http://sqlnexus.codeplex.com/ [/code] The installation guide will also walk you through downloading the ReadTrace tool (contained in the Microsoft RML tools download). And it will also tell you to download the PerfStats scripts which are used in order to initiate the SQLDiag trace. These are also important.
Continue reading ...
Browsing along the SQL Server documentation today, I noticed something that scared me. As a spoiled non ANSI compliant SQL Server developer, I do not use a semicolon to terminate my statements. Well, here’s the excerpt that threw me a curveball: Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version. Here is the link: http://msdn.microsoft.com/en-us/library/ms177563.aspx Holy Crap! My first thought was about all the code I have written and how I could automate inserting semicolons to the end of all the statements. While that would be almost futile, I suppose it could be done. I’m sure Microsoft will provide a tool that will append a semicolon for the future release however. If they did not, it would stop people from upgrading, which would cost them money. This realization made me think more about how I code and how much more important it is to enforce ANSI standards into your work. Being shielded from these subtleties in the past has not been a benefit. You can now tell that this SQL Server developer team is really serious about their product. First they got rid of the NON ANSI joins, which was a big achievement in my view, now they are moving onto the semicolon. I can’t say it’s a big surprise because if you have ever used common table expressions, you’ll know that’s where the first indication of requiring a semicolon was put into […]
Continue reading ...
Here is the maximum value for a datetime datatype in SQL Server: 9999-12-31 23:59:59.997 And the minimum: 1753-01-01 00:00:00.000 The maximum precision for a datetime value is 3 milliseconds. This is why the ending milliseconds above are shown as 997 instead of 999. Here’s the proof to get the max datetime in case you are interested. To get the minimum, simply add a negative sign to the increments below. [cc lang=”sql”] DECLARE @datetime DATETIME SET @datetime = GETDATE() WHILE 1 = 1 BEGIN BEGIN TRY — increment by days SET @datetime = DATEADD(dd,1,@datetime) END TRY BEGIN CATCH — cannot increment by days anymore WHILE 1 = 1 BEGIN BEGIN TRY — increment by minutes SET @datetime = DATEADD(mi,1,@datetime) END TRY BEGIN CATCH — cannot increment by minutes anymore BEGIN TRY — increment by seconds SET @datetime = DATEADD(s,1,@datetime) END TRY BEGIN CATCH — cannot increment by seconds anymore BEGIN TRY — increment by milliseconds SET @datetime = DATEADD(ms,3,@datetime) END TRY BEGIN CATCH — cannot increment by seconds anymore SELECT @datetime BREAK END CATCH END CATCH END CATCH END BREAK; END CATCH END [/cc] This took 7 seconds to run on my machine, fyi.
Continue reading ...
According to the title of the article, you’re probably here for one reason, to find the maximum value for a smalldatetime. Here it is: 2079-06-06 23:59:00 And the Minimum is: 1900-01-01 00:00:00 And here’s the proof: [cc lang=”sql”] DECLARE @smalldatetime SMALLDATETIME SET @smalldatetime = GETDATE() WHILE 1 = 1 BEGIN BEGIN TRY SET @smalldatetime = DATEADD(dd,1,@smalldatetime) END TRY BEGIN CATCH WHILE 1 = 1 BEGIN BEGIN TRY SET @smalldatetime = DATEADD(mi,1,@smalldatetime) END TRY BEGIN CATCH SELECT @smalldatetime BREAK END CATCH END BREAK; END CATCH END [/cc] The precision for smalldatetime is 1 minute. Meaning that you can only specify minute intervals. If you stumbled on this article and are wondering why someone might want the max smalldatetime, here’s a possible use. Say you have an effective date for a record. By this, I mean that this particular record is active from x date to y date. If the current day falls outside of that range, then this record is no longer active. So if you do not want to define an end date that the record is effective for (meaning it is always active), the best way to do this, would be to insert the maximum smalldatetime in order to signify, “always effective”. 2079 not long enough of a time for you? Hmm.. either you are a purist or somehow think you will be around that long, or are VERY devoted to your company. In any case, use Max datetime value instead.
Continue reading ...
