In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor SQL Server monitoring that works right out of the box
SQL Monitor’s defaults are based on advice from SQL Server experts, so you can start monitoring today without an elaborate setup. Start monitoring with a free trial.
 
SQL Compare Compare and sync databases with SQL Compare
“SQL Compare is fast, extremely easy to use, full-featured and affordable. I wouldn't bother messing around with anything else.” Adam Machanic, SQL Server MVP. Download a 14-day free trial.
 
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

Use of Identity Property to Resolve Concurrency Issues

This article describes how the identity property was used to resolve contention in a database More »


Creating Custom T-SQL Conditions and Policies for SQL Server Policy-Based Management

In SQL Server, the built in conditions and policies are a great place to get started with monitoring your environment, but there are no facets for some aspects of SQL Server I want to monitor. How can I check on my environment using Policy Based Management? Check out this tip to learn more. More »


From the SQLServerCentral Blogs - It’s a Lineup at SQL Intersection

I got this image in the mail recently, and it made me laugh. It looks like a police lineup, perhaps... More »


From the SQLServerCentral Blogs - Learning By Doing

I was recently talking with a friend about doing some research into a NAS solution for home and that I... More »


Editorial - Toggle Switches

When SQL Server 7 was released, it was touted as a self-tuning, self optimizing database platform requiring much less attention from a DBA. The product had relatively few tuning options and limited information available about how it processed queries. DBAs were worried about losing their jobs, though as history has shown us, the concerns were overblown. There was plenty of work for DBAs then, and that has continued through the current SQL Server 2012 release.

However the number of tuning options, and the wealth of information exposed by SQL Server to developers and administrators has grown tremendously over the years. We have DMVs and DMFs, many more tuning options, new hints, isolation levels, and more that enable the DBA to manage SQL Server fairly in a very granular way when they want to do so. From what I understand, there are still less options than other platforms have and often the best advice I seen given from various people is to write more efficient code and let SQL Server still determine the optimal plan for query execution.

This week, I'm curious how you feel about the tuning and configuration options in SQL Server. The downside of the additional options in other platforms is that there are more choices to make, more DBA decisions, and more administrative overhead in regularly, and constantly tuning these systems.

Do you want more toggle switches in SQL Server?

I don't mean two position switches, like the physical ones used on the Apollo command module, but rather just switches you can use in SQL Server. These could be database or instance level, sp_configure settings, they could be query hints, they could be session options. Do you want more options, or do you think we have a lot to work with already?

Personally I like the idea that we can change behaviors, but I'd really prefer that the defaults were well set and somewhat self-tuning for most installations.

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

Which styles of datetime or smalldatetime are deterministic when using Convert? This is in SQL Server 2005 and greater? (choose 3)

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.

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today



Yesterday's Question of the Day

Is TRUNCATE TABLE a DDL command or a DML command and why is it different from the DELETE command (choose 3)?

Answer:

  • It is a DDL Command
  • TRUNCATE does not fire triggers, but DELETE does.
  • TRUNCATE resets the identity property; DELETE does not

Explanation: TRUNCATE Table is the DDL command, TRUNCATE resets the identity property and does not fire any triggers.

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

» Discuss this question and answer on the forums

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today



Featured Script

Powershell - Document your environment

This script is an improvement from my original script entitled "Powershell - Query SQL Servers Operating system details".  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

