In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.
 
SQL Search How do you search your database schema?
"I use SQL Search regularly and think it's great." Gregor Suttie, Senior Software Engineer, Pulsion Technology. Download Red Gate SQL Search while it's free.
 
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.

In This Issue

Data Mining SQLIO Results

Learn how to use Analysis Services Data Mining to identify the influence of RAID level and IO Pattern on Latency. More »


Red Gate brings SQL in the City back to London for the third year running

Data professionals are invited to attend Red Gate’s free SQL in the City event for a day of SQL Server training in London. More »


SQL Server's Auto Update Statistics Async Option

I have been trying to tune my SQL Server and I noticed a few database settings for statistics. I also read the previous tip on automatic statistics updates. Could you please describe the Auto Create Statistics Async option? How is this configured and when should I use it and when should I avoid using it? More »


From the SQLServerCentral Blogs - Sub query pass through

Occasionally in forums and on client sites I see conditional subqueries in statements. This is where the developer has decided... More »


Editorial - A Good Security Response

Security will become more and more important in the future, at least in my mind. As we become more interconnected and dependent on digital services, if the level of fraud and security failures do not fall, many people will hesitate to use these services. I think certificates will be the future of digital security, but until we get better support for managing them built into all our OSes, I'm not sure we'll move beyond passwords. I do think we need to move past passwords, but we're stuck with them for now.

Recently Evernote had a security breach and they forced all users to reset their passwords. It was slightly annoying, but it was a comforting response for me. Two week after the incident I had to change the password on my iPad, which I rarely use. It was ironic since I was working on this particular piece when I reset my password. 

I wasn't the only one that thought this was a good response. In this article from Enterprise Security, a number of security professionals praised the way Evernote handled this incident. They note that Evernote had implemented good security practices (from what we know) and notified people immediately. I certainly appreciate Evernote moving quickly on this and am glad I had to deal with the annoying password change. I don't use the same password on other sites, and this was a good reminder to me that I shouldn't. It also served as a reminder to tell my family to do the same thing.

I'm not sure any company I've worked for would handle things this way. I haven't had many security incidents at my previous employers, but I know in one case we were told not to disclose anything and fix issues. I'd like to think that most companies would disclose this, and I do think they should, but most wouldn't. These things happen, just like break-ins happen in physical buildings. Companies should accept that, diagnose the issues, repair them, and move on. Customers will understand the problem and remediation steps. What customers don't understand, or accept, is a company failing to inform them. Or failing to improve security when they know there are issues.

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

/* Using SQL Server 2012 or 2008R2 I create the following two (2) tables 
*/

CREATE TABLE [dbo].[Order](
[OrderID] [int] NOT NULL,
CONSTRAINT [PK_Order_1] PRIMARY KEY CLUSTERED 
([OrderID] ASC)) 
GO

CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] NOT NULL,
[OrderID] [int] NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
([OrderDetailID] ASC))
GO

/* I then create a foreign key constraint */
ALTER TABLE [dbo].[OrderDetail] WITH CHECK 
 ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])
 REFERENCES [dbo].[Order] ([OrderID]) ON UPDATE CASCADE

/* I then insert data into both tables */
DECLARE @val INT,@val2 INT

SELECT @val= 4 

WHILE @val < 6 
 BEGIN 
  INSERT INTO dbo.[Order] VALUES(@val) 
  SELECT @val2=1
  WHILE @val2 < 10 
   BEGIN 
    INSERT INTO dbo.[OrderDetail] VALUES ((@val*1000)+@val2,@val) 
    SELECT @val2=@val2+1
   END
  SELECT @val=@val+1 
 END

/* I then execute the following T-SQL statement */
UPDATE [Order] 
 SET ORDERID =101
 WHERE ORDERID = 4 

The questions are: (Select two answers)

Is the row containing the value of ORDERID = 4 updated in the Order table?

Are the rows in the OrderDetail table originally containing a value of ORDERID = 4 updatted to ORDERID = 101 ?

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: cascade. 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

