A community of more than 1,500,000 database professionals and growing

Splitting Strings Based on Patterns

Dwain Camps from SQLServerCentral.com

Splitting strings based on patterns supported by LIKE and PATINDEX can be an interesting way to address a wide variety of problems. More »

Free eBook: The Red Gate Guide to SQL Server Team-based Development

Press Release from SQLServerCentral.com

Get a free ebook that discusses how you can better manage your team development. More »

Database Deployment: The Bits - Getting Data In

Additional Articles from SimpleTalk

Quite often, the database developer or tester is faced with having to load data into a newly created database. What could be simpler? Quite a lot of things, it seems. More »

From the SQLServerCentral Blogs - When row estimation goes wrong

Dave Ballantyne from SQLServerCentral.com

Whilst working at a client site, I hit upon one of those issues that you are not sure if that... More »

I was reading a note recently from a DBA working at a software company. Their management wanted to ensure clients had a simple backup solution and were leaning towards Windows OS backup instead of SQL Server backups. They were planning on running databases in simple mode instead of taking transaction log backups, which were seen as too complex. While this can work, I'm not sure this is the type of discussion that should even come up.

Management should be concerned with the higher level goals. Clients need a simple scripted backup. Period. The implementation of that isn't something that management should be discussing with developers. This is the perfect example of where the software development goes off the track with micro management. Managers becoming deeply involved in technical decisions and implementations is a sure way to ensure that less than optimal decisions are being made. 

What should happen? Technical developers should get the goals of management (a simple backup process for clients, every day). They should then recommend a solution, but with a minimal of technical details. Managers should have no idea that transaction log backups are being made or a part of the process. Developers should write scripts, tools, or processes that allow an administrator to accomplish a goal in an easy to execute fashion, but shouldn't need to explain how every detail works to the end user.

Keep it simple and effective. That's a mantra that's worked well for me throughout my career.

Steve Jones from SQLServerCentral.com

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

• Watch the Windows Media Podcast - 13.7MB WMV
• Watch the iPod Video Podcast - 17.0MB MP4
• Listen to the MP3 Audio Podcast - 3.4MB MP3

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

Today's Question:

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

By Lokesh Vij

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

This question is worth 2 points 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.

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.

How many queries does the following code produce in an estimated execution plan?

Begin Transaction

   SET QUOTED_IDENTIFIER ON;

   SET ARITHABORT ON;

   SET NUMERIC_ROUNDABORT OFF;

   SET CONCAT_NULL_YIELDS_NULL ON;

   SET ANSI_NULLS ON;

   SET ANSI_PADDING ON;

   SET ANSI_WARNINGS ON;

COMMIT

Note: This has only been tested in SQL Server 2008.

By Dave Pantaleo

Answer: 8

Explanation: The answer is 8.

Displaying the execution plan in SSMS reveals 8 queries. The exact reason is not documented, but we suspect there are some minor costs to the submission of these statements to the query engine.

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

Print Text Month Calendar

Bernabe Diaz from SQLServerCentral.com

This function return the text format of a month calendar. It could be useful for sending through emails when you do do not have other tools, but T-SQL to send text emails.  More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

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

Unable to start SQL agent with domain account - Hi, I've recently changed our SQL services to run under domain account instead of the local account they used to use....

Link server between SQL 2012 AND SQL 2000 - Hi Friends I have try to configure link server from SQL 2012 to SQL 2000, when i realize its not supported...

SQL Server 2005 : Backups

Sharepoint SQL Database Backups - Has anyone had an issue with SQL Server databases supporting Sharepoint 2007 waiting sometimes for hours to start the backup...

SQL Server 2005 : Business Intelligence

moving data into praraw table - i have 10 text files in a folder and also have three tables in sql server data base.depending on the...

Import from multiple Flat and excel files in the same folder - Hi there, Need help with importing from multiple Flat and excel files in the same folder (or its sub-folders) into a...

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

SSIS package change source from Oracle 9i to Oracle 11g - ORA-12154 error - I am trying to change our source database Oracle 9i to new database 11g. I pass the credentials through configuration...

SQL Server 2005 : Data Corruption

BACKUP DATABASE is terminating abnormally - Dear All, Could someone please advice on this. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. Msg 845, Level...

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

Difference between varchar(max) and varchar(8000) - Hi, whats the actual difference between varchar(max) and varchar(8000). Is the difference applies same for nvarchar(max) and nvarchar(8000). Thanks in advance.

