In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.
 
SQL Backup Pro Can you can restore your backups under pressure?
Use SQL Backup Pro's fully integrated DBCC CHECKDB to verify your backups, so you can restore them when it matters most. Download a free trial now.

In This Issue

Data Mining Introduction Part 3: The Cluster Algorithm

This is the part 3 of the Data Mining Series from Daniel Calbimonte. This article examines the cluster algorithm. More »


SQL Saturday #188 - Lisbon

Join us for SQL Saturday #188, a free SQL Server training event in Lisbon, Portugal. This event also has pre-conference paid workshops. More »


SQL Server Central Webinar Series #22 – What counts for a DBA (Wednesday, March 13 2013 5:00pm - 6:00pm GMT)

In this session, Louis Davidson, Microsoft MVP, will discuss how being observant of the environment you work in can help you make sure that you are aware of the health of your database systems, as well as your career. More »


SQL Server Statistics Questions We Were Too Shy to Ask

If you need to optimise SQL Server performance, it pays to understand SQL Server Statistics. Grant Fritchey answers some frequently-asked questions about SQL Server Statistics: the ones we somehow feel silly asking in public, and think twice about doing so. More »


From the SQLServerCentral Blogs - Script to clean up “Windows” logins no longer in AD

I was scanning http://dba.stackexchange.com and ran across the following question: http://dba.stackexchange.com/questions/31478/sql-server-script-to-delete-accounts-no-longer-in-active-directory Basically the OP wanted to know how to get rid of... More »


Editorial - Self Describing Databases

When contained databases were introduced in SQL Server 2012, I thought they were a very nice enhancement to the platform. The idea of having security managed inside the database, thereby removing the problem of orphaned users, was something I looked forward to. Preventing collation conflicts is also a nice benefit, though I've never had a problem with this issue. However I was, and still am, hoping that this was just the beginning of what a contained database would be. I had a vision of a self-describing database.

There are many things that we need to manage with databases: maintenance, backups, auditing, performance statistics, alerts, and more. Most of these are set up at the instance, but I've never quite understood why. Software should help us work more efficiently, and work with us, not against us. So many of these administrative items are dependent on the needs of a particular database or application, but we perform all these actions at the instance level.

Backups are an example of a database dependent item, as we constantly teach new DBAs. However the backup plans are set at the instance level, and if you have disparate requirements for different databases, these result in different backup plans. The MSDN documentation says we should even have a manual for each database. If that's the case, why is the information about a database backup stored outside of the database? I think it would make more sense for the backup plan to be stored in the database, and when the database is attached, or the plan created, the instance would read this plan and create the necessary jobs. If we detached the database, the jobs would be removed. We run recovery in each database, couldn't that serve to enable or disable any alerts, jobs, etc. for any database that wasn't accessible?

I envision much more than just backups as being a part of the database. Every action we need to manage in a database from alerts to auditing, should be stored there. Let the Agent on the instance read the database and automatically set up the jobs needed. If there are conflicts with scheduling, alert the DBA at the instance level, but keep the metadata about the needs of the database in the database. It would make consolidation, or movement of databases much simpler. It would also fit with the idea of a database service, rather than a database server.

» Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

What will be the output of the query?

CREATE TABLE #TEST 
(
 FirstDate DATETIME,
 LastDate DATETIME2
)

DECLARE @i int

SET @i = 100

WHILE @i > 0
 BEGIN
  INSERT #TEST values (sysdatetime(), sysdatetime())
  SET @i = @i - 1 
 END

SELECT distinct a.FirstDate, b.LastDate
 FROM #TEST a
  INNER JOIN #TEST b
    on a.FirstDate = b.LastDate

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: T-SQL. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Yesterday's Question of the Day

Using SQL Server 2012 and 2008 R2 - DEVELOPER EDITION, U.S. version, installed without revisions i.e., default values not altered, I execute the following two (2) T-SQL statements

SET DATEFORMAT mdy;

SELECT CAST('1/27/49' AS date) AS 'Value 1',CAST('1/27/50' AS date) AS 'Value 2'

,CAST('1/27/1949' AS date) AS 'Value 3',CAST('1/27/1950' AS date) AS 'Value 4'

The question(s) is/are: what dates are returned by each statement ?

For example:  (This example may or may NOT be a valid answer, as per the famous T-SQL statement "IT DEPENDS")

Value 4 is 1950-01-27.

SELECT the four (4) correct answers.

Answer:

  • Value 1 is 2049-01-27
  • Value 2 is 1950-01-27
  • Value 4 is 1950-01-27
  • Value 3 is 1949-01-27

