In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro 7 Have you tried SQL Backup Pro 7 yet?
Get faster, smaller, fully verified backups today. Try SQL Backup Pro 7's automated DBCC CHECKDB and be prepared when you need to restore. Download free trial.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
Improve your SQL skills Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

Capturing Baselines on SQL Server: Where's My Space?

In this article, we'll tackle the topic of monitoring disk space usage. By tracking how much is in use and how much is still available, over time we'll have the data we need for better capacity planning, and can ensure that a database won't ever run out of disk space. More »


Troubleshooting SQL Server RESOURCE_SEMAPHORE Waittype Memory Issues

This tip describes memory pressure issues that can arise from the RESOURCE_SEMAPHORE Waittype and how to find which query or transaction is causing the memory pressure. More »


From the SQLServerCentral Blogs - How Do You Query Maintenance Plan Package Metadata?

 Hello Dear Reader!  Since I’ve joined Pragmatic Works I’ve learned a lot, seen a lot, and assisted in interviewing a... More »


Editorial - Building Better Software

Why is programming so hard, especially on seemingly simple problems. Here's a short piece on the Apple iOS6 Do Not Disturb bug that surprised me. The bug is fixed, as of Jan 7, because it was a date issue. According to the article, the programmers that implemented the Do Not Disturb function used the ISO calendaring dates, which do not work as expected this year. The details are in the article, and it reads like an edge case, but it's not one I'd expect to have slipped by Apple's QA department. 

These types of programming errors seem to regularly slip by developers. The situation has gotten better since the days of VB6 when it seemed everyone that could complete a week long course was building buggy applications at a frightening rate. While I still think there are plenty of hack developers out there, it seems that with all the publication and information available, there are better programs being built in many organizations.

However simple bugs can still slip through. The maintenance plan bug in SQL Server 2005 showed Microsoft that there's no such thing as a simple fix or patch. In this case, even the Apple documentation for developers (Fixed Format section) notes that the ISO date should not take precedence when there is a conflict. Apparently not all of Apple's developers are following their own documentation.

Developing software is easy to do, but hard to get right, especially when you are building software that you expect to last a long time and get used in a variety of ways. Experience, practice, learning from your mistakes, and a deep understanding of the business you are working in help developers write better software over time. It's a reason why you should ensure you work to retain your talented developers. They are much harder to replace than the lines of code they produce. A new developer may produce the same amount of work in a short period, but I'd wager they produce many more bugs as well.

» 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:

USE tempdb

CREATE TABLE dbo.MyTable
(
 MyTableID INT NOT NULL IDENTITY (1, 1),
 SomeText VARCHAR(100)
);

INSERT  INTO dbo.MyTable
        (SomeText)
VALUES  ('qwerty');

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 100);

INSERT  INTO dbo.MyTable
        (SomeText)
VALUES  ('uiop');

SELECT  MyTableID
FROM    dbo.MyTable;

Which two values are returned? Assume this is SQL Server 2012.

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.

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.


Yesterday's Question of the Day

What is output of these statements?

DECLARE @BIT BIT
SET @BIT = 'TRUE'

IF ISNULL(COALESCE(@BIT,NULL),NULL) is NULL SELECT 'Output 1'
IF NOT EXISTS (SELECT 1 WHERE @BIT IS NOT NULL) SELECT 'Output 2'
IF EXISTS (SELECT 1 WHERE @BIT IS NULL) SELECT 'Output 3'

Answer: Answer: Nothing is output

Explanation: In all three cases the IF evaluates to 'FALSE' so the SELECT does not execute.

Reference: http://technet.microsoft.com/en-us/library/ms182587.aspx

» Discuss this question and answer on the forums

SQL Wait Stats Joes 2 Pros

You learn a lot when thrown into a crisis situation, where everyone is looking to you for answers. The system is down, the CEO is pleading to you for answers as the each minute cuts the company’s bottom line. When you are in a hurry, you need some new ideas right away. This book is your helpful tool. When used correctly, you can determine solutions and even related solutions to simple (yet lesser known) areas of SQL Server. Learn what causes SQL tasks to wait or site in queues. Even if your system is not in trouble right now, you can use what you will learn in this book to view the history of wait statistics.

Pick up your copy of this great book from Amazon today.


Featured Script

Check Candidate Columns for NOT NULL Constraints

This script reports table columns that allow NULL values but that currently have no NULL values, making them candidates for adding a NOT NULL constraint. 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

Does anyone have complete PDF/DOC on SQL Server replication ? - Hello, Does anyone have complete PDF/DOC on SQL Server replication ? Which includes configuration, scripts, monitoring etc; ? Of course I get everything...

