In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor "It really helped us isolate where we were experiencing a bottleneck"
- John Q Martin, SQL Server DBA. Get started with SQL Monitor today to solve tricky performance problems - download a free trial.

 
SQL Source Control Is your SQL Database under Version Control?
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.
 
SQL Backup "SQL Backup Pro 7 improves on an already wonderful product" - Don Kolenda
Have you tried version 7 yet? Get faster, smaller, fully verified backups. Download a free trial of SQL Backup Pro 7.

In This Issue

Stairway to Server-side Tracing - Level 10: Profiler versus Server-Side tracing

Compares and contrasts tracing using Profiler with server-side tracing, illustrating important performance differences so that one can choose the right tool for the task at hand. More »


Auditing SQL Server 2012 Server Roles

My organization is looking at SQL Server 2012 and I know that the ability to create roles at the server level is a new feature. Since this is new and impacts security, how do I handle them and how do I audit them? More »


From the SQLServerCentral Blogs - Creating an empty table from a SELECT statement

I’m in the middle of reading a good book on DMVs, “SQL Server DMVs in Action” http://www.manning.com/stirk by Ian W.... More »


From the SQLServerCentral Blogs - Help Bring #SQLFamily to Fargo, North Dakota

Did you know that a SQL Saturday in Fargo is in the works?  If not them, hey… there’s a SQL... More »


Editorial - Staging Deployments

Software development can be a complicated dance. Most of us do not work for a software vendor and don't have the strict requirements for our deployments when we control the client systems. That doesn't mean it's easier for us, especially as our environments grow more complex and the availability of our systems becomes more important. Application changes can become disconnected from the database changes, especially when the scope or scale of the change is large, which can present problems.

Making database changes can be challenging since we must ensure that our data is not lost as objects are altered. We have to ensure that any application functions that depend on a certain schema receive the data they need, without unnecessary errors. The timing of changes becomes more important in the database than in applications in many situations. This Friday I am curious how many of you decide to stage these changes in your environment. If you have dependent changes, I'm wondering if you might alter the database first and change the application in a later deployment.

How many of you deploy database changes before code changes?

By "before" I mean you deploy the database changes, possibly making some application changes, but there are other code changes deferred for a separate deployment at a later time. The use of views, defaults, optional parameters and more allow database changes to proceed without accompanying application changes. It may require a bit more work, but the database can potentially be changed during a less busy time, even if development or testing for the all the application changes is not complete.

The future will require more availability and stability from our systems as they become more essential to our organizations. Learning to update software, and databases, with minimal disruption is a skill that will set you apart in the future.

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


Question of the Day

Today's Question:

Once referential integrity is enforced, which of the following statements are not correct? (choose 2)

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

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

Select which of the following statements are true with respect to the NEXT VALUE FOR function used for SEQUENCE Object. (choose 2)

Answer:

  • Sub clause "PARTITION BY" is not supported when NEXT VALUE FOR function is used with OVER Clause
  • Can be used in stored procedures.

Explanation: The NEXT VALUE FOR function can be used in stored procedures and triggers. An OVER clause applied to the NEXT VALUE FOR function does not support the PARTITION BY sub clause. The NEXT VALUE FOR function cannot be used in views, in user-defined functions, or in computed columns.

Ref: http://msdn.microsoft.com/en-us/library/ff878370.aspx

» Discuss this question and answer on the forums

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.


Featured Script

Dependencies View 2008

Provides a list of dependencies for a stored procedure 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

Linked Server Time - out - I have a linked server, with server options ... Connection Timeout = 0 Query Timeout = 0 Running a job, we got the following error: OLE...

Roll up of a column in dynamic query - HI This is data in the table ClientId Ad TagId Name Total 1 D1 47 American 1000 1 D2 47 American 500 1 D3 47 American 300 1 D4 48 Hispanic 1000 1 D5 48 Hispanic 200 This is the result when I pivot the table ClientId TagId Name D1 D2 D3 D4 D5 1 47 American 1000 500 300 0 0 1 48 Hispanic 0 0 0 1000 200 you...

