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

Generating SubTotals using GROUPING

Sunil Chandurkar from SQLServerCentral.com

This article explains how to use the GROUPING clause to generate subtotals for rows in a very easy fashion. More »

Day 7 of The OLAP Sprint

Simon E Doubt from SQLServerCentral.com

The final day of Simon's journey to improve his OLAP knowledge and build a prototype cube. More »

SQL Server 2012 AlwaysOn: Multisite Failover Cluster Instance

Additional Articles from Microsoft MSDN

SQL Server Failover Clustering, which includes support for both local and multisite failover configurations, is part of the SQL Server 2012 AlwaysOn implementation suite, designed to provide high availability and disaster recovery for SQL Server. The multisite failover clustering technology has been enhanced significantly in SQL Server 2012. The multisite failover cluster architecture, enhancements in SQL Server 2012 to the technology, and some best practices to help with deployment of the technology are the primary focus of this paper. More »

SQL Saturday #168 - Tampa BI Edition

Press Release from SQLServerCentral.com

A free day of SQL Server Business Intelligence sessions in Tampa, FL tomorrow. More »

From the SQLServerCentral Blogs - Time Pass With Transaction Log - Part 2 Basics

Ramkumar (LivingForSQLServer) from SQLServerCentral.com

As part of this Transaction Log internal series, In this article We are going to revisit some basic properties of Transaction log.Though architecture, purpose... More »

One thing that we can be sure of as data professionals: we will need more storage over time. We gather more data in our organizations and more data is sent to us by partners or automated systems. At the same time we must ensure all our existing data is always available for users. I don't know how we solve any of these problems, but I do know that any solution will require likely more storage in the future.

As DBAs, we aren't too often involved in the cost of storage. These days as many companies move to SAN-based storage, there are separate administrators are involved in the specification and purchasing of the actual hardware. That means that as DBAs we are removed from the impact of our requests for more storage. We don't necessarily know what the cost to the organization is when we request another 400GB LUN. However this week I'm curious if you know the answer to this.

What's the cost of storage in your organization?

I'm wondering if you know the actual cost in dollars or the impact to limited IT resources of enterprise level storage in your company. Does this impact the way you do business as a DBA? We often don't have control over the growth of our data, but we seem to regularly be pulled into the discussions on how to best manage the amount of data and even archive data or reduce its impact on the organization's storage.

Let us know this week if you are impacted by the cost of storage in your organization.

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 - 14.4MB WMV
• Watch the iPod Video Podcast - 16.5MB MP4
• Listen to the MP3 Audio Podcast - 3.3MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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

Today's Question:

What is returned from #tblTrans when you run the below code.

CREATE TABLE #tblTrans(RowId TINYINT)
GO
BEGIN TRAN
	DECLARE @getId TINYINT 
	SET @getId=100
	INSERT INTO #tblTrans (RowId) VALUES(@getId)
	IF @getId >10
	RAISERROR('RowId should not be greater than 10',11,16)
	PRINT @@ERROR
IF @@ERROR = 0
BEGIN
	COMMIT TRAN 
	PRINT 'I am here at commit!'
END
ELSE
BEGIN 
	ROLLBACK TRAN
	PRINT 'I am here at rollback!'
END
GO
SELECT * FROM #tblTrans

By Raj

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 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

CREATE TABLE #QoTD 
(
  WithOutDefault int,  
  WithDefault varchar(25) DEFAULT 'default'
) 
--1
INSERT INTO #QoTD (WithOutDefault) VALUES (1) 
--2
INSERT INTO #QoTD (WithOutDefault, WithDefault) VALUES (2, NULL) 
--3
INSERT INTO #QoTD (WithOutDefault, WithDefault) VALUES (3,'DEFAULT') 
--4
INSERT INTO #QoTD (WithOutDefault, WithDefault) VALUES (4,DEFAULT) 

Which of the insert statements will use the default on column WithDefault? (select 2)

By Kenneth Fisher

Answer:

• 1
• 4

Explanation: BOL says "Defaults specify what values are used in a column if you do not specify a value for the column when you insert a row." about defaults. Even though NULL is technically the absense of a value in this particular case if you insert a NULL into a column with a default then the column will contain a NULL. The keyword DEFAULT causes the default value (if one exists) to be inserted into the column.

Ref: http://msdn.microsoft.com/en-us/library/ms187083(v=sql.105).aspx

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

Dynamic Transaction Log Backup

Mitch Spruill from SQLServerCentral.com

Backup up all transaction logs based on sys.databases. More »

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

SQL Server 2005 : Administering

Backup size confusion - My backup sproc logs the start and finish of a database backup. It then updates my BackupSchedule table with the...

