In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. Download a free trial now.
 
SQL Backup Pro Get compressed, verified, secure SQL server backups
Use SQL Backup Pro's automated scheduling to get faster, smaller backups. Then verify your restores using DBCC CHECKDB in one easy, automated process. Download a free trial now.
 
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.

In This Issue

A Check and Foreign Key Constraint Improves Query Performance

Check and foreign key constraint do not degrade performance but actually improve it. More »


Unpack an Integer with SQL Server Recursion

Starting with SQL Server 2005, developers have had recursion available as a T-SQL language feature. This article describes recursion and its SQL Server implementations, complete with examples. It also includes SQL Server functions and a stored procedure that unpacks, or parses, an integer into its multiple-of-two components. More »


Registration is open for SQL Relay

SQL Relay is an annual community-run series of SQL Server training events across the UK. SQL Relay will run 8, 1-day conferences over the course of 2 weeks starting June 17th. Speakers from Microsoft, global IT companies, and a host of MVPs, are covering topics on DBA, business intelligence, and development. More »


From the SQLServerCentral Blogs - Does TDE really work on MDF files?

Yes, it does. However, let’s prove it. First let’s create a database, a table, and enter some data: -- create a... More »


Editorial - The Vacation Dilemma

This editorial was originally published on June 12, 2008. It is being re-run as Steve is traveling.

In IT many of us often don't take enough vacation. There are many tales and war stories of how hard we work, and all too often, tales of people that carry over vacation because they can't or don't find the time to get away from work.

I'm as bad as anyone, and not having had an HR department for 4 years, I've rarely taken a day off in that time. Including weekends! Sure I've had some vacations with the family, but more often than not I've had a laptop to check on work, get the newsletter out or something else. Last summer I went to Mexico and I think that was the first time I'd been unwired for more than 2 days in 4 years.

In the past when I've prepared for vacation from SQLServerCentral.com, even if I was available, I've pre-written editorials and basically had everything done before I left so that whoever was covering for me had very minimal work. So in essence, I haven't had much of a vacation since I did the work ahead of time, doubling up for a week or two to be prepared.

So for this Friday's poll...

Should you have to get extra work done before or after vacation?

I'm not really talking about finishing something for a deadline or getting your normal work completed. Instead I am wondering if you need to get more work done that covers for your time off. So the week before or after vacation, do you need to do more than normal work?

My feeling is that if you have to do extra work, you've lost some of the value of the vacation. If people leave your work and you have to do extra when you get back, the same thing occurs. You can't really recharge or refresh if you find yourself stressing about extra work.

This year I'm making an effort to get away and let someone else handle the load. So you'll see more guest editorials, or perhaps a few days with just "Steve's on Vacation" in this spot :)

What do you think? Should you have a real break when you go on vacation? Should work be assigned to someone else so you can enjoy yourself? Should work just get delayed so that vacation doesn't cause any extra effort on your part? Let us know.

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


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). 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.

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


Question of the Day

Today's Question:

---Statement 1
CREATE TABLE table1
(
ID int PRIMARY KEY,
Name varchar(20) 
CONSTRAINT UN_Name UNIQUE(Name)
);
--Statement 2
CREATE TABLE table2
(
 ID int PRIMARY KEY,
 PersonName varchar(10) REFERENCES table1(Name)
);
What will be the output of both queries?

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.


Yesterday's Question of the Day

When running the code below on SQL Server 2008 and above, what will the final query (with the comment "Predict the output of this query") return?

CREATE TABLE dbo.Test
   (PrimKey int NOT NULL,
    ValueCol int NOT NULL,
    PRIMARY KEY (PrimKey)
   );
go
CREATE TRIGGER TestTrig
ON dbo.Test
FOR INSERT
AS
IF (SELECT COUNT(*)
    FROM   inserted
    WHERE  ValueCol < 0) = 1
BEGIN;
  RAISERROR ('Negative values are not allowed!', 16, 1);
  ROLLBACK TRAN;