Explanation: Use the two digit year cutoff option to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years. A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.

Ref: http://msdn.microsoft.com/en-us/library/ms191004.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Featured Script

SQL Server Performance Base Line Script

Performance Base Line Report from SQL Server DMVs and Perfmon counters More »


Database Pros Who Need Your Help!

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

SQL server Jobs fail over - Hi, Is there any possibilities for SQL server Job fail over to the different server???

Is trigger a bad solution - Dear, I fire triggers on different events. But some of my clients insist on not using trigger. Because they claim that...

Move Primary Key to File Group? - Was wondering if anyone was aware of any papers or best practices they could point me to regarding moving Clustered...

SQL Server 2005 : Business Intelligence

My FIrst Web Report Problem - Hi Specialists. I have created my first ever report in Microsoft Visual Studio and it works fine, however when i try...

SQL Server 2005 : SS2K5 Replication

Error while creating Publication on sql server 2005 - Hi all, i am re-creating new publication through New Publication Wizard but it showing Error :- "sp_MSreplagentjobexists can only be executed in...

SQL Server 2005 : SQL Server 2005 Integration Services

TDS buffer length too large and Protocol error in TDS stream - Hi all I am trying to bring data from Application Server to Staging DB in SQL Server 2005 using SSIS...

Convert datetime in SSIS - Hello i have a excel doc with the following data in in a column 31-JAN-13 15.04.51.590000000 how do i convert this...

passwords and package encryption - Due to unicode "upgrade", I'm looking at redoing about 20 packages. I use the wizard to generate the package. This...

Lookin for Reference for EDI Using SSIS and X12 Format Files - Let me start by saying that I'm not looking for assistance in solving a work problem. I'm looking to improve...

Problem with running SSIS packages - I've just about run out of ideas here, hope someone can help. I've created a bunch of SSIS packages and am...

SQL Server 2005 : T-SQL (SS2K5)

Looping without cursor not updating properly - I got asked to not use a cursor for a loop that I'm working on so I grabbed the code...

SQL Server 2005 : SQL Server Newbies

sequencial insert - I have list of Heads like A, B, C, D.... and like to insert in a table with three column COMCOD,...

Problem loading csv files with SSIS - I receive a number of csv files from a third party on a regular basis, it could be 1 it...

SQL error Connection. - http://imageshack.us/photo/my-images/339/89149644.jpg http://imageshack.us/photo/my-images/4/36524814.jpg When i Click start at "Service manager" they be like this . Im using SQL 2000 =Sory my bad english

SQL Server 7,2000 : Administration

xp_sendmail not sending @message text - xp_sendmail will send out the email with correct to address, correct subject line but the message text is blank. This...

SQL Server 2008 : SQL Server 2008 - General

SQL.log file growing rapidly C:\Users\sqlaccount\AppData\Local\Temp - Hi Guys, On the development server I noticed a file called SQL in [b]C:\Users\sqlaccount\AppData\Local\Temp [/b] that has grown up to 10GB....

Looging all executed SQL Server Queries - Hello Friends, I am using SQL 2008 R2. This instance of SQL Server ,many user uses.All user uses the same user sa...

Table affected by which task - There is any way to find out that particular table in database is affected by which ssis task or which job...

Larger Date Range taking MUCH less time - Hi I'm executing the below TSQL on a table with about 700,000 records. I am clearing the caching so it does...

Differential Backup Blocking Trans Log Backup - Hi, I have a maintenance plan that runs a differential backup every 4 hours and a separate job that backs up...

my backup maintenance plan is failing - Hi friends my backup plan is failing from error logs i got this information. can any one throw some light...

Doubt in Data Warehouse design... - Hi, We are creating a Data Warehouse for our Client We need to store a list of locations that our company. My Colleague...

Need Help In SSRS - Hi everyone i have an issue in SSRS report .i have created a new entry for one column of a...

REG compatability levels of a database - Recently i got new job. when i checked the production databases. some databases are in compatibility level 80 . some in...

Create CLUSTERED Index - Hi I want to change the order of two fields in Pk of a table,I need to drop it and recreate...

SQL backup plan - what are the best practices for the backup plan.

Diagnosing Page Latch Issue - I'm working with both a Vendor's DB and a purge script they've provided, and I need some outside opinions. This purge...

Backup issue through maintenance plan - i am getting the below error while taking the backup via maintence plan.. i am getting an error only for...

Sharepoint SQL Server - http://technet.microsoft.com/en-us/library/cc298801.aspx •To ensure optimal performance, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances...