Changed SQL Services Acct - "Cannot Generate SSPI Context" - We want to rollout a new account to use for SQL Services. On a Dev server (SQL 2005 SP3, Windows...

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

Restoring database from production to Dev - We have Sql Server 2005 database server in both production and development environment. I have the requirement to restore a database...

Table Level Rescovery/Restoration - HI All, Can we restore/recover individual Table in Microsoft SQL Server. As far as i know it is not possible Out...

Log backup and running time - Hi, Bit of an idea to ponder: I have a server with 15 user databases ranging in size, the largest being 120GB....

Microsoft SQL Server, Error : 2 - While connecting to SQL Server 2005, I am getting the following error. "An error has occurred while establishing a connection to...

SQL Server 2005 : Business Intelligence

Execute Single Query in Multiple Servers (central location ) - Hi, i need to execute one query in 20 SQL Server instances and the results needed to send an email. any one...

Transforming Date types in SSIS from an Excel Source - Hi, I am using SSIS to import data from multiple excel files into a SQL Server database staging table using a...

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

SQL Server 2005 : Data Corruption

Corrupt or tampered with? - When I boot up my computer, I get an error dialog for SQL Server. I don't understand what it means,...

SQL Server 2005 : SQL Server 2005 General Discussion

Named Pipes Stops Working After Some Time - Hi All, I have a SQL 2005 x64 Std Ed instance that allows TCP and Named Pipe connections. After restarting the...

SQL Server 2005 : SS2K5 Replication

SQL Server 2005: Transactional replication between hard drives on same server - I'm relatively new to SQL server development in a non-collegiate scenario, so i will try to be as concise as...

SQL Server 2005 : T-SQL (SS2K5)

Best approch to finding/fixing miss-spelled names. - I have a column in a table that has user names, user can appear in this table multiple time, but...

sp_send_dbmail fails within a trigger - I'm trying to write a trigger that will inform me via an email using sp_send_dbmail when a certain value has...

Transaction Log Growing Despite Simple Recovery - I have a data warehouse database which is set to simple recovery. The transaction log was originally set to 1G,...

SQL Server 2005 : SQL Server Newbies

scheduling job - Hi friends, We need to monitor a user session for locks to troubleshoot a problem in the program. When the...

SQL Server 7,2000 : T-SQL

BCP error -Unable to open BCP host data-file - SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file NULL   I am getitng the above error when i...

Need Split delimiter string into columns Solution - Hi All, I need help with a string with delimiter that needs to be split into column names. I have been...

SQL Server 2008 : SQL Server 2008 - General

Database backup and restoration - Hello All, We currently have a backup and restore job which runs on a weekly basis and the database size is...

result of adding non-clustered non-unique index vs clustered one - We're testing some rather large - at least for us - narrow tables that will be populated with between 200 and 500...

Data Conversion Error - I have a staging data which holds all data as NVARCHAR, I am now loading from the staging table to...

KILLED/ROLLBACK STATE - SERVICE RESTART - Hi all, Is there any fix other than sql service restaRT when there is transaction stuck in killed/rollback state (after killing...

venkatrao.m.cse@gmail.com - Hi, tempdb full on solve reply me

Anyone help to script the linkedservers on weekly basis by automatically - Hi , Anyone help to script the linkedservers on weekly basis by automatically in text or .sql format file Regards, Saran

Help needed in SQL Server Replication - I have got History tables to track the changes and for those, the Primary Key is not there. Also there...

File Groups advatages - hi, We have a best practice in BOL that "We need to have a separate File Group for the User data". Is...

Cannot scan a remote server with SQL Server BPA 1.0 - Hello gents I am using Microsoft SQL Server 2008 R2 BPA 1.0 wrapped in Microsoft Baseline Configuration Analyzer 2.0 on my...

Same stored procs and different schemas = issues? - I have two stored procs which are identical, except they exist in 2 different schemas. Stored proc A in schema...

Change the Date Format - Good evening, Please, my sql server Database has a table which contains this field: [b]Date_Start(Datetime, Null)[/b]. The format of the data...

XML Data Type as a parameter in a Stored Procedure - Hi, I've table as follows, [code="sql"] CREATE TABLE [dbo].[majikanAG_subMajikan_1]( [idx] [int] IDENTITY(-2147483648,1) NOT NULL, [batch_Id] [uniqueidentifier] NOT NULL, [icNo (Baru)] [varchar](100) NULL, [icNo (Lama)] [varchar](100) NULL, [payerNme] [varchar](300)...

SSIS Conditional Split not working when run on SQL 2008 R2 using SQL Server Agent - I'm having an odd problem after upgrading our SQL 2008 server to 2008 R2: An SSIS package containing a conditional...

SSRS 'Render' and TempDB Growth - I have a client that beginning two days ago is having an issue with TempDB growing extremely fast and running...

How to interprete the unused space ? - In the properties of a user database, we can obtain the database size and the available space. We can shrink...

Performance issue - Hi, I have two tables. treatment_plan table having 8 fields like txt_instruct_Description_1,txt_instruct_Description_2.... As per requirement txt_instruct_Description_4,txt_instruct_Description_5,txt_instruct_Description_6,txt_instruct_Description_7, txt_instruct_Description_8 field values should be moved to another...

Comparision of tables in two instances - Am having two instances A and B, in that having a table called students in two databasess, but the records...

Check Data While Inserting - Dear All, I have a UI like, LineNo ProdID Discount(%) and a "Insert" Button -------- ------ -------- I can insert a single or multiple lines in this UI. While...

Not able to find historical data using SQL Server Profiler in SQL Server 2008 - Hello All, I would like to capture the script that was ran by user on yesterday between particular time. But somehow i...

Transactional replication problem - Hi everyone, We encountered some strange error when we were trying to add a subscriber. Following steps were performed: 1. Publsher and distributor...

Problem having restoring database backup file on secondary server - Hi for last two days at one of our client side we have been trying figure out a why its...

Doubt about Shrinking process , Rebiulding index and fragmenatation - hi i know that shrinking data base increase fragmentation , so logically shrinking data files would two result in fragmentation but...

SQL to divide one row by another row based on formula or rule. - Hi All, I have a below table and need to divide one row by another row based on the formula. Date Name ...

BCP to import tab delimited file with header - Hi, i am trying to load a file which is a tab delimited file , which has a header row in...

SSMS User Interface Tip to Freeze Panes. - Accidentally discovered this, and thought I should share it, as I don't think I've seen it mentioned before. In Management Studio,...

ROWLOCK - CREATE TABLE [dbo].[TESTROWLOCK]( [ID] [int] NULL, [NAME] [varchar](100) NULL, [SURNAME] [varchar](200) NULL ) ON [PRIMARY] GO [b]TAB -1 [/b] I m trying BEGIN TRAN SELECT ID FROM TESTROWLOCK...

existing column Level encryption in sql server2008 - Hi, how to set the column level encyption in sql server 2008 in production enviroment without drop existing column and...

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

SSRS 2008R2 showing counties (not just states) - I have a hopefully simple question. SSRS 2008R2 can do maps by state just fine... is there an easy way...

sp_updatestats and the default sampling rate - Using SQL Server 2008R2 As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue...

Primary key or not to primary key, that is the question - Yeah, bad taste on the subject but nonetheless, you're reading my post :). I'd like to pose a scenario and see...

TSQL Optimization - CASTing and CURSOR - hi, Is CASTing cost an additional overhead to the query? I need to optimize our database initialization and i reviewing some...

Trace Flag 8048, 8015, or SUMA? - Hola! Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server...

Few tricky Questions about SQL Server 2008 that I came across... - Few Questions about SQL Server 2008 that I came across. [b]Q: Which of the following allows for the truncation of...

First letter upper case for each word in sql server. - hello All, I want to write down stored procedure or function that will return or generate first letter in Upper case...

SQL Server 2008 : T-SQL (SS2K8)

i want update the 2nd column based on first column: - hear i have id column based on that getting one more column as mid and data as belo: [u]id[/u] 100 200 300 400 500 Expected output: [u]id[/u] [u]Mid[/u] 100...

Optimization of dynamic SQL - Hi All, I have a stored procedure with many optional parameters. Following is one of the code blocks from that SP which...

Bushy plan vs left-deep - I have read about Bushy plan and left deep in some articles. Bushy plan will consume more memory as it...

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

How to report on historical movements within a changing hierarchy - I'm in an IT environment where we're wanting to: 1. run queries which will show rates of consumption of entities which...

status on a given date or date range - I have a situation where we need to be able to query the status a claim was at any given...

Add variable number of rows into a table based on the values in another table (without cursors/while loops) - Below is a simplified version of my tables: set nocount on drop table #x create table #x (docid int, pages int) insert into...

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

How to read XML file with multiple children and load it in SQL Server - I have an XML file in the below given format. How can I import it into SQL Server using OPENXML...

Ranking functions, views, and predicates - So, I ran into a nifty little "bug" I'll call it. From what I can find this was an issue...

BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'. - DECLARE @Cmd VarChar(4000) DECLARE @SProcName VarChar(40) DECLARE @SP_Parm1Value VarChar(40) DECLARE @SP_Parm2Value VarChar(4000) set @SProcName = 'Rpt_JobTransactionsSp' set @SP_Parm2Value = '|SRMQDIC|,|ROD|,|B|,|HNS|,|0|,|B|,null,null,null,null,|000|,|9999|,|1/1/2013|,|3/17/2013|,null,null,null,null,null,null,null,null,|T|,null,null,|1|,|0|,|1033|' set @SP_Parm2Val

SQL Server 2008 : SQL Server Newbies

powershell and ssis - hi all, im trying to build a data collection of various sql servers. currently i do most of the data...

Do Users based on logins in master have access to other databases? - When I create a user and select master as the default database does that mean or imply that user can...

SQL Server 2008 : Security (SS2K8)

working with sa account - I am working with sql 2008 and using all features of security but not able to understand the need and...

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

Replicated data duplicating on subscriber? - Hey All, I'm relatively new to replication and here's what I'm seeing across environments and SQL Versions (from SQL 2000 thru...

SQL Server 2008 : SQL Server 2008 Administration

How many instances we can create - Hi all I have small doubt, how many instances we can create in production environment and how many databases we can create...

SSIS package failing while executing as Agent job - Hi, I had some weird issues while running SSIS package as agent job. I can run SSIS package manually but failing...

Log Shipping Compression - Hello, does anybody know if any versions of SQL have the ability to compress log files before shipping. The plan...

Suggestions on TEMPDB Setup - I have a teradata environment with database aroudn 3TB. This server has around 40 core processors. Microsoft suggested to Use...

Career : Employers and Employees

What to do about a complete lack of jobs? - I've encountered the problem of not being able to find further employment as a SQL Server programmer/admin in my area,...

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

Reporting Services : Reporting Services

Design page does not work.. - I have a SSRS project which when i open on another computer does not take me to the design page...

In SSRS, how to insert hyperlink in a cell. - I have developed a report using SSRS 2008 R2. In the report i've multiple tables and given page break to...

Data Warehousing : Integration Services

running SSIS packages in msdb with SQL Agent on SQL Server 2008 R2 - Hello, I am quite new to SSIS, and I've run into a problem deploying a set of packages on SQL...

Data Warehousing : Strategies and Ideas

Experiences using Wherescape Red on SQL Server? - I am struggling to find much content out online so I was wondering if anyone wanted to share their experiences...

Data Warehousing : Analysis Services

dates and columns - hi, i have another question (or two) :) 1) i want the mdx to always return the data from yesterday without choosing...

Sum and Group Results - Date Comparison - I am working on a MDX Dataset from a TFS2010 Cube (I Have also posted on the Team Foundation Server...

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

trying to make my first report and it is not going :( - Hi, I am new here, I have a question, unfortunately every time I solve it on my own I keep mixing...