Consider the following script consisting of 5 batches, choose the best answer for what happens.

CREATE TYPE dbo.MyTable AS TABLE
     (CountryCode CHAR(2)
     ,CountryName VARCHAR(250)) 
GO

CREATE FUNCTION dbo.Test (@MyTable MyTable READONLY) 
 RETURNS TABLE 
RETURN SELECT CountryCode, CountryName
        FROM @MyTable 
GO

DECLARE @T MyTable
INSERT INTO @T 
 SELECT 'US', 'UNITED STATES' 
 UNION ALL 
 SELECT 'CA', 'CANADA'

SELECT * FROM dbo.Test(@T) 
GO

DECLARE @T MyTable

INSERT INTO @T 
 SELECT 'US', 'UNITED STATES' 
 UNION ALL 
 SELECT 'CA', 'CANADA'

SELECT *
 FROM dbo.Test((SELECT CountryCode, CountryName FROM @T))
GO

DROP FUNCTION dbo.Test; 
DROP TYPE dbo.MyTable; 
GO

Answer: The first three batches run without error but the fourth batch fails with an error

Explanation: You cannot pass a subquery result to the stored procedure as a table variable. The Error message that appears for the fourth batch is:

Msg 116, Level 16, State 1, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 206, Level 16, State 2, Line 7
Operand type clash: char is incompatible with MyTable

» Discuss this question and answer on the forums

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.


Featured Script

Backups Snapshot

This script gives a server level snapshot of recent backups 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

SSRS 2005 - Web Service Identity Account Change - Hello all, Due to time constraints, I recently had to set up SSRS server using a temporary AD account as the...

how to reduce sort on order by? - I have a query where sort is done at the end on different columns from 3 different databases. I see...

need script for space util - Hi, Can anyone share TSQL script for displaying each drive wise Total, Free , Free% space utilizaiton report so that if it...

Auto Create/Update Statistics for TempDB - I'm getting inconsitent information on this.. By default these two options are ON, and for the best practice of TempDb, some...

Error: Server local connection provider has stopped listening on [ \\.\pipe\SQLLocal\MSSQLSERVER ] - I am occasionally seeing the following error on two of our SQL 2005 64bit servers. (build 9.0.3239) [font="Courier New"]"Server local connection...

SQL Server 2005 : Business Intelligence

Using Excel Connection Manager to connect to UNC Path - Hi, I am trying to connect to the UNC path , but am unable to connect to the Shared Drive, i...

Import Excel into SQL Server using SSIS dynamically - Hi Experts, Can anyone explain me step by step how to import excel into SQL server database using SSIS dynamically and...

Data flow task consuming time due to data conversion task - Hi All, I'm developed a SSIS Package to load data from Oracle data base to SQl Data base. I'm using additionally Data...

While adding datedimension to cube it is showing three other dimensions. - Hi, i am newbie to ssas.. while i was creating a new project following instructions in msdn(using adventureworks dw db) and...

Creating Real time DASHBOARDS - Hi to all. I work for a company that uses from 2005 , SSAS of MICROSOFT. Now we are using SSAS 2008 R2...

Query to get list of SSIS packges in a Server - Hi ! Is there any query to get the list of ssis Packages that reside in MSDB on a server ? Thanks...

SQL Server 2005 : Data Corruption

Time-out occurred while waiting... Reset to device warning - Hi guys and girls, I hope you can help me. I´m running SQL2005 since a couple of years on server, and...

SQL Server 2005 : Development

Select in a view for more result - hy ! There is my view mvtDtate mvtMouvementDebCred TypemvtMouvementDebCred 1/1/2013 154 000 D 2/1/2013 210 000 C 3/1/2013 654 012 D 4/1/2013 213 000 564 C After...

SQL Server 2005 : SQL Server 2005 Security

Windows AD Groups Question - We want to introduce Integrated Security and use Windows Groups to simplify some things in our SQL Server access. I...