How to get Below T-SQL qury Output..? - [size="3"][font="Courier New"]Hi All, This is the query I have written DECLARE @FromDate DATETIME DECLARE @EndDate DATETIME SET @FromDate = '2013-01-01 00:00:00.000' SET @EndDate = '2013-02-13 00:00:00.000' SELECT year(sd.FKDAT)...

How to update same record that fires trigger? - There is a flag column that basically indicates that record changed since the last "housecleaning". Can I create a trigger on...

Not consuming memory - My company is rolling out a new product that was supposed to go to beta on March 1. That has...

How to update multiple tables in sql server 2008 ? - Hi All, I want to update multiple columns from multiple tables in a single UPDATE Query... Just want to do like below...

Executionlog2 table - How much time Executionlog2 table and catlog table in ReportServer database hold the data? Does it store all historical data and...

Inserting currency symbols in money datatypes - Hi, I heard we can insert currency Symbols like $ to Money and small money datatypes. But while i am trying i...

modifying a SP - We already have a SP which selects data from db. I need to modify the SP so that it selects one...

Table variable subquery as column - Hi All, I'm trying to use a value from a table variable's column that matches w/a passed in variable as a...

sqlcode vs sp - hi friends i have small doubt in sql plese tell me how to Determine when to use stored procedures vs....

Repeating code over time period - Hi Guys, I have a code snippet to run every month and have to take a union of current data and...

SQL Server for Content Censorship - Hi Champs, We have little strange requirement in a case of SharePoint where backend is SQL. Users in social features of...

If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION - Can someone explain how data compression works at the index level? If I compress a primary key, am I compressing...

Virtualization - yet again ... - I spent some time searching this forum and the interwebs and haven't found a lot of definitive insight/agreement on virtualization...

SSRS Reporting assitance- consolidated report using 3 Stroed PROC - Hi, I have 3 Stored Procedures 1) SP_JobPostingID, 2)SP_WorkerOrder, and 3) SP_WorkOrderRevision Note- all the 3 have same number and signature of Columns, what...

SQL Server 2008 R2 not starting after moving"model" database - SQL Server 2008 R2 not starting after moving model database. Any ideas? Error: 945, Severity: 14, State: 2. Any ideas...

Transaction log in Subscriber database for Transactional Replication - I have created replication between two SQL Server 2008R2 databases and it has been functioning well since the initial setup...

Query in SSRS - hi All ,i have one requirement where the SSRS report is displaying only certain data of a column based on...

SQL Server 2008 : T-SQL (SS2K8)

Combine variable and select statement - I want to combine this select statement and a variable(@EndUserDel) to get result.how can i do that? [code="sql"] declare @EndUserDel nvarchar(20) select...

Group a set of chain values - I have the following table in SQL Server 2008 [code="sql"] DECLARE @UnitConvert table ( ID int identity(1,1), ConvertUnitOne nvarchar(50), ConvertUnitTwo nvarchar(50) ) INSERT INTO...

XML with NameSpaces in each Node. - HI Guys, I came to a situation where I have to load XML file in SQL server tables. I have worked...

Need Help Deconstructing Tree Hierarchy Adjacency menu Table with T-SQL - I have a Menu table with a structure like this: MenuID, MenuDesc, ParentMenuID, Sequence The Menu is for a packaged bit of...

Single value MIN and MAX dates from multiple rows - This post has the code and an Excel Workbook with results and expected results (Red text) My query works for the...

Update master from detail - I have a timesheet master table and a related timesheet detail table. Each detail row could trigger need for an...

XML nodes extraction - Hi, Thanks for your help in advance, working with XML nodes for the first time and finding it frustrating. I...

ORDER BY = Bubble Sort ? Quick Sort ? Insertion Sort ? - Hi All, When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer? 1)...

remove duplicates IF matches - So I have the following statement: SELECT ca.Caseid,[weeknum],[monthName], Response1_Num , Response2_Num, Response3_Num, Response4_Num, comments,[userlogin], [supervisor], [customer id], comm_ID FROM HMD hm join...

SQL Server 2008 : SQL Server Newbies

Filtering by StatusId - I have a Stored Procedure which returns data based on the StatusId. DECLARE @StatusId INT=2 SELECT * FROM Employees WHERE StatusId=ISNULL(@StatusId,StatusId) If the StatusId is...

Query Help - [code="sql"] I want a generic query to move the "IN" state records from tableB,TableC,TableD to TableA which has studid as...

import data with identity columns... - Really going crazy here! I have table1 and table2 [for e.g. purpse]. They are of exact design: id [autoincrement, primary key, int] name...

Best way to store images - Hi. I recognized if we store an image in binary type in SQL SERVER they take more space. For example a...