splitting tempdb to multiple files - max size? - i have noticed tempdb contention on our tempdb, so i want to split the data file to multiple files.We have...

SQL Server 2005 : Backups

Backup policy for 2 TB database (Interview perspective) - Backup policy for a 2 TB database..... what would you recommend exactly and why for each day of the week. what...

SQL Server 2005 : Business Intelligence

Long versus wide fact tables - Hi, I'm working at an insurance company which has several different dimensionally modelled warehouses using differing techniques. One of these is using...

Problems printing a report that utilizes a barcode font. - I have a report that utilizes a barcode font (3 of 9) for one of the report fields. We have...

SQL Server 2005 : Development

Copy DB Tables from PRod to DEV - Hello, I have situation that my developer wants some tables from Prodction DB server to their desktop m/c. We have already sql...

SQL Server 2005 : SQL Server 2005 General Discussion

Baselining Database Code - I would like to find an easy way to baseline procedures, functions, etc for SQL Server 2005. We are contemplating...

Maintenance PLans in SQL Server 2005 - Hello, I am running SQL Server 2005 SP4 Standard and have sysadmin right. I have created a maintenance plan but it...

SQL Server 2005 : SQL Server 2005 Security

Hide all system views/tables from users in SQL server 2005 - We have a request from client to hide all system views/tables from users in SQL server 2005. As user assigned to...

SQL Server 2005 : SQL Server Express

Database stuck in RECOVERY_PENDING state - Hi, I have client who sql express installation hosting search database for sharepoint search. they informed yesterday that search not working...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Too many statistics - Hi, After reading Gail Shaws articles about performance issue finding, I thought i'd give it a go myself. [url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url] While playing around with...

SQL Server with VMWARE - Hi. We have a big concern with VMWARE. We are running over VMWARE windows server 2008 (domain controller and DNS). We...

SET FMTONLY ON - Where does this come from - In doing monitoring and troubleshooting I have found a lot of the following statement as lead blockers in on and...

SQL Server 2005 : SQL Server 2005 Integration Services

How to schedule ssis package to run only on Business working days ? - hi friends, I have a stored procedure (performing some transformations) , which is in SSIS package.I need to schedule SSIS package...

Data Import from CSV file - I have a CSV file which is having record like mentioned below. [code="other"] UserName User Id Type RamKumar ram MSAD RamaC rama MSAD RamesK rames MSAD Rameshv ramesh MSAD RamKumar ramp PCG Ramig rami ...

Creating Header AND Footer to a flat file destination file - Hi Guys, I am working on a financial project; the requirement is interface flat file should not contain any duplicated arrangement....

DATA TASKS FAILS...IN SSIS - i have a dataflow; loading from flat file to sql table. There are truncation warnings, but actual data will not...

SQL Server 2005 : T-SQL (SS2K5)

Find Price Changes in Item History Table - Ok, here's an easy one for some of you. I want to read through a group of items and determine...

Full Text Search query and noise words - Hi coders (and administrators :-D ), I am working on query with CONTAINSTABLE. Everything works well if search terms doesn't contain...

SQL Server 7,2000 : Administration

Creating a view becomes a SYSTEM object?! - We run a SQL Server 2000 instance v8.00.2249(ye, I know...) on which I wish to create a view. I have...

SQL Server 2008 : SQL Server 2008 - General

Aggregator advanced tab - Hi all, what is the use of advanced options(key scale,number of keys,count distinct scale,count distinct keys,auto extend factor) in SSIS...

Mail queued, not delivered - Hi Everyone, We have SQL SERVER 2008 R2,that it's Database Mail has stopped working.When I checked the sysmail_event_log,I found the...

Select query to determine unique values - I am trying to create a query to return certain information, but I am not sure how to get the...