END;
go
-- Insert attempt #1
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (1, 1);
go
-- Insert attempt #2
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (2, -2);
go
-- Insert attempt #3
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (3, -3), (4, -4);
go
-- Predict the output of this query:
SELECT COUNT(*) FROM dbo.Test;
go
DROP TRIGGER TestTrig;
go
DROP TABLE dbo.Test;
go

Answer: 3

Explanation: An insert trigger fires once per execution of a statement that may cause rows to be inserted. During that single execution, all inserted rows will be in the inserted pseudotable. Coding triggers that only work correctly if a single row is inserted is very bad practise. Even if the application currently inserts rows one at a time, you never know if that changes in the future.

In the case of this question, insert attempt #1 succeeds (as there are no rows inserted with ValueCol below zero). Insert attempt #2 fails (the negative value in ValueCol causes an error message and a rollback). This aborts the batch, but the next batch (starting at the next "go") will execute normally.

Insert attempt #3 then succeeds. Both inserted rows violate the business rule, but the trigger only causes an error message if the number of violations is exactly one. The correct way to code this would have been to use IF EXISTS (SELECT * FROM inserted WHERE ValueCol < 0). Incidentally, this would also have been more efficient.

Reference: http://msdn.microsoft.com/en-us/library/ms190752.aspx

» Discuss this question and answer on the forums


Featured Script

Change DB Owner to sa for multiple DB's

Changes DB owner to sa for Online DB's where owner is not sa 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

cmd line rename syntax error - argh - Hi there, I have a SQL job that produces output.txt then I need to rename it with date suffix. I'm producing...

Generating output to text file from SP via SQLCMD - Hi all, I have created a stored procedure to report on login mappings which I want to be able to run...

Database exception error - Hi, We have an application which access data from SQL DB (SQL server 2005 std. edition). Around 40 members use the...

Access Control of SQL Login - Dear, I have created several logins such as ACC, SALES, HR, DIST, ADT and IT. Now I want that the respective...

Sorting Rows - [size="1"]Dear, I have a product table containing columns ProdID, ProdName and ... The data type of [b]ProdID[/b] is [b]varchar(10)[/b]. It contains data...

Read transaction log file - Hi, We have an issue where someone had fired a delete query on a table which did't have any audit...

Database mail and operator not working together - Hi I have set up DBMail for sending out mails during success of a job.but when i run the job...

SQL Server 2005 : Backups

Log backups while Full/Differential backups are running - A few weeks ago I was at an event where someone came up and asked me if it's bad to...

SQL Server 2005 : Business Intelligence

Look Up making more time - Hi Friends, I am processing 200,000 data into a table. Inside data flow , I use look up component where the...

SSIS-Data in multiple .csv files to columns in same csv file - Hi Everyone I have the below problem,could anyone please suggest me a solution. file 1 has below data 1.csv A B C 2.csv E F G the data in the output...

SQL Server 2005 : Development

Problem with trigger - Hi Everyone, I have a problem with triggers. Trigger must be fired and update COLUMN_A in TABLE2 after insert COLUMN_B...

Counting NULL values in a Pivot table - I have the following query: select type, istatus, count(logical_name) from dbo.devicem1 group by type, istatus order by type, istatus The possible values...

SQL Server 2005 : SQL Server 2005 Security

disable remote access - I am trying to disable remote access for a SQL instance Here is what I did in SSMS, Right click server instance->...

SQL Server 2005 : SQL Server 2005 Strategies

Random number generator - Ok.Guys I need your help. I have a table of about 60000 records. I want to add a column say...

SQL Server 2005 : SS2K5 Replication

Could not connect to 'srvrXYZ' server because 'srvrXYZ' is not defined as a remote server - Hi Experts I am trying to delete my publication from my Publication server and i am getting error. I have...

SQL Server 2005 : SQL Server Express

SQL Server 2012 Instances - TITLE: Microsoft SQL Server 2012 Service Pack 1 Setup ------------------------------ The following error has occurred: Cannot create a new Service Broker in read-only...