SQL Server 2005 : SQL Server 2005 General Discussion

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 transaction and lock of the table for SELECT - Hi to all I am very new in SQL Server world. I was working with Oracle more of 10 years. Now...

Export table data to UTF-8 text file problem - Running: Microsoft SQL Server 2005 - 9.00.3152.00 Enterprise Edition SP2 Enterprise Edition (Intel IA-64) I need to export data from a table...

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 2005 Performance Tuning

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

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

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

Import .dbf files in sql server 2005 - Hi there, I have a .dbf file which i want to import in sql server 2005. When I right click...

SQL Server 2005 : T-SQL (SS2K5)

How to know which column updated - Hi, I have one Customer Table which contains more than 25 columns. When user Updated any column in customer table. Example: if he...

SQL Server 7,2000 : Backups

Restore DB backup from Enterprise Edition to Standard Edition - Team, i have my production database on SQL 2000 Enterprise Edition. Now to test one of the component, i need to...

SQL Server 7,2000 : In The Enterprise

Project Plan - I'm looking for a decent Project Plan (in any format) for moving from SQL Server 2000 to a SQL Server...

SQL Server 7,2000 : T-SQL

How to get output of SP_HELP_JOB into a script? - Trying to write a T-SQL script to check up on the status of a replication merge agent job that randomly...

SQL Server 2008 : SQL Server 2008 - General

How to change date format in sql server 2008 r2 for all sessions? - How to change date format in sql server 2008 r2 for all sessions? I tried, EXEC sp_configure 'default language', '23'; go RECONFIGURE GO DBCC...

Why does Delete/Update grab an exclusive lock? - In books online it says DELETE & UPDATE always grab an ex table Lock. Im trying to figure the reasoning for this...

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

SQL Server Statistics - Hi to all, is it possible to force SQL Server 2008 not to use Range_ROWS in its statistic ? I have the...

SQL Server Statistics - Hi to all, is it possible to force SQL Server 2008 not to use Range_ROWS in its statistic ? I have the...

Joining Multiple matches from Right Table to 1 row of results - I have a cross reference table, attributexref, which holds values of different fields based distinguished by hattributename. I am wanting...

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

What will be the default instance name on a SQL Server failover cluster (2 nodes) - Hi, What will be the default instance name on a SQL Server failover cluster (2 nodes). I have an active/active sql server...

The multi-part identifier "Master.Buyer_Initial" could not be bound - I'm trying to load part (by that I mean certain columns) into a new table, but I keep getting this: Msg...

Total newb question - Hi guys, I'm very new to sql and MSSM 2008. I have management studio 2008. I made a program to read my...

SSIS - Using Dataflow task to insert records, but the log is growing and eating up all disk space - SSIS - Using Dataflow task to insert records 47 mill records from a SQL Server 2008 r2 db to a destination...

Different execution plan between SQL and SP - Hello, wonder if someone can help, i am looking at a performance issue with a stored procedure (SP). The SP...

Transpose Rows to Columns with first column values as column names from the table?? - I've been banging my head on this for a couple of days now, and I feel the answer is just...

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

how to create a flat file from SQL tables - Hi, I am trying to create a flat file from a database. I am just learning how to work on sql...

Logging field changes in table using triggers or service broker - When someone changes a value in a table I want to retain the old value(s) for a time in a...

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

SQL Server responds to overwhelming network traffic by shutting down - Dear Everyone, We had a network issue this morning, which resulted in extraordinary levels of network traffic for a short while....

BUFFER I/0 - My Sql Server Buffer I/O wait is very high I have 390 GB RAM I set the sql server min 300 gb...

Huge difference for same operator in different environment - [font="Tahoma"] We have the same query performing differently in DEV and QA environment with 99% identical data. DEV was restored from...

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

bringing consecutive days on the same row as start date and end date - I want group data so that the consecutive days for same Location and Contac tName come on one row as...

Blocked Process Alert issues - This is really beginning to annoy me. I have Blocked Process Alerts on every single server in our environment. The alerts...

Backup Overwrite - I have a requirement where i need to take daily backup with datetime stamp , the issue is we have limited...

LIKE with and without wildcards in WHERE clause - I have a set of pretty well tuned queries with indexes and all the appropriate trinkets. This is a 'last...

Iterating thru a Tables columns - I have a dataset that i need to iterate thru in a row wise manner where each row has columns...

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

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

SQL Server 2008 : T-SQL (SS2K8)

Create database and schema dynamically - I want to make a Stored Procedure to create a database with a dynamic name. In that SP I have to...

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