problem in connect between 2 servers - hi i have 2 pc named PC1 and PC2 that connect with wired network.i installed SQL Server 2008 on both PC.(Instance...

Update XML tag - Hi, Can any one fix this "Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in...

XML execution plan to Graphical execution plan in SSRS report / programmatic way to convert XML execution plan to Graphical execution plan in SQL SERVER - Hello, I am stuck with one scenario.This is my query. SELECT TOP (2) qp.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY...

SQL Server authentication mode for SCCM 2012 - Good day, Can someone please help me or direct me. On the SCCM 2012 requirements page for SQL Server Requirements it says...

Full Text Search Multiple Columns - we are testing a new project and are trying to use Full Text search. We have a front end app that...

Full, Log & Differential Backups - Hello, I've started out testing how a database using the full recovery model is backed up starting with a full database...

Connection to SQL Server dropping - I'm running a MsAccess app connected to SQL Server through an ODBC link. It works fine in almost all cases,...

Indexes question.... - Hi, If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index...

New login & Access to DB - Hi Friends, i have asked to perfrom a task to create a New login & then provide the access rights to a...

Logical Query Processing - Dear all, It was my understanding that when processing a query the order in which it is processed is FROM, JOIN,...

TSQL Problem: Week between 2 different months - Hello, I have a script problem. I calculate data per week (group by week) However, for indicators, I have business rules which apply...

More or less SARGABLE clauses in a WHERE Clause - Question - Is it better to have more SARGABLE Clauses in a WHERE Statement OR less to find a record(s). Is it...

To OTAP or not to OTAP? - Currently I am working in an organization, where development and production databases reside in the same SQL Server instance. The...

What is DCEXEC? - As the question. It is used in an SQLAgent job. Googling links it to SSIS execution, but no explanation AFAI...

rebuild index worsens query performance - we have a query which runs several times a day reads from several tables in one database ("W"), and two tables...

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

Selecting a count based upon maximum in a data field - I have customers with various amounts assigned to them by a code. A count is required by the code based...

NOLOCK Hint Corrupts Results from SELECT - Good day fellow SQL Server Enthusiasts, I'm having an issue with the NOLOCK query hint causing erroneous joins. In a nutshell,...

SSRS 2008 R2 page break after every 4th person in group - Hi Everyone, I need to add a page after the 4th Patient in a report. The report has two groups. Organization and...

Best way to implement partitioning - I have my own idea on how to handle this, but want to se if there is a different perspective...

IF any of my system database is crashed my sql server will work ah ? - hi to all IF any of my system database is crashed my sql server will work ah ?

HOW TO FIND A VALUE IN ALL COLUMN OF ALL TABLE IN A db. - HI ALL, As i Have a task to find 9 digit value in all column of all table in a DB...

Interop.SQLDMO.dll with .NET 3.5 64-bit framework - We are upgrading our servers to 64-bit and also want to upgrade our code to utilize the 64-bit .NET architecture....

Error encountered during creation of databases: CREATE FILE encountered operating system error 5(Access is denied.) - Error messages: CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'S:\SQL\userdblog01\test_log.LDF'. CREATE...

SQL Server 2008 : T-SQL (SS2K8)

How to query odd record - i have a data that looks like the below.Every record should have set and clear but some don't. Therefor, how...

Keeping Current - Hi All... I have a set of databases located at a facility hours away. I have a machine here at the...

Recursion with a Twist - Hi - can't seem to get my head around this one, so any assistance is welcome. I am trying to write...

OPENQUERY error: An unexpected NULL value was returned - Hi, all! Over the past year, we've been migrating our reporting databases from 2000 to 2008R2. We've got many stored procedures...

How can I generate XML and apply a style sheet in t-sql? - Hello, I am beginning to write a procedure that will extract data from several table in XML format and apply...

How to use values keyword as parameter of a function - I would like to have a function which accepts any number of parameters with the same type and returns true...

Search a string from multiple column in a table - Hi I want to search a string in multiple columns and i want the result from the column which has matching...

Convert rows to columns - We have a following table: [Day] [Time] Mon 07:00 Tue 12:00 Mon 10:00 Mon 15:00 Tue 15:00 Tue 20:00 I want to make a query in sql 2005...

Update statement question - I'm doing a DB review and have always been taught to write updates with a Join like using this syntax. [code="sql"] Update...

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 : Working with Oracle

Data migration from sql to oracle - I have transferred one table from sql server 2008 r2 to oracle 11g. using import export wizard. all permissions in oracle...

Help with Linked Query Setup for Oracle - Hey guys, I am trying to setup a linked query to an oracle server on my machine. I am using...

SQL Server 2008 : SQL Server Newbies

Using SUM(1) to count Rows (vs Count(1)) - I have run into an instance where a developer was using SUM(1) to count rows in a table rather than...

SQL Server 2008 : Security (SS2K8)

Limit Concurrent Logins by database and/or user ID - In my environment I have a sql server 2008 database on 64bit architecture. The production database accessed by users is...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2008R2 in a Cluster - errors in event log on passive node - I have 2 Instances installed in a 2 Node Cluster. Windows 2008R2 and SQL Server 2008 R2. currently, all Cluster...

Automatic mirroing failover and application connectivity - Hi All, We are planning to setup DB mirroing for our prod server with automatic failover. I need your suggestions and recomendation...

Cannot Repair or remove node - I have a 2 node cluster. When I move the instance of SQL from node B to Node A, SQL...

Changing Locations of DataFile - Hi All, As i see some of the servers are having databases online... Now this Database have data file & log file on...

SQL Server 2008 : SQL Server 2008 Administration

High CPU/IO/Memory Script - Hi, I need to set-up job whihc can find the connect/Query which is taking high IO or High CPU or High...

autogrowth - I would like to know as a dba practice, do you setup the autogrow for file size to a restricted...

Buffer Pool Dirty/Clean Pages - Hi Guys I have posted a few times regarding this topic, I have taken into account all advice given and I...

Upgrade from 2005 32-bit to 2008 R2 64-bit - Is there a way to do an in place upgrade from the 32-bit 2005 version to the 64-bit 2008 R2...

Fragmentation and log shipping ?? - tables/indexes are heavy fragementated on production instance, what about the hot standby instance ( it's setup by log shipping every 15...

SQLServerCentral.com : Anything that is NOT about SQL!

Is there something wrong with the search on the site? - It's coming back with no results for anything you put in there. I even tried just searching on 'sql' and...

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

Upload .rpt files in SSRS report manger 2005 - How do we upload .rpt files to a specific folder in SSRS 2005 report manager. I can see the upload...

Barcode problem on report viwer (Barcode Professional) - I have problem on Neodynamic Barcode Professional 6.0 for Reporting Services on trial version. It's look perfect on report builder but...

Reporting Services : Reporting Services 2005 Development

Need HELP on SSRS SPLIT function - Hi All I need your advice, 1) I have a dynamic string which changes each time. for example today the string will...

Database Design : Disaster Recovery

Using SAN replication for DR on a SQL server - In order to achieve quick failover (and failback) in the event of the loss of a data centre we are...

Data Warehousing : Integration Services

Pointing to a Particular Data Flow Task from a Script Task - Hi all, I have an SSIS package that downloads csv files. The first process queries a db table (Execute SQL Task)...

Data Warehousing : Analysis Services

Fact table pointing to the same dimension twice. Don't quite know how to deal with this - Hi, Im having a problem and Im hoping that someone might be able to help me. I have for example a fact...

Error while deploying a cube - Hi Every Body As i am new in SSAS. I want to deploy the cube but it display following error....

Microsoft Access : Microsoft Access

Incremental Number - lets say I want to increment 0000 to 9999. I know how do this. how would you identify if I have...