A community of more than 1,500,000 database professionals and growing

SQLServerCentral Runs sp_Blitz - Performance Part 1

Steve Jones from SQLServerCentral.com

The third article that examines the output of the sp_Blitz script™ run against SQLServerCentral's database servers. More »

Free Webinar: Statistics in Oracle and SQL Server

Press Release from Red-Gate

In their third live 'Oracle vs. SQL Server' discussion, Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will look at statistics in Oracle and SQL Server. Expect a lively debate on Oracle vs. SQL Server from two leading RDBMS experts on Jan 23, 2013. More »

Hypothetical Indexes on SQL Server

Additional Articles from SimpleTalk

Sometimes, you have to test out alternative indexing strategies, but the task of creating the candidate indexes would just take too long. Is there another way? Well, yes, why not use the same method the DTA (Database Tuning Advisor) uses, and take the tedium out of the job. More »

I believe in encryption. I have an encryption talk that delivers the very basics of what encryption options exist in SQL Server, and I would think that most data professionals that develop or manage databases should understand encryption and how to enable it. I find lots of people don't understand, and I'm glad that I can try to teach them a few things about when encryption is and how it works in SQL Server.

Cloud computing use is growing, and while we don't have a good definition for what a cloud computing means, more and more organizations are considering looking at moving some of their applications, and data, to the cloud. As we move this data, many DBAs have concerns over the security of the data they are supposed to protect. These are valid concerns, and they should be at the forefront of your mind if you plan on moving anything to any type of service provider.

One interesting point brought up in this op-ed is that customers ought to have their data encrypted, but the keys used for the encryption should be controlled by the customer, not the service provider. That's sound business strategy, and it should be the case with all your data, not just data sent to the cloud. If you use a service to rotate and store your tape backups, you ought to have encryption on those tapes, with keys managed by your company. Just as you ought to have the keys inside your company if you use locks on the shipping containers holding the tapes.

Steve Jones from SQLServerCentral.com

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

• Watch the Windows Media Podcast - 17.2MB WMV
• Watch the iPod Video Podcast - 16.3MB MP4
• Listen to the MP3 Audio Podcast - 3.4MB MP3

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

Today's Question:

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'

By Thomas Kovarik

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 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.

In SQL Server 2012 Developer Edition, I execute the following T-SQL statements

DECLARE @date VARCHAR(50),@I INT;

SET @date = '2016-01-01 13:16:43.350'

SET @I = 0

WHILE @I < 12
 BEGIN
  SELECT DATENAME(Weekday,(SELECT EOMONTH ( @date,@I ))) + ', ' 
  + DATENAME(MM,(SELECT EOMONTH ( @date,@I )))
  + ' ' + DATENAME(d,(SELECT EOMONTH ( @date,@I ))) +','
  + CAST(DATEPART(YEAR,(SELECT EOMONTH ( @date,@I ))) AS NVARCHAR(4))

  SET @I = @I + 1
 END

The questions are as given. Select the correct answer(s). Choose 4

By bitbucket

Answer:

• 2016 is recognized as a leap year
• The last day in February 2016 is Monday Feb 29th
• The last day of 2016 is Saturday Dec 31st
• EOMONTH succeeds in impliciting converting the date

Explanation: List of leap years: http://en.wikipedia.org/wiki/List_of_leap_years#1900s

According to http://technet.microsoft.com/en-us/library/hh213020.aspx, the optional integer expression specifying the number of months to add to start_date. If this argument is specified, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then an error is raised. Scroll down to B. EOMONTH with string parameter and implicit conversion

» Discuss this question and answer on the forums

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset

As the most influential thought leaders in the data warehousing and business intelligence industry, the Kimball Group has developed pioneering techniques that serve as industry standards for DW/BI system design, development, and management. With this new edition of their bestseller, veteran experts from the Kimball Group get you up to speed with using the new Business Intelligence release of SQL Server: SQL Server 2008 R2. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, the authors follow the full project lifecycle, including design, development, deployment, and maintenance. Get your copy today from Amazon.

Truncate or switch range of a partitioned table

Federico Iori from SQLServerCentral.com

Script to duplicate structure, switch or truncate choosen partitions of a table More »

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

SQL Server 2005 : Administering

Monitering Solution - How to propose a monitoring Solution for a server..

cluster index and non-clustered index - [b]can any body answer this question ? In what cases developer use cluster and non cluster index . suppose 40,000 rows are...

cluster index and non-clustered index - [b]can any body answer this question ? In what cases developer use cluster and non cluster index . suppose 40,000 rows are...