Left Join doesn't return all recodrs in left table - I am linking two tables with left outer join. I need to keep all the records in the left table...

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

convert data type - I would like to convert a column from varchar(10) to int. I also want to ignore any values that have charaters...

sys.dm_exec_query_stats - Is there a way to get sys.dm_exec_query_stats These below in minutes or more readable time? ,max_logical_reads ,total_worker_time/execution_count ,total_elapsed_time/execution_count Thanks in advance

Not sure why PIVOT returns a single row? - I'm new to using the PIVOT operator and I want to PIVOT the following CTE result set: RowID Region Factor ===== ====== ====== 1 Capitol Text1 2 Capitol Text2 3 Capitol Text3 1 Central Text4 2 Central Text5 3 Central Text6 1 North Text7 2 North Text8 3 North Text9 To...

read UNCOMMITTED run slower than read COMMITTED - Hello I have the following query which runs considerably slower when we change the isolation level to read uncomitted, I just...

xml in subselect and performance => alternatives? - hi there, many times there is the requirement of selecting multiple records into one xml-string. Usually you do this using a...

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

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. - I have the following XML statement, where I want the union of several tables to be presented as XML. I get...

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

SQL Server 2008 : SQL Server Newbies

Newbie DBA - Microsoft Dynamics AX - I have been working for years as an AS400 developer but our company is now switching to Microsoft Dynamics AX...

Rebuilding System Databases - Hi Guys Situation: SQL 2008 Master DB is corrupt, instance won't start and I have backups of the master database I'm referencing...

SQL Backups vs System Backups - Being sorta new to DBA work ... If / what are the benefits of keeping sql backups in addition to system backups...

DATEADD minus 1 month - Questions? - Hi, hopefully a bit of an obvious question, but I need to run the following on a daily basis to...

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 : SQL Server 2008 High Availability

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

Log Shipping Filegroup File Name the same on secondary database LS fails - We have two SQL Server 2008 R2 STD installations one will be primary (active) the other secondary (passive). There are...

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

Replication Issue - I have a Database on which we have Logshipping and Transaction replication both configured. Once i do DR Drill and test...

SQL JOBS Not Functioning - SQL JOBS Not Functioning :w00t: I have Stored procedure & that stored procedure needs to be executed on 20 Databases daily, Currently i...

SQL Server 2008 : SQL Server 2008 Administration

Minimum rights for Remote Powershell access and VMI call - I am currently reviewing one of our SQL jobs that collects disk drive space on all of our production and...

CACHESTORE_SQLCP - Hi Guys I've seen it posted that when using a Query like below to check Memory Allocations, if there is a...

What transactions system "Configuration option 'show advanced options' changed from 0 to 1" execute ? - Dear Admin, Why belowlog appears in MSSQL log. What transactions system execute ? MSQSQL 2008 R2 Date 11/28/2012 11:57:32 AM Log SQL Server (Archive...

transactional replication: Snapshot fails at 99% - Publisher is sql server 2008 R2 SP2 Remote distrbutor is sql server 2008 R2 SP2 For snapshot folder I am using UNC...

Cost threshold for parallelism - Hi all When dealing with the Cost threshold for parallelism, I am almost certain that the [i]Cost[/i] is not an amount...

sys.configurations missing 'backup compression default' - Hello, We have a SQL 2008 database with compatibility level set to 80. I don't know if this is the cause...

windows cluster ip Configuration. - hello experts, i want to configure cluster in my laptop (8GB RAM, i7, 1 TB disk), so i have installed VMware...

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

VB script error - Hello, I am trying to upgrade an SSIS package to 2008. The package includes a Script task editor with the following...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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

Eliminating empty output files in SSRS subscription - I have a report with a subscription in SSRS. This subscription post by mail a pdf file. When i have an...

Data Warehousing : Integration Services

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER - Moved to production - Hi, i have developed a SSIS package in SSIS 2005: Source : XLSX File Destination : Sql server table. It is working fine on dev...

SSIS Package not importing particular rows to sql server - I have a package that imports CSV files into SQL server, however certain rows are not importing. As soon as...

SharePoint List Adapter and HTTPS - Hi I was wondering whether anyone had managed to get the codeplex SSIS SharePoint List Adapter components working with an HTTPS...

Testing Center : SQL Server Security Skills

How can I unencrypted a stored procedure - I got few stored procedures With Encryption for the database that I converted to SQL Server 2000.   I need to...