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

Enforce business rules with indexed views and a two-row table

Dan Ryan from SQLServerCentral.com

Here’s a technique that uses a view with a unique index and a dummy table with only two rows to trick SQL Server into enforcing your business rules. More »

The 2012 SQLServerCentral/Exceptional DBA Awards Party at the PASS Summit

Press Release from SQLServerCentral.com

The 2012 SQLServerCentral party at the PASS Summit is on and will once again include the awards ceremony for the Exceptional DBA of 2012. Get your tickets now. More »

SQL Saturday #155 - Lima

Press Release from SQLServerCentral.com

SQL Saturday is exploding in South America, and Lima hosts it's event on Sept 22, 2012. Come join fellow data professionals and learn about SQL Server. More »

SSIS Basics: Introducing Variables

Additional Articles from SimpleTalk

In the third of her SSIS Basics articles, Annette Allen shows you how to use Variables in your SSIS Packages, and explains the functions of the system-defined variables. More »

From the SQLServerCentral Blogs - SQL Audit 301 – Using Powershell to Manage Audits

Today we’re going to go over some very basic scripts to create, drop, and copy SQL Audit objects using Powershell... More »

As a US citizen, I'm lucky to work for a UK company. I get a generous allotment of vacation, and desperately try to use it all each year. I haven't been successful yet, but I'm getting closer. If we could only get a little more snow at the beginning and end of the season.....

Last year a friend told me that they lost 6 days of vacation. Over a few years this person had accumulated extra vacation by basically working too hard and when the year ended, some of their vacation was lost because of carryover limits. This person vowed to change this year and use all their vacation, but with a busy job at a small company I'm not sure that will come true. Unfortunately, I've all too often seen this same behavior from many people in IT.

This piece shows that all too often people are not taking their vacation, or they're not taking it as a complete break from work. I've been in both situations, and over time I've learned that I'm not being more productive in either case. I'm unbalanced, and over time I become less productive. That's especially true if I'm doing creative work, like writing, or developing code.

There's no shortage of poor managers out there, and there are plenty of them that will work you as hard as you allow. I don't have any great solutions for dealing with them other than scheduling vacation every year, insisting you get to take it, limiting contact with work, and if all else fails, looking for another job.

Life is short, too short to spend more of it as work than you have to. Take advantage of your vacation to relax and recharge, even if it's a stay-cation at home. Getting away from work is not only important, it's something you owe to yourself and your family.

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 - 18.0MB WMV
• Watch the iPod Video Podcast - 17.0MB MP4
• Listen to the MP3 Audio Podcast - 3.5MB 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:

Table A

Select Count(*)
 from [Table A]

Table A has 10 rows including 3 Null rows.

Table B

Select Count(Column Name)
 from [Table B]

Table B has 7 rows including 3 Null rows.

What will the result?

By Naseer Ahmad

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.

How many distinct T-SQL constructs can assign a value to a regular identifier with a leading '@' without using SET nor SELECT?

By Jack Hummer

Answer: 8

Explanation: Probably too much to hope we can avoid a big discussion on 'distinct' constructs, but here is how I count them:

1. An Input argument to a Procedure or Function - http://msdn.microsoft.com/en-us/library/ms187926
2. An Output argument to a Procedure - http://msdn.microsoft.com/en-us/library/ms187926
3. EXECUTE a Function: EXEC @return = udfFunct() - http://msdn.microsoft.com/en-us/library/ms188332
4. The Stored Procedure return status: EXEC @status = uspProc (This is very different from a Function return.) - http://msdn.microsoft.com/en-us/library/ms188332
5. The OUTPUT clause: OUTPUT INTO @tablevar (Could count this 4 times but it is really one construct) http://msdn.microsoft.com/en-us/library/ms177564
6. RECEIVE .... FROM INTO @tablevar - http://msdn.microsoft.com/en-us/library/ms186963.aspx
7. FETCH NEXT FROM cursor INTO @varname - http://msdn.microsoft.com/en-us/library/ms180152
8. DECLARE @varname INT = 0; - http://msdn.microsoft.com/en-us/library/ms188927

Ref: Definition Regular Identifiers - http://msdn.microsoft.com/en-us/library/ms175874.aspx

» Discuss this question and answer on the forums

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!

Display Names and Locations of SQL Database Files and DB Owners

Charles Brinkman from SQLServerCentral.com

Displays the names of all of the SQL databases on a SQL Server instance, their data and transaction log files, their owner, and their physical locations. More »

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

SQL Server 2005 : Administering

Have to enter the port to connect via management studio - A strange problem has cropped up in our enviornment that I'm kind of scratching my head about. Why all of...

When we talk about fragmentation it's both Table as well as Index fragmentation, is it ? - Experts, Silly question... When we talk about fragmentation it's both Table as well as Index fragmentation right ? I always see posts...

removing publications - Hi, I've inherited replication both merge and transactional. Right now, I've known that much of it is not in use,...

directory lookup for the file failed with the operating system error 2 - Dear All, I am getting a problem.I have SQL server 2005 installed on C:\..drive.and I have S:\ drive as a LUN. Now...

SQL Server 2005 : SQL Server 2005 General Discussion

High Space Used in Database - I am getting alerts for [b]high space used in database 90%[/b] in my one of the production server. Can any...

SQL Server 2005 Agent will not start - The agent service is configured to use a service account, as is SQL server itself. SQL starts just fine so...

SQL Server 2005 : SQL Server 2005 Integration Services