SQL agent failed job detailed informtion??? - Hi Team, We configured different jobs in each sever(full,diff,tlog,dbcc and rebuild \reord,history cleanup jobs). We are getting alerts when the job...

SQL job on startup - Hi All, How could I run a sql job or maintenance plan on server startup? Thanks

SQL Server 2005 : Business Intelligence

Can I Enable/Disable a date parameter? - Hi All Using SSRS 2008. I have a report where a user can view either open or closed jobs. If they're viewing...

Average in ssrs 2008 - Hi, I want to find the average in my text box.i have my code(conversion) in my report properties(custom code) where i...

SQL Server 2005 : Data Corruption

Finding a possible corrupt bit - To All I have a table of the following definition [code="sql"]USE [CISPROD_DEV] GO /****** Object: Table [ADVANCED].[BIF041] Script Date: 11/14/2012 13:00:56 ******/ SET ANSI_NULLS...

SQL Server 2005 : Development

How to copy production DB into New Developmant Database? - Hi, We have Sql server 2008 R2 and i would like to set up my new test server and copy production...

xml - hi well am on fire rite know ,,,,,,:D anyways i am new to sql not as expert as you guys but...

SQL Server 2005 : SQL Server 2005 General Discussion

Automated Trigger a SSRS Subscription - Hi All, i have requirement here to trigger a SSRS report as soon as our ETL Jobs are completed. I...

How to solve this issue sqlserver.exe process is taking so much of memory [70%] - HI to all, How to solve this issue sqlserver.exe is taking so much of memory [70%] in task manager --->in process -->...

Failed SQL Server 2005 Cluster Upgrade to SP4 - I am looking for some suggestions to a problem that we ran into upgrading our SQL Server 2005 Cluster to...

Try Catch with Bulk Insert - Hi All, I am having some trouble getting the following code to work, in 2005. I am trying to load a...

SQL Server 2005 : SQL Server 2005 Integration Services

audit table with pacakge info - Need to create audit table with data for each package exectution source excel filename, source row count, insert row count, error...

SMTPClient question - I have a package using the Send Email Task. On the SMTP connection, I have the smtp server specified and the...