SQL Server 2005 : T-SQL (SS2K5)

column update based on calculation and group by - Hi, I need to update the Perc column in Table_A This is based on the following calculation: ((Pass/VM_Id) * 100) and this needs to...

SQL Server 7,2000 : Administration

Detecting Detached Databases - I've many databases on the server I've inherited. Some are detached and rather than manually check each db to see...

SQL Server 2008 : SQL Server 2008 - General

Problem With BackupDB for Deleted Info - I have two database AgencyDB(primary DB) and AgencyBackupDB(Deleted Info from AgencyDB by user) and i have this three table in...

identity insert - Hi, when I am insrting data from a csv file I am getting error: 'Explicit value must be specified for identity...

Problem with audit trigger - Hi, Have just created an audit trigger which will fire whenever a row is inserted, updated or deleted from a...

Combining two stored procedures to get one result set. - I have got two stored procedures as below: [code="sql"] ALTER PROCEDURE [rept].[systemActivities] -- Add the parameters for the stored procedure here @client VARCHAR(50), @startDate DATETIME, @endDate...

SQL Server 2008 Merge Replication: Metadata Cleanup Failure - We are an issue, related to the metadata cleanup. Some time ago we reduced the retention period down from the...

DBCC DBREINDeX - Good Day. I noticed that DBCC dbreindex is still available in 2008R2 . Wehn we run this utility to rebuild PK Custered...

to find specific position string - Hi friends i have small doubt in sql server , table data contains like id , name 1 , srinivas 2 , ravikumar 3...

Indexes on a Query with a pivot function - Hi all, Many thanks to the poster that helped me clear up a misconception that I was having with indexes a...

Fragmentation - Hi all. I found there was fragmentation in my server and i rebuild the indexes to remove fragmentation. But even...

Trouble shoot - what are the ways to trouble shoot replication : how to resolve primary _key violation in transactional replication ?

Compare data between rows of same table - create table ##Temp1 ( myid int identity (1, 1), mytab1col1 int, mytab1col2 varchar(10), mytab1col3 smalldatetime) insert into ##Temp1 (mytab1col1, mytab1col2, mytab1col3) select 1, 'name1', '2010-01-01' union all select...

How to update order status based on detail information - I have 2 tables ( orderheader and ordershipmentdetail). The order can be closed when all products and their requested quantities are...

sql query - Hi friends i have small doubt in sql server , how to write an sql query for getting employee names for...

Cannot create an account in my SQL - I have a sql server 2008R2 in domain A, I have a domain trust (full forest trust) between domain A...

Database Mail - I am trying to get my database to send emails but I can not get it to work. From everything...

Error while taking backup for a user not having sysadmin permission - I am accessing my sql server 2008 r2 express server using SSMS. I have about 5 logins. For a particular...

Error converting varchar to numeric only in where clause - Hi experts... I have a problem with one of my SQL Server query's... First of all, we are in SQL Server 2008...

queries on Logins - Hi If I execute the stored procedure multiple times on same day, the first time category should not repeat for the...

Backup taking too long after migration to SQL 2008 R2 - Hello, We recently migrated databases from SQL 2005 to SQL 2008 R2. It's a nice shiny hardware, much better than the...

Need Computed Column / Trigger / Function to insert data from another table - Hi, I've got the following Problem, which I'd like to solve in SQL Server 2008: I got a Table 'Products', each product...

