In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Toolbelt Want to work faster with SQL Server?
If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.
 
SQL Intersection SQL Intersection, April 8 – 11 Las Vegas
Join us for 125 different sessions at SQL Intersection. Attendees may cross over to the sessions of the co-located DevIntersection conference at no extra charge. Use discount code "CENTRAL" to receive $50 off. Book your place today.
 
pass The world of data is changing – are you ready?
Upgrade your skills at the PASS Business Analytics Conference – use BACSSC to save $250 today!

In This Issue

HASHBYTES: Is CHECKSUM really required?

When compared with HASHBYTES, CHECKSUM, has a number of drawbacks. Hence, a question that comes up is: Is Checksum really required? In this article, I attempt to answer that question. More »


Forum Etiquette: How to post data/code on a forum to get the best help

Get correct answers to your SQL forum questions faster by making it easier to load your sample data and read your code. More »


SQL Server 2012 Integration Services - PowerShell-Based Project Deployment

You have the option of using PowerShell when automating management of SQL Server 2012 Integration Services. While this functionality is not as straightforward as those accustomed to traditional PowerShell cmdlets might expect, the steps required to accomplish the most common SSIS administrative tasks follow a relatively consistent pattern. More »


From the SQLServerCentral Blogs - PowerShell Usage in DBA Work – Case Study 6/7

SQL Server SMO is a great resource for DBAs, unfortunately, before PowerShell, it is a pretty deep learning curve to... More »


From the SQLServerCentral Blogs - Execution Plans in Azure SQL Database

Microsoft has stated pretty clearly that they’re putting code on Azure first, ahead of the desktop. Which makes one wonder... More »


Editorial - What Do You Want to Know About I/O?

There's lots I want to know. What affect does an SSD have if I use it for a log file? One of my filegroups with indexes? What about tempdb? How do you dig in and prove latency to the storage groups from within SQL Server? Will the query optimizer take advantage of information about drive performance? There are lots of questions I have, and I'm hoping to get answers. I'm hoping to attend the "Ask Anything I/O" panel at SQL Intersection in a few weeks. I'm leaving the event Wed night, so unless there's someone I'm trying to catch in a last minute meeting, I'll be sitting in the audience.

I am guessing that Brent Ozar, Kevin Farlee, and Mat Young will have lots of answers for me and others. I'm also guessing most of the questions will be met with references that are already out there, and I hope that the presenters will publish a list of questions and answers for attendees. That's fine, as there are lots of times when I'm looking for information that has been published, but I can't find in the ocean of Google results. Attending a session given by experts can be a way to shortcut some research and searching to find where the answers are located.

That's one of the reasons to attend a conference. Getting quick answers from experts, or learning how to better target your research. All the speakers at SQL Intersection are well known, longtime, expert SQL Server users who are friendly. They're more than willing to answer questions, or help guide you to solve a problem or learn something new.

The other great thing about conferences? You get to hear some great questions from other attendees. We all attack problems differently, and work in very diverse environments. There are many times I've heard how another person has implemented SQL Server and it's intrigued, or even inspired, me to try something similar with my instances. 

If you can find the funding, SQL Intersection is a great event to attend, with an impressive list of speakers, each hand picked by Kimberly Tripp and Paul Randal of SQLskills. They provide amazing training in their Immersion events, and are trying to bring even more options to you with SQL Intersection. You can even walk away with something tangible in addition to all the knowledge you'll gain: a Surface Tablet.

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

A Principle server sends a transaction to the mirror and waits until the transaction is committed on the mirror server. Then the transaction is committed on Principle Server. This mode is called?

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

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

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Yesterday's Question of the Day