MSSQLSYTEMRESOURCE NOT IN DATA DIRECTORY LIST - We are planning to migrate our system DB (SQL 2005) on our cluster environment. We followed the instruction given by...

Accessing reports from secondary server: - Hi , I have a question related to our environment. We maintain reporting services and users access it anytime . we have...

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

Backup on network fails - Hi, I am trying to configure SQL server 2005 backup to a another server drive \\server2\folder. The folder on second server...

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 ...

Reporting Services - Limiting Records Per Page - This is probably an elementary question, but for the life of me I can't decide the proper direction to go...

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

What process is consuming my VAS Reservation - I am encountering an error on one of our environments where further logins are not allowed anymore. Only way to...

Execution context and Procedure Cache - Not sure whether i m thinking the right way...I know Sql server stores execution context information in Execution plan but...

Server becomes very slow [RESOURCE_SEMAPHORE] waittype. - From last week our server periodically becomes very slow, I'm getting multiple RESOURCE_SEMAPHORE waittypes. The server box specs are: 4 Quad...

SQL Server 2005 : SQL Server 2005 Integration Services

Data Type Conversion of Excel Source - I would like to import an excel sheet to a DB table which is having around 100 columns having data...

Downgrade SSIS 2008 package to 2005 - I developed and tested 3 .dtsx packages in my development environment using Visual Studio 2008 v9.0.30729.4462 QFE, not realising that...

SQL Server 2005 : T-SQL (SS2K5)

Stored Proc and performance - Is there any SQL that can be added to a Stored Proc to track if there was any blocking of...

Need a Stored procedure - Hello, I need help writing a stored procedure that will take a parameter in as @hostName VARCHAR(100) and using xp_cmdshell,...

Find out the SQL Server restart date/time with TSQL - Hi, I am just wondering if there is way to figure out when SQL Server was last restarted using TSQL? Thanks.

Delete rows from the table using cursor - Hi All, I have used the cursor in the stored procedure to delete the data from the table. I'm deleting the...

SQL Server 2005 : SQL Server Newbies

Converting MySQL Steps to SQL Server - I am installing a product which needs a database - it can use any database as long as there is a...

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

QUERY TUNING - How to Query Tune The Stored procedure.. Any helpful document.. Where to start how to do.. Thanks

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...

SQL Server 2008 : SQL Server 2008 - General

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

Generate scirpt with data from database command option - Generate scirpt with data from database sql server 2008 r2 ( cz database too large) command lime option

Decryptbykey - Hi I am using an encrypted db, I run a select query but certain columns remain encypted. To resolve this I...