Installation issues during SQL 2005 SP4 - I am trying to upgrade SQL 2005 to SP4 from SP2. Few SQL components were successful. version numbers are below Products...

create report with date suffix - I need to create a report & run weekly to output to file on server with filename_date×uffix.txt. The original report (which is...

Need script that totals free disk space AND free space in files - We have a number of ways, including Quest Spotlight in our case, to get either free space on the drives...

SQLServerAgent could not be started (reason: Unable to connect to server ''XXX; SQLServerAgent cannot start). - Hi Everyone.... I am getting this issue in windows server 2003 server cluster....I try to bring agent node online but no...

Cannot generate SSPI - Error message shown in the logs is : Approva.ApplicationBlocks.ExceptionManagement.BaseApplicationException: Error occurred in Openconnection in SQLServer. ---> System.Data.SqlClient.SqlException: Cannot generate SSPI context. ...

SQL Server 2005 : Business Intelligence

Need to add % sign for only few rows - Hi I have SSRS (2008) Report which line MTD YTD Total Line 1- Balance 10000 200001 208987 Line 1- Volumne 100 200 330 Line 1- Avg 42 67 78 Line 2- Balance ...

SSRS 2005 Multi-Value Filter. - Hi all, I met with a very tricky problem. I have a muti-value filter in my name whereby user are able...

SQL Server 2005 : SQL Server 2005 General Discussion

Maintenance Plans issues in SQL Server 2005 - Hi experts! This is my first time creating a Maintenance Plan in SQL Server 2005 and I’ve a couple of questions...

SQL Server 2005 : SQL Server 2005 Strategies

Run stored procedure when temp table is created/dropped - Hi guys and girls, I need to update 1 or 2 records in my database each time a temp table with...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Creating and Running a Trace from Stored Proc - is there some special format you have to put the SQL in, when you try and launch from a stored...

Tasty Performance scenario to feast on, looking for general feedback and theories only - Looking for some ideas on a performance scenario we are seeing in production. I can't make any changes to production...

SQL Server 2005 : SQL Server 2005 Integration Services

Header and Trailer Record - Hello, I have an SSIS package which creates a fixed width text file. I now need to add a header and...

SQL Server 2005 : T-SQL (SS2K5)

Where condition not equal to multiple columns - PUZZLE - who can solve this? - Concerning the puzzle below, need a SQL guru to tell me how they solved this problem. Goal: To get statement 2 to...

SQL Server 7,2000 : Administration

DTS Package - TestServer: SQL Server 2000 ProdServer: SQL Server 2000 Client on my machine: Sql Server Management Studio 2008. [b]Goal[/b]: Move DTS Package from Test...

SQL Server 7,2000 : Performance Tuning

High Volume of SQL Compilations/sec - hi there, We currently have an issue where on one of our SQL Servers, there is a high number of SQL...

How to Improve the perforamce of large tables ? - Hi All, Please give your valuable suggestions on how can i improve the performance of tables which having 2 crores of...

SQL Server 7,2000 : Replication

About moving data between databases - I have few tables that needed to be replicated/synchronized across several databases in our SQL Server 2012 . Example: DB1 has T1,...

about Transanctional Replication - hi can we have transcational replication for only few tables thanks with best reagrds pradeep

SQL Server 7,2000 : T-SQL

Compare Two sets of delimited string items - Hello, We have a Visual Studio application that is passing parameters to a Stored Procedure in SQL Server 2008 to search...

Null value is eliminated by an aggregate or other SET operation. - Hi all! Happy Friday! ok, I came across a stored proc that is sending out this error when it runs: "Null value...

SQL Server 2008 : SQL Server 2008 - General

PLE (Page life expectancy). - On a production system we have a PLE which is low at some moments. So since yesterday afternoon we have run...

Group By - Help - Hi geniuses, I need some help with a query I need for a report, here's what my table contain: Project | ReceiverGroup...

sql query to find in which datafile a particular table exists - Hi Friends, Let us assume am having 2 data files one mdf and one ndf. I have stopped the autogrowth...

how can i get count of sikped question depend record column and collectionofResponse column? - here i am having four table [code="sql"] DECLARE @question table ( QuestionId int, Record uniqueidentifier, indexnumber int, questiondetail text, IsActive bit ) -- select...

need book on perfmon counters - Hi Team, I need a material for sql perfmon counters. It should contain threshold values for those counters and definiton of...

How to change defualt directory for saving files from SSMS? - I mean sql-scripts. Now It saves only for a current session, after each reopening of SSMS I have to show...

IN clause for xml datatype - Can i use IN clause for xml datatype?

getting total disk space - i want to find the total drive space in server instaed of free space , can any one send the script...

Partition Tables - Hi All, I need the partition as following: The current data starts in the Head partition. Based on a partition function, When...

error - C:\Users\1245>sqlcmd -S MOLNEW0014\TEST -i c:\script.sql Sqlcmd: 'c:\script.sql': Invalid filename.

Execute statement in CATCH block - Hi, Need some help in determining if the below statement is good enough. How can I get statement to execute...

Linked Servers - I am trying to implement Linked Servers. but I am having problems establishing connection to my two servers: [img]http://i1159.photobucket.com/albums/p631/enjei26/01_zpsfabe67a0.png[/img] I have already...

Detect missing indexes - Hi all, I have a script to detect missing indexes in my database, but I'm not sure about info in this...

things to know when designing a new database - what are things one need to always keep in mind when designing a new database for an application

Add a column Count from another table - Currently I have this query. [code] SELECT C.listcode, C.year, COUNT(NamesTest.year) as Mailed FROM ( SELECT * FROM (SELECT DISTINCT listcode FROM NamesTest) as A CROSS JOIN (SELECT DISTINCT...

Unable to log in - I am unable to log into one of oursSQL Server Databases . It errors out " Cannot generate SSPI Context". I am...

Stored Procedure for dashboard - hi, i have three tables A,B,C in which i have datecreated column and status column in all three tables. Status will...

Initial log file size growth - Hi! Can someone tell me why the initial logfile size (set to 3gb) on a new log file grows as the...

Sql 2000 DB migration to SQL 2008 - I wanted to migrate one of our SQL 2000 database to new SQL 2008 server. So to check migration compatibility...

XML with word document style - hello all. is it possible create one xml file with word document style from query result?

update trigger - there are two types of trigger for updating table,one is column level and another is row level update trigger what is...

Moving Datab from one datafile to another - Hi All, We have one database with 3TB of size, with 6 Data files and 2 Transactional log files. Due to...

Calculating Median, Max, Min, Lower Quartile and Upper Quartile for a Boxchart? - Hi All - My boss asked me to create a boxchart using SSRS for some of our data. I read articles...

Nested replaces ? - Hello all, At this moment I need some nested REPLACE actions. Some time ago I saw a set of routines for string...

Unable to install sql server 2008 Management studio - Dear Sir/Madam, I downloaded Visual Studio 2010 from microsoft so its automatically installed sql server 2008 R2. In Sql server 2008...

Importing a csv file into a database - inserts quotes automatically into every field - We are using a SSIS package to insert a series of CSV files (saved in excel) into a database table. The...

Lock Pages in Memory setting for 64-bit systems - OK, I was just in a very active Twitter discussion about this question, and I would like to get input...

SQL Server 2008 : T-SQL (SS2K8)

Join on nvarchar column using LIKE - UPDATE LII SET value_exists = 1 FROM LII od JOIN VPL sl ON (sl.it like '%' + od.value + '%' AND od.opr = 9 ) WHERE od.tn...

SCAN count + OR Strategy - All, I found some wierd thing today when i ran the below two version of queries. select * from sub_fund where sub_fund_id in (1,2,3,4,5,6,7,8,9,10) scan count...

SQL Index Help.. - Hi guys, I have question for you guys, I have one table ABC (For Example) and Table ABC has few Indexes First...

Name search with Full Text Search - Hello. I'm having trouble implementing a query for searching names on a table PERSON, which has two columns, FORENAME and SURNAME. I'll...

Stored Procedure modification - Hi, We have existing Stored Procedure and we would like to modify to add some interwal logic to check that if...

Need Alternate Solution - Hi, I Created Function For Indian Money Format [code="sql"] CREATE function [dbo].[Fn_Indian_Money_Format] ( @amount MONEY ) RETURNS VARCHAR(50) AS BEGIN DECLARE @charMoney VARCHAR(50), @RemainingChar...

Problem with CAST to VARCHAR with SUBSTRING Function - I'm haveing trouble with a simple CAST to VARCHAR Statement. [code="sql"] SELECT CASE WHEN CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2))) = '98' THEN CAST(substring(Customer.STARTDATE, 3,...

Trying to understand ISDATE & CAST behavior - I am trying to understand why the case statement is failing... [code="sql"] create table #DateTest (col1 varchar(55), col2 varchar(255) ) insert into #DateTest(col1,...

SQL Server 2008 : Working with Oracle

Need help - MS SQL Trigger for Oracle insert - Hi, is it possible to use a ms sql trigger to insert a row in Oracle Table ? I use this way...

Performance of a Query : which one is giving good performace - Hi Please help me... I want join 4 tables in the Oracle.Table 1 is having 4 different where conditions. Which one is...

SQL Server 2008 : Security (SS2K8)

high risk stystem stored procedures - has anyone compilede a list of high risk system stored procedures in SS 2008 R2? like xp_cmdshell, that access the...

grant execute overrides user permissions - Hi. It looks like granting a user permission to execute a proc overrides that user's insert/update/delete permissions they'd otherwise have...

Encryption basics. - Hi all. I am researching the feasability of encrypting a few columns in one of our databases. I have done...

SQL Server 2008 : SQL Server 2008 High Availability

Handling Stored Procedure - Hi all, I am developing a console application. After doing the initial checks and pre requisites, i have to introduce 5...

Synch two SQL databases - Env: SQL Server 2008 R2 We have two SQL Databases used by two different applications. We need to synchronize some of...

Peer-to-peer replication and identity columns - All of the articles regarding identity columns for peer-to-peer replication talk about setting identity ranges when writing to multiple members...

Changing compatibility - Is it fine if we change compatibility level of a Database which is in Logshipping from 80 to 90 in...

Monitor Log Shipping - Monitoring Log Shipping My client in Manhattan maintains a disaster recovery server in South Brooklyn. We employ log shipping to sync...

LOG SHIPPING by two different systemt architecture? - Hi, Can we able to configure for LOG SHIPPING by two different systemt architecture? source server ------------- System type – windows 2008 R2 enterprise edition...

SQL Server 2008 : SQL Server 2008 Administration

Concurrent Database Access Per Instance - Hi All, I am a newer in this forum. We are using SQL Server 2008 R2 Enterprise Edition in our Company....

value cannot be NULL paramter name: viewInfo (Microsoft.SQLServer.Management.SQLStudio.Explorer) - I have a SQL Server 2008 R2 x64 machine that I used to update 20+ Databases. My Login works fine but...

check traces - Is there a way to find out what traces are enabled in a sql server instance? and also what is the...

Trace Flag 1118 - Please share what helped shape your point of view on whether or not to enable TF 1118 in your SQL...

create a policy in policy management - How to create a policy that prevents users to create user objects in master database? Thanks

Trace Flags to turn on for SQL instances < 2012 - I need some opinions here. There are 2 trace flags [url=http://www.sqlservercentral.com/articles/trace+flags/70131/]3504 [/url]- For internal testing. Will raise a bogus log-out-of-space condition from...

tempdb files on SSD - On sql 2008 R2 which has 40 CPU's and one SSD drive I want to do the following, please throw...

central management servers - I setup a sql server instance to host central management servers. Then I added all the sql servers to diffrent group...

Error backup database: nonrecoverable I/O error occurred on file ... - Hi all, I have installed SQL Server 2008 Std Edition in Cluster on Windows 2008 R2. I have a maintenance plan that...

Career : Certification

How to get started with a career - (Creating a new thread so I can inform about my background) Hello, I am Sagar. I have a Bachelors in computers...

SQLServerCentral.com : Anything that is NOT about SQL!

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

SSRS 2008 R2 inserting lines in Table Report - How to insert the Straight lines in tables if we try to insert through Tool Bar option it is inserting...

Reportserver Engine changing non-unicode to unicode - I am experiencing this exact issue as mentioned here [url=http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/06/17/possible-performance-implications-when-using-string-parameters-in-reporting-services.aspx][/url]. I do have a work around. I was wondering is...

Issue with Small matrix spilling over 2 pages - I have a small matrix that measures 3.5 on the ruler. my report is set as landscape. but it seems...

Barcode in RDLC Programmatically - How to generate RDLC Barcodes Programmatically........

Reporting Services : Reporting Services 2005 Development

Reporting Services - Dataset multiple - Hi I have a problem with use of Dataset multiple. I would want to create a 'similar' join between two dataset...

Database Design : Design Ideas and Questions

Urgent normalisation help please! - :-)

Data Warehousing : Integration Services

SSIS convert to date issue - im developing an ssis package. while im in the sql server , there is a field that contains a text string , for...

Help !! Create dynamically table destination - Hi All, Please I need your help. I have an excel file File.xls that i want to load in a table TempTable....

The column "Column 2" cannot be processed because more than one code page (65001 and 1252) are specified for it. - I get this error if i try to import a tab delimited flat file to a table in sql server. [i]The...

Data Warehousing : Strategies and Ideas

SQL Server 2012 VLDB Data Warehouse Design Question - Appreciate feedback to this question. I'm working on a design for VLDB that will be in the several TB size...

Data Warehousing : Analysis Services

MDX trouble in a calculated member - Hi to all. I use SSAS 2008 R2 and i built a cube. In this cube i have a little trouble with...