Optimizer and stale statistics - The question is, what happens when statistics on NORECOMPUTE set tables go stale? i.e. pass the rowmodctr > (unfiltered rows...

Similar strings. - In the past week, I saw somewhere a string similarity function. (I think I saw it in my 'spare' time when...

sql partiton view - hi, i build view that contain lat say 10 table, each table contain check constraint on column name origtime type datetime , after...

Name Parser - I am working with a number of large data extracts (10 - 50 million records) and need to parse full name...

mixed data columns - I currently have a column that have both numeric and characters: Example: 3 PF 11 PF PF 3 3 I am trying...

large object inBuffer cache alert - Hi Can anbody advise on how to handle an alert that tells me i have a large object in the buffer...

Result of dynamic sql with parameteres into a variable - Edit 25.4.2013::: Problem was solved, but I have question about view, my last reply... thx for response //////// Hello, i have problem with...

MCTS 70-448 expiring in July 2013 - hi, I want to gain a certification of entry level. I'm working as a BI developer for around 5 years and...

Excel 64 bit driver - Hi all, I am running the following query in sql server 2008 r2(64-bit), MS office 2007 excel file. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel...

SQL Client Access Licence requirements - Hi SQL 2008R2 Std Ed. We are having an internal debate on what does (does not) require a SQL CAL for the...

Script Out Database Mail Settings? - Anyone bother to have created a script to Reverse Engineer / Script out your EXISTING database mail settings? I set up a...

SQL Server 2008 : T-SQL (SS2K8)

how pick value from database on sql server2000? - Hai friends, I m creating one sample application its has two dropdown boxes ddl1 and dd2 if i choose dd1 in...

Delete other than top 2 rows for each foreign key element in table. - I have a table like that MemberId(F.K) ! Event ! Event Date 1 'Test Event1' "2012-03-20 05:39:51" 1 'Test Event1' "2012-03-21 05:39:51" 1 'Test Event1'...

The maximum recursion 100 has been exhausted - Hi I have this query below, CTE code. and I get this error: The statement terminated. The maximum recursion 100 has...

Need help on creating Left outer Join - Hi All, Need your help to create a Left Out join on 2 tables, which will be based on the Period...

What is "cost"? - When the Query Analizer estimates the cost of a query as 5.2837628, is that 5.2837628 of something? Or is it...

Add column with previous days results - Hi, Please help me out with the below query. We are calculating Exposure column (Hilighted in the query) based on current date...

Pratical Advantage of Heap - Hi all experts, Is there any practical advantage of using Heap as a storage instead of clustered index for table.

T-SQL Rename Multiple Tables - I am looking to rename a lot of tables using a wildcard, for example, the table names are: OrderItem OrderItems OrderItemss I would like them...

Distinct Query Help - I HAVE DATA LIKE THIS ID DATE PID

Find Serial number in sql string - So i need to query Active directory and get all the computer based on certian OS type, no problem doing...

SQL Server 2008 : SQL Server Newbies

SQL 2008 Query - Hi I am a complete novice and would like some help. I have a table (see attached image) that i...

Error 5 when trying to connect to database - I am trying to connect to SQL Server 2008 R2 Express from Visual Studio 2012. When executing the VS code...

"Total Revenue Per Day" SQL Query Format - I have a table called DollarTransactions in a database, which records purchases. The table has the following fields, which you...

Query Help with Date and Time - HI I am selecting from a table and setting a condition in the "Where" clause on a column that has a...

Count instances across fields by date? - I need to search a table and count instances of a particular string across multiple fields by date, I've used...

Replace column data with column data of another table - I have Two tables as follows Table A: Empid WeekoffDate 1000 2012-04-07 00:00:00.000 1000 2012-05-17 00:00:00.000 1000 2012-06-07 00:00:00.000 Table B: Empid WeekoffChangeDate WeekoffDate 1000 2012-05-15 00:00:00.000 2012-05-17...

index fragmentation. - Hi All, The dynamic view sys.dm_db_index_physical_stats gives us the external fragmentation value in percent. Is there any way we can find...

SQL Server 2008 : Security (SS2K8)

Difficulity with SQL Logins and Language settings - Hi All, We have supplied a customer with a SQL based accounts package, however we have hit a problem with the...

SQL Server 2008 : SQL Server 2008 High Availability

Moving System Databases in a SQL Cluster - Hi, Iam tasked to move our System Databases (master,model & msdb) to a new storage location onto a different LUN / drive. Its...

Restoring System Databases - Hi , Im using an Active/Passive SQL Cluster 2008 R2 Standard Edition. In my task to relocate the System Databases, I messed...

Slow Disc Access on Single Cluster Node - Hi there, We currently have an issue with one of our SQL 2008 R2 Clusters. The cluster contains 2 virtual nodes...

SQL Server 2008 : SQL Server 2008 Administration

Maintaining Statistics - Hi, I am having a little issue in understanding how to implement a good maintenance plan in updating statistics. I have...

Multi-server Administration - Execute in master - Dear experts, I have implemented a multi-server environment. There is one master server (MSX) and three target servers (TSX). But I have...

Why powershell? - Hi all experts, I have being reading about Powershell a lot this days. What i came to know about powershell is...

Programming : Powershell

Problem copying .bak with "$" in file path/name (PS running in SQL job step) - I'm using OLA's SQL JOBs to do my database maintenance. So far, I really like them; they're very handy, especially...

SQLServerCentral.com : Anything that is NOT about SQL!

SQL Enlight 1.9 - Anyone using this tool, and if so - is anyone using this tool at verion 1.9.1.619 with SQL Authentication, successfully? If...

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

Jump to URL paramater not working in ssrs 2005 - Hello, I am using ssrs 2005 I have a main report and a subreport. When the user click on the dollar...

Urgent: Reporting services migration - subscription jobs did not get created - We are moving to a new data center. So, We created a new virtual appweb server and new database server...

barcode generation problem in RDLC local report - I am trying to encode [url=http://www.aspper.com/barcode-rdlc/tutorial.html]barcodes in RDLC reports[/url], I am using vs 2008 and C# class library. It works well...

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

Reporting Services : Reporting Services 2005 Development

IMAGE LOADING IN SSRS REPORT PROBLEM - Hi all, Here is a SSRS issue. I need the solution ASAP. I am creating a report from a temp table. In...

Database Design : Design Ideas and Questions

Schemas vs Several Databases - Hi Folks, I would like to discuss the following design issue: I'm working in a Project in which several systems are involved,...

Data Warehousing : Integration Services

Data Flow Script Transformation and Pre/PostExecute methods - when do they run - Maybe I'm not understanding this component properly so I'd be grateful if someone could shed light between what I'm expecting...

How to do Batch Process ? - Hi Friends, I have to load 200,000 thousand records in a table. Since RAM usuage goes very high and i...

How to convert the data from stored procedure to excel sheet through SSIS. - I have one task, The task having stored procedure and the task will be run daily and insert the data...

How to avoid technically the null values while concatenating columns - HI, In SSIS by using dervied column i manipulate fields and end up in my desired results. But for an record if...

Ho to Use multiple conditions in Xpath ? - Hi guys, New to Xpath. Was trying to use XML task to load some values. Using Microsoft' XML inventory mentioned below. How...

send me dowload link for SSIS - Hi, I need to download and install SSIS in my system.But i am unable to do so.I dint find a right...

Separate ETL server - I have recently inherited responsibility for managing a small data warehousing team, using SQL Server 2008 R2. My predecessor, on...

Problem extracting character/string values from an Informix database using SSIS 2008 - I am using SSIS 2008 and having a problem with pulling character data values from an Informix source. The etl...

Data Warehousing : Strategies and Ideas

help to design a Cube that shows me all purchases for those who bought a given product - Hi, I'm stuck, so stuck I'm not even sure what to google or put in the subject, so sorry for that. I've...

Geography Dimension - I am currently working on a warehouse project that includes a dimension for geography. It includes Zip Code, City and...

Data Warehousing : Analysis Services

Dimension with Composite Keys and Multiple Hierarchies not giving expected results. - Hi everyone! Basic problem: Hierarchies with no Composite Keys roll up correctly, but for hierarchies with Composite keys all the values are...

Login failed for user 'NT Service\MSSQLServerOLAPService' 28000 - I am using SQL Server 2012 developer edition on Windows 7. I have the following error attempting to process a...