SQL Server 2005 : SQL Server 2005 Performance Tuning

sql pages out before hitting low memory - What can I do to prevent SQL from paging out its memory? Situation: I receive calls about SQL App being slow. event viewer...

SQL Server 2005 : SQL Server 2005 Integration Services

Load data into CSV file - I am fetching data from sql server 2005 table, there are 3 columns D1,D2,D3 which are in datetime datatype. Into...

Improve Performance of Lookup Transformation - Hello I need to tune SSIS package. I use Lookup Transformation for Insert Vs Update Operation on Destination Table. Every night I ran...

VISUAL STUDIO 2005 ALWAYS THROW AN ERROR DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER - HI! I have SSIS Package and I set the protection level to DontSaveSensitive. I'm using Xml File and SQL Database as...

SQL Server 2005 : SQL Server Newbies

How to display 2D barcodes in SSRS reports - The [url=http://www.avapose.com/dotnet_barcode_reporting_service/2d_barcodes.shtml]SQL Reporting Services 2D Barcode Generator Control[/url] by Avapose.com is able to add 2D barcode generation function into Reporting...

SQL Query help - I need help with the following SQL query: select whoverified, whochecked AS 'userid', count(*) whoverified, count (*) whochecked from rxproductivity join users on rxproductivity.whoverified...

SQL Server 7,2000 : Administration

Alter column order - Table contain 6 column in order like A B D E F G Need to addd one more column "C" after B column. (in between B...

SQL Server 2008 : SQL Server 2008 - General

deploying a cube - Hi what are my options for deploying a cube? is there an interface users can use to browse the cube as I...

Trigger For External Server Insertion - Hi All, Please i need an answer to this question. I have two servers, one locally and one hosted, i normally...

Documenting RDL files - I'm working in SSRS2008 R2 and I have several projects, which in turn have several report definitions held within them....

Increase values with update statement - Hi everyone I hope somebody can help... I have a table in SQL with the following columns: FinancialYear (varchar), Site (varchar),...

Importing 00:00 into Table - So I've created a query that adds times to get a certain calculation. The end result I would get a...

SQL 2005 - Get Deadlock information - IN sql 2005, I use DMV to get the number of deadlocks per sec. How can i get those deadlock's...

SQL Server Install Location - Can the SQL Server Binaries (2005,2008,2012) be installed in a location other than the C: drive. How can I specify the...

Import .txt file into SQL Table - Not working! - Hi there, I would really appreciate it if someone could help me. At the moment part of our customer information...

Urgent - Reporting services migration and upgrade to 2008 R2 - Hello, We have a task to move reporting services 2005 instance to new servers with 2008 R2 version. This is what I...

Assistance in converting INT into date. - I have a query that has two date fields that are Int....20050307. I need to convert them to date fields....

Is SQL2K8 SSMS slower than SQL2K5 SSMS? - We have a DB [MYDB] in SQL2K5 box, we are migrating the DB to SQL2K8 box, hence restored the backup...

Is DBCC CHECKDB an appropriate weekly check for a 365x24x7 availability db? - We have a db that has a mission critical website sitting on top of it. We check it weekly with...

Lock only one row in update (lock a row) - Hello!!! The question is (if it is possible without any workaround, like add a state column) How can i achieve this... I...

Validate a date held in a text field. - Our systems administrator has took it upon themselves to use a free txt field to house a data value. This is...

SQL Server unable to start with error "Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893788" - Hi Everyone, We have a two node active/passive SQL cluster. Below is the OS version and SQL Version: OS: Windows Server 2008...

calculating time difference - hi, I have a table ID UserID Purpose DateCreated 1 500 login 2013-03-24 14:39:43.273 2 501 login 2013-03-24 14:39:43.277 3 502 login 2013-03-24 14:39:43.277 4...

How to Design Data Staging Area in a Data Warehouse - HI I am a trainee in Business Intelligence, I was asked to list the Best Practices of Designing a Data...

How to merge below two SQL query in single one..? - [size="2"][font="Courier New"]Hi All, I have these two queries and I want to merge into single sql query.. Select KWMENG, NETWR, NTGEW, MATNR,...

SSRS EMAIL subscription not working in 2008 r2 - reports email subscription is not working in SQL 2008 r2 error we are getting ‘sending mail the requested body part...

prerequisites to install SQL cluster - prerequisites to install SQL cluster?

backup - I have transactions 1,2 ,3 and 4 . 1,2 are committed and the rest are uncomitted , when i take t-log backup...

Problem in 1 Billion Records Table - Hi, I have one huge table which has 1Billion records. I tried to delete unnecessary records but it took a lot...

fragmentation in database - Hi apart of avg_fragmentation_in_percent in dm_db_index_physical_stats what other factor should be considered while considering table to be defragmentated eg table...

Query for XML column values comparison - Hi We have a query for return rows by comparing with XML column values [code="sql"] SELECT [DBChanges].* FROM [Checklists].dbo.[DBChanges] WHERE ( [Permited_by].exist('for $x in...

Should I backup more than the database(s) on a server? - I'm a rookie when it comes to backing up a server. I've been able to successfully backup and restore database(s)...

2008 SP2 Recompile - Just read this from Gail Shaw .... http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ 'No, you’ll still get the non-optimal plan. In 2005 the optimiser is required to...

IDENTITY value becoming too big - I have a table that gets a fresh set of data from our source each day. This mean I DELETE...

BCP - I have a stored procedure that exports data from a table into .csv file. There are several different record sets...

Giving permission for each table within a database - I want to give different permission for different tables within a single database for a user.For example a user has...

Truncate table - When we truncate the table does the indexes made on them exists in memory??Can we check whether they exist or...

Deadlock on update command - I have the following dead lock happening quite frequently at 3-4 times a day. The deadlock seems to happen on...

Trace - to Capture all or most queries - A. Are black box and defaula traces same? B. Is it possible to capture all the queires in the black/default trace,...

Best Approach to Archieve DB - Example - We have a DB which have loads of data & we want to archiev it to some Server in such...

Database Diagram printing problems - Greetings all; I have seen this issue/problem for a number of releases now and I have not been able to...

SQL Server 2008 : T-SQL (SS2K8)

Some guidance needed.... - I am wondering how I should go about handling weighted by month calculations for a trending report. Currently, I just...

Multiple condition based on where clause in sql server 2008 - I had a table Product with these fields [code="plain"] ProductName, Description, Manufacturer, Product Code, Technology, Address, Country, City, Length [/code] I want to search all these fields with these criteria [code="plain"] SearchType='Contains' SearchType='Ends'...

how to retrieve the data when a comma separated ids are sent as input parameters - Dear all, I have two tables namely: 1. User table 2. Location table Given below are their design details: [u]User table columns:[/u] UserID INT, ([b]Primary Key[/b]) UserForename...

Performance ISSUE by USING CURSOR HELP !!!! - Hello Mate, I have the following TSQL but I meet a performance issue on Phase 3. Phase 1 and Phase 2...

difference betwwn inner join and intersect in sql - I would like to know when to use inner join/outer join and intersect/except as they probably are doing the same...

Mapping Old identity values to new ones - Hi All, I have a requirement in which I insert values into same table, but I need to map old identity...

Remove characters after last slash in string - Hi All I have the following string [code="sql"]declare @string varchar (100) set @string = 'x:\folder1\folder2\folder3\test.txt' select @string[/code] How would I remove all the characters after the...

Only allow users to see their own records - I'm working on the database from hell, so bear with me... (they think 'normalization' is a dirty word, or a...

Performance Problem In a Procedure - Hi, I feel sorry that in my previous post I put very limited information about my question and apologize for the...

System M Derived in SQL Server ? - All, I have recently read the below articles. [url]https://www.simple-talk.com/sql/performance/join-reordering-and-bushy-plans/ http://www.benjaminnevarez.com/2010/06/optimizing-join-orders/ http://en.wikipedia.org/wiki/Query_optimizer[/url] [quote] Most query optimizers determine join order via a dynamic programming algorithm pioneered by...

SQL Server Logon Trigger Problems - Hello, I have two business needs to deny logon to any connection that is accessing SQL Server with a session that...

How to call a batch file to execute from an SP - Hi All, Need your assistance please, I am not very good with scripting. I have created a draft of SP, and I...

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

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

SQL Server 2008 : Working with Oracle

Oracle ODBC Driver - We are running into a issue with our development SSIS server. We have a Oracle 10g driver loaded and now...

SQL Server 2008 : SQL Server Newbies

Unique Constraints involving two columns. - I have a Table called EmployeeDepartment,it has EmployeeDepartmentId as Primary Key as well as Identity,EmployeeId which is a foreign key...

Need help with Select statement!! - final project is due tonight at midnight and i cant finish up these select statements a. Find all those customers who...

Month Summarised tabular data output from a table - Using SQL how is it possible to get this data transformed in a tabular summary report? data sample supplier number selection_date...

SQL Server 2008 : Security (SS2K8)

How to impersonate an AD Login within a AD Group defined in SQL - We have active directory groups defined within our SQL 2008 server. I have another AD login defined on the server....

SQL Server 2008 : SQL Server 2008 Administration

Mostly accessed objects in database - Is there a way or a query that I can find out in the past year what tables and view...

Cannot backup - Old killed backup still running - Hi I have one DB that on a multi DB server that people cannot connect to. I'm not familar with...

Why should we have multiple FileGroups instead of one? - I just happen to visit a good forum site on SQL Server. Over there, there was one article which says...

doubt on backups - Hi all Here we are having 100 databases ,for 30 databases are backed up in first shift they are not prepared...

Problem with Database mail - We have SQL SERVER 2008 R2,which Database mail does not work properly.When I try to send test mail,it says it...

SSIS ForEach Loop Container - loop through all DB's - I am trying to get the FELC to loop through all the databases for my sql server. I want to...

Career : Certification

Database sample question - So.. I am using Adventure works 2008 sample database now as my SQL server software is only compatible with that......

Programming : General

query other databases from the information in another database - Hi, I have a database called databaseA with a table called, otherdbs with two columns server databasename server1 parts server2 parts I would like a...

Find duplicate client records and suggest one record to keep - I'm trying to get a list of duplicate records and suggest one to keep. The first column returns the unique client...

Programming : Service Broker

Fire and Forget Solution. - Fire and Forget Solution The solution is let the target end the conversation first. The initiator can simply send the message...

SQLServerCentral.com : Anything that is NOT about SQL!

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

SQLServerCentral.com : Suggestions

Message Snippet - This enhancement's utility may be limited to folks like myself that post on SSC often but it would make posting...

Reporting Services : Reporting Services

Documenting SSRS RDL files - I'm working in SSRS2008 R2 and I have several projects, which in turn have several report definitions held within them....

Data Warehousing : Integration Services

Appending the results of multiple stored procedures into a text file using SSIS - Hello everyone. I have a requirement where I need to get the output resultant set of various stored procedures and...

SSIS packages deployment strategies - Hi friends, I would like to discuss some ideas toward the deployment of SSIS packages across servers (Development, test, production and...

CDC Related question - I have a requirement to transfer data from SQL CDC table into the oracle database in some intervals(every hours or...

Concatenation through SSIS - I am trying to do the required scenario through SSIS. Input flat file: id | name 1 a 2 b 1 c 1 cd 2 xyz 3...

Data Warehousing : Analysis Services

ROLAP Writeback Vs MOLAP Writeback - Have been using the Writeback facility in SSAS and wanted to understand the difference between the ROLAP Writeback and MOLAP...

Calculated Member - I'm studying MDX and can't solve one problem. To simplify there is a fact table (fact_Proc) and two dimension (Dim1...