Procedure expects parameter 'command_string' of type 'varchar' - hello all. I used this procedure for export to xml file: alter PROC makeXML AS DECLARE @myXml xml,@SQL nvarchar(max) SET @myXml = (SELECT * FROM QMS_QmsBase FOR...

Tail bak up - Is it possible to take Tail backup after the full backup ?

Setting Constraints for a Report Parameter - Hi Friends, I have report which has a Parameter named Account. This Account is a Text Parameter. We can send multiple...

write query results to xml with T-sql - hello all. I need to have one procedure that writes query results to xml. please get me t-sql code for this. I used...

How to copy the ER diagram from SQL server 2008 - Hi all, I wanted to copy the ER diagram to a word file. I generated the ER diagrams but the tables...

Need long running queries from dynamic views - Hi Friends, Is there a query to get long running queries from dynamic views in sql server 2008 R2. The O/P...

Generate sciprt for large table with command - I want to generate sciprt large table with command , i couldnt able to do with SSMS

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

can dml triggers statements get logged - I want to understand since dml triggers are using data modification statements are they logged in log file and if yes...

Error whilst Saving the document as - hello all. I use this procedure for export from sql server to word that be found in http://www.simple-talk.com/sql/t-sql-programming/writing-to-word-from-sql-server/ and changed connection string...

SQL Server Agent Impersonating account - hi....guys What is Impersonating Account in SQL Server Agent ? Please explain me in details and what is the use of this...

findout Database Corruption - any query to Check the log for database corruption.?

Buffer pool - hi all, Where the Buffer pool will be allocated? Is Buffer pool is different from Cache, if yes where this will...

sql queres - Hi friends i have a small doubt in sql server plz tell me how to solve this one How to Concat...

sql query - hi friends i have a samll doubt in sql server plese tell me how to solve this one table data...

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...

sql security - pls any body give some issues regarding sql server security like some login failed error messages Thank you

File group backup - I am new to file group backup and restore .So i am trying something to do with file groups. Below i...

Reindex tables and update statistics - I've been digging in the database that supports our software because it's always been known for having slow performance. I've...

Declare error message before CATCH? - In doing code review, I suggested to a developer to move their error variables that were used in the CATCH...

How to save sp exec result into a temp table - Hello, I have a sp where I created a temp table, I need then insert the result of another sp into...

Partitioning multiple tables of varying ranges - Everyone- I am fixing a partitioning gone wrong implementation of a small number of large tables in a data warehouse. The...

Replication Oracle to 2008R2 - truncated update commands? - I have set up an Oracle transactional publication on a single table as a test. Snapshot works ok so i...

Puzzle, I used SQL to see what happend next. - Some time ago I came across this puzzle. I do not want to spoil the satisfaction of solving this puzzle yourself. So...

comma separated - Hi all, how to get the column names from excel as comma separated string in sql server number of columns will...

TABLE RELATIONS - I am trying to write a program in vb.net that has a master/detail form. I have 2 tables I need...

mail merge in sp - hello all. I want to have a mail merge in sql server without c# code or vb code. for example in storeprocedure...

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...

Monthly Aggregation - I've created following code: [code="sql"] select DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as MonthNo, DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))) as Year, DATENAME(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as Month, COUNT(doclinkid) as Registered, sum(case when REPWPK.T_BMAL.Semua_Status_Kod like 'S%'...

SQL Server 2008 : T-SQL (SS2K8)

QUERY TUNING - How to Query Tune The Stored procedure.. Any helpful document.. Where to start how to do.. Thanks

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...

First Stored Procedure - I have written my first stored procedure to try to pass a table name in a variable to SSRS. USE XXXX SET...

Recommendations on "Sql Server Books" for newbies - Hi Guys, It's a multi choice, so be smart to pick more than one, and help others in their quest for...

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,...

Word analysis with SQL - I did a blog post earlier called [url=http://svangasql.wordpress.com/2012/11/12/pass-summit-2012-twitter-dashboard/]PASS Summit Twitter Dashboard[/url]. It shows all tweets by people at the PASS...

set identity insert on - I'm doing unicode conversion. I am recreating a mirror image of some 100 databases and all their objects. Before I...

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,...

Finding dependencies across databases - Good afternoon... I wonder if anyone can help me with something which, as is often the way, I thought might...

Recursively create Tree Structure - Hello Friends, Following is the DDL, Sample Data for the requirement I have : [code="sql"] --DDL SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE...

Question about rounding the points in a Geometry. - Here's a POLYGON from my MS SQL 2008 database that I pulled to use here as an example: POLYGON ((-64.8040519990027 18.366768000647426,...

SQL Server 2008 : Working with Oracle

where to find dbservername when connecting to oracle from asp.net - hello i have a rather novice question but i cant figure it out myself. when i try to modify web.config...

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 : SQL Server Newbies

User defened Datatypes Default values. - Dear All I would like to know the syntax of adding Default value during UDT creation (Rather than by using...

Secondary indexes in SQL Server. - Hi all, What are secondary indexes in SQL Server and why are they needed.

SQL Server 2008 : Security (SS2K8)

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 Administration

Maintenance Plan for Daily backup for a specific Database Not working as expected - Hi All, i have created a Maintenance Plan for daily FULL Backup of 2 databases in a SQL Instance. The...

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

Tempdb and disk - I am reading a tutorial of Randal Paul that says, Checkpoints don't flush tempdb data to disk. Log records are...

How to manage log space issue, when you have more than 1 log file for a particular database? - Hi All, If you encounter log space issue and if you have more than 1 log file for a database, how...

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

A .NET Framework error in SQL server 2008 R2 - Hi All, one of our System monitor job is failing with the below error. ''[i] Executed as user: REDMOND\MyServiceAccount. Start...

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...

I think I already know the answer but... :unsure: - We have a prior 64-bit SQL 2008 EE install on a server that has a 14-spindle internal array and an...

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...

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,...

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

Difference bewteen Entity.InstanceSelection and Attribute.Value Selection in report model - Hi, Can any one explain me what is the different between InstanceSelection on entity and Value selection on attribute. If i...

IF/ELSE/THEN FUNCTION IS SSRS 2008 R2 - How to add this Function in SSRS 2008 R2 if {Reportname.Fieldname}='yes' then {reportname.Amount1} else {reportname.amount2} This is added in Crystal Report but i...

Report Column headers are not correctly aligning when deployed into ".Net" Web Application - Hi, I have developed a report, i am deploying it into .Net Web Application. In the SSRS the Report Column...

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...

Reporting Services : Reporting Services 2005 Administration

Scheduling - Hi, I want to execute a procedure on the specified time. How can i do this?

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...

MDX Query - Little TROUBLE with 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...