Performance Issue - Memory - Hi All On one of my SQL Servers, I am noticing what I think is memory pressure PLE is +-100 Buffer Cache Hit...

Business Intelligence SQL Server 2008 where to start? - Hi guys, Can you recommend a site or a book where I can learn BI for sql server 2008. I know...

looping thru views - I have a database with a large number of views. I would like to to be able to loop through...

Stuck on new fault with my update - Hi Professionals I am running the following query as advised previously which updates the source table based on a column...

Linked Server Anonymose Logon Error - Hi, So we are changing the SQL service account. The account we are changing to is in a domain user and...

SQL Server 2008 : SQL Server 2008 High Availability

How to stop SQL services on both cluster node without failover - I need to clean stop SQL services and dependent services on Cluster both node without failover for adding new SAN...

Mirroring failover - Hi All, I have to failover couple of the prod databases.....which resides in cluster. What are some of the things I need...

Database mirroring question - Hi, We use database mirroring in sql server 2008 r2. We have 1 primary server and 1 mirror server. Our application connect to...

Mirrored database becomes SUSPENDED during nightly maintenance - Hi there! I will try to keep this post as short as possible, without leaving out any crucial information. [b]Server Setup:[/b]...

Not setting NOT FOR REPLICATION on subscriber foreign keys. - So it seems that the default behavior for transaction replication is to mark several things as NOT FOR REPLICATION. This...

SQL Server 2008 : SQL Server 2008 Administration

SQL Server DBA Support Services - Hi All, Can you suggest the organizations that are providing services on SQL Server DBA support? Apart from Microsoft ofcourse... :-) Regards Mayur...

Trace flag 4199 - Generally do you run with this on or off? I've always been slightly worried about setting this trace flag to ON...

Tracking changes to a database via triggers and the transaction logs - Hello -- We have SQL Server 2008R2 standard running on one of our servers. The server contains three databases. One of...

Script to calculate when a job ended. - I need to alter the script listed below to calculate the DateTime that the Job completed and sort on the...

Replication??? - My database ''ABC" is configured with snapshot to Sub 1 and transactional replication to Sub 2. last week i drop...

Database Timeouts : Error -2 Severity 11 - Hi Folks, at times with heavy workload, my devs tell me they receive a lot of database timeouts. A the database...

How can you issue a full backup and not interrupt the LSN's - My two cents worth...I was reading an article the other day on junior dba questions and saw the question: How can...

Question about DBA authority versus responsibility - Hi, I'm looking for some advice for how I can assert myself in this situation without alienating my colleagues or causing...

WMI event for mirroring - Hi Friends, I have to implement Mirroring on 4 databases. 1. How can I write code for Failover for all databases....

Reorganize index online option - Does Reorganizing index with online option causes high disk I/O...I noticed users are getting timeouts after running reorganizing index command...

Audit triggers - Dear Experts How to use triggers in auditing and in which cases Can any one provide examples Thanks

Career : Certification

Slave to 70-450!! - Hi all, I'm in need of some advice from some learned peeps regarding study material etc for my 70-450 exam. I've...

Career : Employers and Employees

Client does not want to pay overtime. How to deal with it ? - I am hourly-paid employee of a consulting company, but work all the time at client site. Sometimes I have to...

SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Counting for Labels - I am running all of my production labels using SSRS 2008R2 I am trying to constuct a label that will Print...

Similar Reports - Hi, We have many reports running that are very similar. Usually 1 column difference for example Sales by Product Sales by Month Sales by...

SSRS report to show the results of two datasets at different granularity on same report - I need to build a report to show the Top n products and sum of the rest products across column...

Launching a Report from the URL link - Hello All, I'm using SSRS 2008 R2. After rendering a report, I want to be able to copy the URL link...

Thread being aborted at subscription - Hi , I receive very often the error message 'Thread being aborted' at several subscriptions of different reports. The strange thing is...

Data Warehousing : Integration Services

Merge,Look Up,SCD which one is good for millions of records - Hi, I am new to SSIS. I have to load 50 millions of records(full load) into a SQL Server table. After the full...

Update Data in Prod table. Lookup ? Merge? - Hi, I am in need of a solution. I am supposed to load the data of a table from PROD server...

Datatype Issue ..SSIS - HI, I have a task in ssis which loads data into DB from Source Files on my Drive, Issue is...

Documentation in SSIS - I am just starting learn and use SSIS, and was wondering how you document changes? In a stored proc I...

Data Warehousing : Analysis Services

Different aggreation results with and without a dummy WITH clause - I'm trying to diagnose a bug in a calculated measure in a SSAS cube, and while debugging I get this...