Import Multiple XML files into Sql Server using SSIS - Hello All... I have a set of XML files which I need to import into database on Regular basis(after validating some...

SQL Server 2005 : T-SQL (SS2K5)

Conditional Delete not working - Why does this delete all my records, reguardless of company? delete Plant_Master where EXISTS(Select Plant_CD from Plant_Master where company like 'MSC')

WHERE Filter - Hi to all. I haven't posted for a while and it is now time to wear my sql cap again...

automate t-sql - I'm not an expert at T-SQL, so I hope someone can help me automate this. Currently, I'm doing this manually by...

Select statements included within a function cannot return data to a client - Hi, i'm trying create on function, but the sql print: Msg 444, Level 16, State 2, Procedure Retorna_UltimoLog, Line 9 Select statements included...

Rand() function error - CREATE FUNCTION ufntGenerateRandValue ( @minValue int, @maxValue int ) RETURNS int AS BEGIN DECLARE @randNumber int Set @randNumber = Cast(RAND() * @maxValue + @minValue AS INT) RETURN @randNumber; END GO This function compiles without error...

SQL Server 7,2000 : Administration

Log Shipping monitor scheduled job - HI all, We have a scheduled task that I believe is redundant (due to us having 3rd party monitoring software) and...

SQL Server 7,2000 : Replication

Transactional Replication - Snapshot is not getting applied to subscriber - Need Help: I have looked everywhere but I cannot find help for this specific topic. Any help to pinpoint the problem...

SQL Server 7,2000 : T-SQL

Query-restrict output - I have a system that logs users who use the system daily. Some user may log in several times during...

SQL Server 7,2000 : SQL Server Agent

grant permissions to view SQL Server agent. - Hi all, I am not able to see the sql server agent. But, when I login as a sysadmin I am able...

SQL Server 2008 : SQL Server 2008 - General

Used Pages vs Data Pages - Hi, I executed the following code to determine the size that a certain table takes no the database: [code="sql"] select o.name AS Table_Name, i.index_id,...

combining result in stored procedure ?? - I have problem : I have to 2 select queries , which is called inside 2 separate procedures. But i have to call...

Query failed (aggregate function or the GROUP BY clause) - SAMPLE QUERY : SELECT name, SUM(population) FROM bbc where population >1000 query failed and got error msg 8120 ERROR "Column 'tt.dbo.bbc.name' is invalid...

Performance hit on Indexing (I Think) - Hi guys, I am looking after / building a solution that loads data from flat files using bulk insert. The file contains a...

EXECTE permission on function - Hi, HOw to grant execute permission to all "FUNCTIONS" on the database for particular "USER" ? please provide the script or command...

Query Optimization - Hi guys, I have one query which is listed below which takes 5hrs mininum to execute it.. please help me on...

Would it be possible to load the XML into an XML variable (perhaps in a function), and return name/value pairs? - Not required...

- Would it be possible to load the XML into an XML variable (perhaps in a function), and return name/value pairs? - Not required

Partitioned compound clustered indexes - Hi I have a table with 125 columns, and 250 million rows. Unfortunately the ID column is NVARCHAR(15), and is non-unique....

Get Left-most Char of Col A to insert into Col B - Hi experts, For example: Col A Varchar(6) contains FGHIJK Col B char(1) I need to insert the first character - F in this...

Help needed - Hi Gurus, I am new to SQL server world. I need your valuable suggestion and help to have an approach for...

Need help with the below procedure - From: "Mishra, Abhinash, Vodafone UK, Partner" <abhinash.mishra@vodafone.com> To: Abinash Mishra1/India/IBM@IBMIN, Date: 11/15/2012 08:06 AM Subject: proc USE [R33HypInfa] GO create procedure DF012_Validation AS BEGIN update [R33HypInfa].[dbo].[DF012_HFM_Staging] set ICP=(select B.ICP from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B Wh

SSRS 2008 generating reports from snapshots - Hi, I have a requirement to generate same reports with different parameters and have it run from a snapshot. Is there...

concat string with numeric - I know this is pretty basic ... but how do I concat string and numeric? I wanted Client.LName + ', ' + Client.FName + ' -' & client.ID as ClientName where...

Covering index on persisted computed column being ignored - Hi All, I came across this rather odd bit of behaviour so I thought I would share it. Either I'm missing...

Scripting out permission for database? - Hi All, moving databases from Prod to new server. And also want to carry forward permissions. Need help on moving...

SAN drive. - 1)How to identify if the disk displayed in My Computer is local or a SAN drive on win 2003 & 2008...

Order by Date column - Hi, I have query to execute the output. It should be sorted with date also, but its sorting with only Seq_no. PFA - for...

SQL Server communication over subnets - Hi Folks, OK, the problem is that I am trying to configure two SQL Server 2005 machines to communiacte over two...

SQL IO Performance - I'm running out of brain cells trying to figure out what I've overlooked. Hoping someone has a quick checklist of...

Trigger on Insert for a unique row - Hi All, i'm looking to try to create a trigger that will just create a txt file in a folder when...

Continuing career with SQL - Hello, I hav just joint this forum. I have to ask few things. I have worked with SQL 2000 and basic 2005.database design. I've...

Setfocus - I'm looking at moving in a different direction with my career. Thinking about becoming a SQL Developer. Does anyone know...

source unpivot(PIVOT and UNPIVOT) - Does anyone know the source unpivot syntax? I cannot find it from google. Thanks!:-P

xp_cmdshell access denied to desktop file when logged as administrator - why ? - I am logged in as an administrator and each time i run the following query exec master.dbo.xp_cmdshell 'dir c:\users\administrator.mydomainname\desktop\resumes\*.*' I get...

SQL Server 2008 : T-SQL (SS2K8)

Avoiding cursor: Help with getting only first match after previous match - Hi I'm sure there is a better name for this problem, and its probably quite a common one with a...

Traversing through Temp Table - Hi, I have i temp table as [code="sql"]CREATE TABLE #TEMP (ID INT)[/code] In this temp table i would be having 2 or more...

Calculating absence instances - Morning all, I have an issue and it is driving me crazy, so was helping someone may be able to help...

Need an Efficient Way to Rank Rows with Some Rows Having the Same Rank - Does anyone have a suggeston on the most efficient way to do this ranking? I need to add an incremental...

Trying to calculate employees working pattern - Hi all, I am having a bit of a problem trying to calculate an employees working pattern i.e. if I specify...

Debugging a loooong query - Hi all, I have the pleasure of inheriting a select statement that has multiple subqueries and unions in subqueries, and all...

Exercises in t-SQL - I'm in the process of setting up a training environment here, it's a mechanism for me to bring high flyers...

SQL Server 2008 : SQL Server Newbies

Minimal installation requirement for best performance - Hi, I'm running an Windows 2008 R2 Server with SQL 2008 R2. Wich windows services are unnecessary on my host and can...

last job that ran - Hi all, If I have a job that executed twice, how can I, using t-sql, find which one executed last? Thanks, Eugene

Improving intentionally denormalized table - I had a job interview about two weeks ago for a position for prviding support to teams of public health...

Can't restore transaction logs - Hi Everyone, I cannot restore the transaction logs after restoring the full backup. It shows the transaction log is too early to...

SQL Server 2008 : Security (SS2K8)

Error while logging Performance Monitor counter to sql server - I am trying to do following task and I donot know it is possible in this setup or not? I am...

SQL Server 2008 : SQL Server 2008 High Availability

SQL servers service failure - During failover from NODE02 to NODE01, as part of our patching activity, we found that SQL Server services are failing...

How to check stored procedure was last modified in sql server 2000 - HI, How to check last modified partiulare stored procdure? Any t-sql query ..? Please anyone help me?

SQL Server 2008 : SQL Server 2008 Administration

Dynamically managing SQL Memory - Heres the scenario, I've recently come across involving a multi-instance SQL server server. The server itself hosts a multi tiered DW,...

SQL Buffer Manager Size - Hi Guys I use the following script to assess the current size used by my plan cache and the data cache [code="sql"]select...

Issues with Database miling - Hi all, Am getting the error frequently when i attempting to send a mail from SQL: Please let me know to...

Size of Log file too big - I have discovered yesterday that the size of the log file on my database has increased to 55 Gb while...

Upgrade Database from SQL Server 2005 to 2008 R2 using Restore w/StandBy - I tried to restore a SQL Server 2005 Database backup to a SQL Server 2008 R2 instance and place the...

What would be the best RAID configuration for this hardware? - We have a brand new DELL MD1200 with 12-spindles - These are 15Krpm, 600GB SAS drives The server this is going to...

Wait Types concern - Hi Experts, In one of our DW servers the performance is very slow that a simple select statment is taking hours....

SQL Connectivity not stable & jobs getting failed. - Dear All, We have a server with MS-SQL 2008 64-bit edition. It is working fine till last month & we did...

Career : Certification

Querying Microsoft SQL Server 2012 Training Kit (Exam 70-461) publishing delay - I've been waiting patiently for this training kit coming out for months now, expectantly waiting for it's arrival end of...

SQLServerCentral.com : Anything that is NOT about SQL!

Is there any good database magazine out there? - Hi there, can you recommend a database magazine that covers all database environments (MySQL, MSSQL, NoSQL, everything...? We had a good bi-monthly...

Not enough work? - Just curious, has anyone ever had to quit a job because they just can't keep you busy?:doze:

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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

Report using multiple datasets with No common fields - Hi All, I am starting out and I am struggling to get my head around it. I'm having multiple shared datasets which...

SSRS 2012 configuration issue - I installed SQL 2012 Reporting Services – SharePoint and RS add-in for SharePoint products on separate server from the central admin...

How can a report user browse a cube on a report server web site - So If I build a SSAS cube, what is the best way to present it to my end users? Is...

Change a Report Owner - An employee left and they are the owner of reports.  Now the reports won't run.  Is there anyway to change...

Reporting Services : Reporting Services 2005 Development

Parameter Is Not Valid on a Chart - I am trying to implement a connection between two reports - the first being a pie chart and the second is...

Database Design : Disaster Recovery

Data Warehouse Disaster Revcovery Options - I'm in the process of implementing a Data Warehouse. I have a Staging Database and a DatawArehouse Database. I have data feeds...

Database Design : Design Ideas and Questions

Lots of Key Lookups vs. UniqueIdentifier Clustered Index - Hello. I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes. Later the clustered indexes were rebuilt on...

Database Design : Virtualization

Sql 2008, Task Manager, and Hyper-V Guest - I was told that one can not expect reliable results using Task Manager inside a VM (Guest). If that is...

Data Warehousing : Integration Services

variable set in design time but not run time - Hello, I have a package with two global variables. One is an ID to identify a customer; e.g., 215. The other...

SSIS 2012, SSISDB Catalog and ServerStorage protection level errors. - I'm using SQL Server 2012 Enterprise Build 11.0.2100. I have about 200 packages that move data between servers...typical ETL. Sometimes...

SSIS - Effect of Maximum Insert commit Size settings - Recently I was designing SSIS for loading data from OLTP to DW database. As per the requirement the bad data...

Warning: Could not open global shared memory to communicate with performance DLL - I'm a Local Admin on this Database Server but I get the following warning? [SSIS.Pipeline] Warning: Warning: Could not open global...

Data Warehousing : Analysis Services

Multiple dimensions from 1 table or 1 dimension with multiple hierarchies - Being accustomed to Cognos DMR (ROLAP based technology) I am trying to wrap my head around SSAS. My pilot consists of...