/* 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 ?

Answer:

  • Order table is the row containing the value of ORDERID = 4 Updated
  • OrderDetail table are all the rows containing the value of ORDERID = 4 Updated

Explanation: According to http://msdn.microsoft.com/en-us/library/ms186973(v=sql.105).aspx, ON UPDATE CASCADE says: Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key are also updated to the new value specified for the key.

» 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

Backup history details EMAIL (past 24 hours) in HTML format

Get backup status email daily to your inbox in HTML format.  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

SetSPN - Where we would run SETSPN command for SQL Server ? -On DB Server on the domain controller ?

Update query is taking longer time - Hi, We had to delete huge data from one table and after that we rebuild indexes associated with this table then...

SQL Server 2005 : Backups

Change in Recovery Model - Hi, In my Datawarehouse project(implemented 2 yrs back) we want to change the Recovery Model from Simple to Full. What will be...

starting sql server - can we restart the sql server in single user mode if yes what will happen to server

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

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

SSIS 2012,Ecel 2010 excel source error connection manager - Hi all, ----------------------------------------------------------------------------------------- Could not retrieve the table information for the connection manager 'Excel Connection Manager'. Failed to connect to the source using...

SQL Server 2005 : Working with Oracle

Trigger i Oracle - Hi Team, Am new to Oracle, i need a trigger i have a table named Stud: Columns : ID, Stud_Name, Another table named...

No data found error - Hi all, We get the below error when using order by clause in the select query using the linked server...

SQL Server 2005 : SQL Server 2005 Integration Services

Metadata errors moving from Testing to Production - Guys, I really need some life saving help on this one! I have a package that uses Progress 10b OpenEdge ODBC...

SSIS and dealing with embedded double quotes - Is there a way for SSIS to overlook embedded double quotes in the data so when I run my package...

ssis and ssrs interview questions - can any one tell some interview questions in reporting service and integration service on sql server 2005 please this is urgent...

SQL Server 2005 : SQL Server Newbies

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

Best backup compression tools - We are planning to buy backup compression tool, please suggest which one will be good. Thanks!!

SQL Server 7,2000 : T-SQL

create sql str with special condition - create sql str with special condition hellow I have 2 table Table a(code, price, check) Table b(code, percent) I want to create a sql string...

SQL Server 2008 : SQL Server 2008 - General

data design considerations - Scenario is that I want to have a primary key to identify a new entry in a main table. Call...

restore database without overwriting users - I am receiving a daily download of a database and restoring it locally to do some custom reporting on. I...

Optimal values needed for Server - Hi, Can any one tell me what these items are total cpu usage,context switches/sec,paging rate,network bandwidth utilization,disk queue length .Under what...

Deadlock Analysis Results from trace flag 1222 - Hi. I have just captured deadlock info into the error log using trace flag 1222. Can anybody help me disecting the...

SQL registration in Management STudio - From time to time, I need to test a users login with SQL, so I go to a registered servers...

Parallel Thread Deadlocks Help? - Trace Flag 1222 enabled Profiler Captured Database Tuning Adviser I’m believe I’m getting “Intra-Query Parallel Thread Deadlocks” and not really sure how to...

Minimal Logging Enhancement in SQL Server 2008 - Hi folks, SQL Server 2008 has enhanced INSERT..SELECT statement to allow minimal logging in certain cases, documented here: [url=http://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx]http://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx [/url](See section: Best...

Inserting rows from one table to another another with PK column value incrementing - Hi, We are using Sql server 2008 R2 express. We have two tables,table1(id int,name nvarchar(50)) and table2(id int,name nvarchar(50)). For both...

No Analysis Services Option - Hi When i open ssms i dont get the Analysis Services option in the server type field. I have checked my...

calculating period of time - hi, and here comes my next question :-) i need to calculate the sum of quantity by period of days from beginning...

Inserting child between descendants using HierarchyID datratype - Hi, We are using Sql server 2008 R2 express. We are trying to use HierarchyID datatype to represent organizational structure. We...

SQL Server 2008 Transaction log backup issue... - Hello SQL Masters, Greetings! I would just like to ask regarding Transaction log backup. While backing up the T-log, is there still...

Creation of auto Increment row - Hi, I am using Sql server 2008 R2 express.I want a column [b]id[/b] with auto incrementing value in my table.But i...

certificate error - Iam unable to open the reporting services url as getting getting error as There is a problem with this website's...

how to copy all the scripts one sql server database server to another sql server database - Dear all, Hope you doing..., actually i am changing all my tables from 10.101.__.17.dailyreps database name to another server 10.101.--.23.daily reps. i copied...

select 200 column out of 250 column - If My table consist of 250 colum and i want to select 200 column so it is possible to write...

Enabling Trace Flag T1118 - Hi, We are using Sql server 2008 R2 express. We heard enabling T1118 is good for tempdb contention.Somewhere it said...

Assistance with this query - I'm a bit new to SQL, and I'm attempting my first real project. Currently, my setup is: SQL Server 2008...

SSMS Performing Slow over remote connection - Hi Mates, One of the instance is hosted in a cluster node with 128GB of ram and 80 processors allocated to...

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

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

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

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

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

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

Curious: performance difference when using function in a where clause. - Hello all, This is just something I noticed and am [b]wondering[/b] about (I think this is an unexpected effect), there is...

Track SQL severity levels in SCOM? - Trying to set up alerts and/or email notifications in SCOM 2007 that are triggered by SQL Server (2005 & 08) severity...

Validating trace file events - Hello, If I have a trace running and populating some *trc files.. My question is how can I validate that i am...

SSMS crashing at startup - Hello, I have a windows 2008 server with an instance of sql server 2008 installed on it. After installing an instance...

SSIS Warning - Global Shared Memory - Hi I'm busy rewriting DTS packages as SSIS packages. As and when I finish a package I run it in debug...

SQL Server 2008 : T-SQL (SS2K8)

I think I already know the answer but... - I suspect the answer is going to be "No" but thought I'd check with more experienced heads than my own. I...

how to shows results from three queires to one please? - Hello All I have below 3 sql queries which gives results on order by accounts, Now the question is how to...

how to populate month value equally to all business days value and help me to write Stored procedure for below scenario? - Hi Every Body, i am strucked up in creating stored procedure.... My scenario is: I Have a table Sales_Month with values...

Reorder a custom order column - Hi, Been stuck on this for a little while and hoping that someone can help me figure out my logic problems. So...

Return 1 record with data from multiple tables - All, I have data in numerous tables. The first 2 tables are simple: tblProducts ----------------- ProductID ProductName CategoryID tblCategories ---------------- CategoryID CategoryName Each product links to a category. That part is...

Query help - [code="other"]I need a query to get the expected output Table:Student stguid stuName studwor stid ------------------------ ----------------- ----------------- ----------- 642-4d5d-9af0-4c7a18dd ChrisName Chris 255 4171-8655-2de255b88e08 ChrisCity SAN City 179 2a0d-4100-bd1c-343882 ChrisCou

Alpha Numeric number Generation - Hello, Need help with in writing a Stored Procedure for Auto Alpha Numeric Generation. If I pass an Alpha numeric...

Extract Saturday through Friday as the weekly date range - I have data with attendance hours/dates for every day of the month/year. I need to create a query where I...

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

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

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

SQL Server 2008 : SQL Server Newbies

Mixed joins - I have a table (tblTimesheetWOErrors) containing an error list with (ErrorID, WONbr, TimesheetID, Username, Date). The WONbr refers to a...

Database email - I setup database email and get this Message [260] Unable to start mail session (reason: System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection'...

how to insert datetime column into table - All, I'm having strange issue trying to do the following which I thought was just going to be straight forward select top...

New Database Request Form - Hi guys, Just wondering if anyone has created a new SQL Server Database Request Form? What I'm looking for is a...

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

Disable & Rename 'SA' - Hi All, Our SQL risk assessment determined that 'sa' should be renamed and disabled. As a fallback readiness, i gathered all...

SQL Server 2008 : SQL Server 2008 High Availability

Transaction Log Full - Hi, I need help with my database. The database is mirror to another database and not the disk is full. No...

Finding an LSN - Blind log shipping - I've been tasked with trying to set up log shipping in a blind scenario... that is, the source server and...

SQL Server 2008 : SQL Server 2008 Administration

Column encryption and decription........ - Hello experts, I need to encrypt 4 col. in sql server 2008 and decrypt again. Do I have to do one...

SQL Job Agent History format - Is there any way to fix the formatting? I'm just getting lines of text without carriage return/line feeds. It makes...

Catching culprits of high tempdb growth - Hello, We are facing issues with tempdb on our SQL server 2008 clustered instance. The SQL version is Microsoft SQL Server...

SQL SERVER 2008 PRO installation - Hello,All. can anyone please help. I have visual studio 2008 pro installed in my system.while sql server 2008 pro installation i stuck-up...

starting sql server - Is this possible to restart the sql server in single user mode if yes is there any impact on server

Log Backup - After I take the log backup, shouldn't I see the decrease in size of my transaction log file? Do I...

HIgh CPU usage by SQLserver.exe - HI All, We have moved one of our 2005 database to 2008 R2 windows server- with 16GB RAM and 4CPU's. The database...

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

Programming : Connecting

Connecting to SQL Server 2012 with Mixed Mode Authentication - I have Installed Sql Server 2012 on windows Server 2012 and enabled mixed mode authentication on Installation. I have added...

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!

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

Reporting Services : Reporting Services

No Home Folder in SSMS - Hello friends, I have inherited a SSRS 2008 R2 Server v10.50.1600.1 I want to manage the reports/ssrs/subscriptions via Management Studio. But when I...

Could not connect to the report server http://localhost/reportserver - I currently have SQL2k8 R2 Reporting services on client machine (Windows 7). I created and deployed reports on this machine....

Data Warehousing : Integration Services

Sharepoint connection error - Hi Iam trying to access Sharepoint data from SSIS. Iam not able to use the sharepoint adapters because I don't...

Open/View SSIS Packages - When I go into SSIS and look under the MSDB folder I can see the name of my package P1...

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

SSIS packages - Hi Iam running SSIS packages and scheduled it for 30minutes in sqlagent.Iam running nearly 10packages for every 30minutes.The problem is...

Data Warehousing : Analysis Services

Can´t view properties for analysis project. - Hi, i have an analysis services db which contains datasources, data source views, dimensions and cubes.. I have used the "open...

Getting Grand Total of a calculated member - I have a calculated member in my cube called [LY Pounds]. I need to get the grand total of [LY...

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

Creating a Cell Calculation - Hi Everyone, I hope someone out there has experience with this. I am trying to use a cell calculation in my...