Data split while loading into CSV - Hi, I am loading data from sql server to a flatfile (.csv format). One of the column will have values like =HsGetValue("GPDetail","Scenario#Actual;Year#2012;Period#P07;View#YTD;Entity#G1G2_0609000;Value#<Entity...

SQL Server 2005 : T-SQL (SS2K5)

Tlog space getting full whcle executing the Update statement - Hi All, Could you please assist on tlog space issues. I am trying to execute the query have update statement like...

SQL Server 2005 : SQL Server Newbies

need to fetch the value between two delimiter - declare @text varchar(max) = 'MSH|^~\&|||||21110428134755-0500||ADT^A04^ADT_A01|21110428134755|P|2.5.1|||AL|NE EVN|A08|21110428133821-0500||||21110428133821-0500 PID|zdly100001|zdly111000^556|PATID1234^5^M11|6g7g7|JONES^WILLIAM^Aryan^III^Dr.^MBA|Singh|19621225000000|M||2106-3|1200 N ELM STREET^^GREENSBORO^NC^27401-1020^TX|GL|(919)379-1212^^^test1@gmail.com^TX^324^5656568^121|(919)271-3434~(

SQL Server 2008 : SQL Server 2008 - General

Strange Management Studio 2008 R2 (10.50.1600 and 10.50.1617) behaviour - Using management studio (10.50.1600 or 10.50.1617.0) Connecting to named sql server instance (lets call it [DEV-SQL\SqlKing]) version 10.0.4279 --> Cumulative update package...

How to mask a field? - Hi, I have a table with 10 columns Out of 10, i need to mask 5 columns because i don't want to...

HAving Brain fade on a simple SQL query - Hi all, I'm having an issue remembering how to do this (simple) query in SQL. I have a view that pulls from...

Insert into table from temp table - In my stored procedure, I create a temp table from table A. Then I modify data on the temp table....

log for database is not availabe - Hi, I am getting the below error error message. The log for database 'Commun' is not available. Check the event log for...

point-in-time restore of a database requirements - Hi all I have a question about a point-in-time restore of a database. I thought I understood all about backups years...

How to get sub total on non-numeric field in SSRS? - I want to get the sub total for say non numeric field Month. So how can i get it in...

Middle Number Needs GROUP BY - I can't figure out the GROUP BY. This result should be: A 2 B 7 C 8 Thanks! [code="sql"] IF OBJECT_ID('tempdb..#TempMid') > 0 DROP TABLE #TempMid CREATE TABLE...

datetime conversion challenge - How do I convert this date '19/10/2011 2:00:00 PM' to a standard datetime value?

Go from SQL 2008 R2 standard edition to Developer? - Is it possible to 'downgrade' SQL 2008 R2 from standard edition to Developer edition? I'm working on developing a long-term...

SQL Server 2008 : T-SQL (SS2K8)

A printable list of T-SQL commands - Where would I find a list of T-SQL commands and reserved words? The number of reserved words and system functions like...

How to combine tables in one straight row? - Hello friends, I hope every one is in good condition upon reading this forum. I am finding a hard time combining...

Improving query performance to detect first duplicate - I need to query some tables looking for All Sales that are active at the same time and have at...

how to change the date value to null?? - hi , I have a column with date datatype. i updated the column with some date like 02-02-2012 00.00.000 ,for 100...

any ideas on how to spead up this query? - As the title says select id , count(distinct company) as CompanyCount , count(distinct district) as DistrictCount from Tracking group by id thanks in advance

comma separate value show as table - I have One table tbaleOne Column1 ------------------------- AA,BB,CC DD,EE,FF GG,HH,II I need out put col1 col2 col2 --------------------------------------------------------- AA BB CC DD EE FF GG HH II

Convert Text to Date DataType - Hello Everyone, I have an sql table which stores dates from some other system as text. Eg.: DD/MM/YYYY ,31/12/2012. I have made...

SQL Server 2008 : SQL Server Newbies

Insert into one table from multiple tables - Hi All, Just needed a bit of help with an insert query. I have 3 tables that I need data from each...

SQL Server 2008 : SQL Server 2008 Administration

Unable to enable FILESTREAM feature of SQL Server 2008 R2 on failover cluster - Setup: SQL Server 2008 R2 Enterprise Edition x64 (10.50.1600) Windows Server 2008 R2 Enterprise x64 When attempting to configure the filestream feature through...

How to mask a field? - Hi, I have a table with 10 columns Out of 10, i need to mask 5 columns because i don't want to...

Db Snapshots question - Hi, I have a question on database snapshots. I have created a table and inserted 2 rows. create table tab7...

Transaction Log drive in simple recovery - I'm creating a database on a server with 3 hard drives. One is mainly for the OS and I shouldn't...

Synchronization of 2 different DB's - Hello, there are two similiar databases given. They are widely even identical though have some different columns and tables. I am looking...

Splitting a huge, a huge table in two or three - Ok, I've seen big tables and databases before, but this one is really big. I just recently discovered that the reason...

get a SQL alert - I just got a SQL alert: What does it mean, I see it generates some errors in sql log, see attachment. It...

Career : Certification

Passed 70-433 yesterday (yay!). Now 70-451 or short path to MCSA 2012? - Hi guys and girls. I passed the 70-433 exam yesterday on the second attempt. I missed out by 2 questions...

Programming : XML

XML Data Type .modify() Method in an update statement? - I have an column of XML data type in a table. I need to change the text() node value on...