In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor "It's the freaking iPhone of SQL monitoring"
"Everyone just gets it… that has tremendous value" - Rob Sullivan, DBA, IdeasRun. Get started with SQL Monitor today - download a free trial.
 
Red Gate Deployment Manager NEW! The easiest way to deploy .NET code
Deploy ASP.NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL DBA Bundle ‘The Case of the Missing Index’
Discover the Top 5 hard-earned lessons of a DBA, presented by The DBA Team. Learn from lesson one now.

In This Issue

Tally OH! An Improved SQL 8K “CSV Splitter” Function

The Tally Table has proven to be a simple and elegant method for avoiding many varieties of RBAR. Unfortunately, one of its more common uses, that of a CSV splitter, has a well-known and serious performance problem. MVP Jeff Moden shows us what that problem is and how to correct it. (UPDATED with additional info and attachments on 5/12/2011). More »


Index Create Memory Setting for SQL Server

The "index create memory" option in sp_configure advance server settings, controls the maximum amount of memory initially allocated for creating indexes. This tip deals with common issues around this setting. More »


From the SQLServerCentral Blogs - SQL Server – Skipping Function Execution when parameters are NULL

You can avoid executing a function when NULL values are passed as parameters. To achieve this you need to create... More »


Editorial - 2013 Goals

It's the last Friday of 2012. If you're reading this, then I assume the world hasn't ended and you are planning on starting the next year next week. This is the time of year where we often look forward and make resolutions with the best of intentions. Most people don't keep many of their resolutions, though I'm not sure if this is because they aim too high or too far from their current path.

As this year ends, I'd like to ask you to think smaller, and think about your career. For the last poll of 2012, I wanted to ask this:

What's your goal in 2013?

For your goal, I'd like you to think about one thing that you'd like to improve in your skill set in 2013. Is there one area of improvement that you'd like to tackle across 2013? I'm sure there is. SQL Server is a huge platform, and we all have holes in our knowledge. 

With that in mind, I'd like you to think about something you wish you could do. Perhaps you want to understand how a tally table works, or you'd like to be able to get the versions for all your instances using PowerShell. Maybe you want to tackle something like The OLAP Sprint and improve your BI skills.

Choose one area, and then make a goal. Give yourself a concrete project to tackle. The project shouldn't be large, and it shouldn't take the entire year to complete. Drop a note in the discussion and then set an appointment for yourself that repeats every week, every two weeks, or every month and work on the project. Next year I'll ask you if you actually accomplished your goal.

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

Using SQL Server 2012, create this simple stored procedure.

USE AdventureWorks2012

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[TestProc3] 
AS
SELECT * FROM [Sales].[vSalesPerson]

Executing the above SP returns 17 rows of data. Without further work it does NOT tell me the meta data of the columns returned. I have a requirement that requires knowledge of the column meta data. I then execute the following T-SQL

/* SELECT A */

SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc3'), 0)

/* SELECT B */

SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc3'), 1) 

The question is: Which SELECT statement (SELECT A or SELECT B) returns NON NULL values for the columns: source_database, source_Schema, source_Table,source_Column.

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.

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.


Yesterday's Question of the Day

What will be the output of the query in the "quantity" column?

declare @doctor table(
    doctor_id int identity(1,1),
    name nvarchar(50),
    primary key(doctor_id)
);

declare @service table(
    service_id int identity(1,1),
    name nvarchar(50),
    price int,
    primary key(service_id));

declare @doc_serv table(
    doctor_id int,
    service_id int
    primary key(doctor_id, service_id)
);

insert into @doctor values('doc1'),('doc2'), ('doc3'), ('doc4')

insert into @service values('serv1', 1000),('serv2',500),('serv3',700),('serv4',1200);

insert into @doc_serv values(1,3),(3,2),(3,3),(2,1),(2,4),(1,2);


select quantity = count(*)
from @service s
inner join @doc_serv ds on s.service_id = ds.service_id
right outer join @doctor d on ds.doctor_id = d.doctor_id and s.price < 1000

Answer: 6

Explanation: The key to understand this question is logical processing order of the select. Filtering is made at the same time as right outer join is executed. 4 rows from left set meet price's requirements (s.price < 1000). Although "doctor 2" also has connections in bridge table, none of them meets requirements which leaves him as ad additional row from "doctor" table without connections. Because "doctor" table is on the right side of right outer join "doctor 2" and "doctor 4" will be included in output with null values, which will give us 6 rows in total.

This example shows that the query isn't logically the same as if it would contain filtering in "where" which is executed after joins are made.

» 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

Table Information View -- No Cursors!

Row counts and storage space information for all tables in the database. (SQL Server 2005). 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

Issue installing SQL Server 2005 - Hi. I am having a problem installing SQL SERVER 2005 on my windows 7 enterprise 32 bit operating system. The installation...

The replication agent encountered an error and is set to restart within the job step retry interval. See the previous step history message or replication monitor for more information - Hi, Im new with SQL, please help me on how to trouble shoot this error "The replication agent encountered an error and...

CALL PHONE from SQL Stored PROC/ Alerts - Is there a way to call phone and give automated msg from SQL server; as notification / alert methd; I can do...

Changing the initial size of db file - I need to shrink database and log files to less than their inital size, thus change the initial size of...

How to Create Linked Server for a MySQL database using SQL Server Management Studio - It took me about a day to figure this out, so I thought I'd try to save someone else the...

SQL Server 2005 : Business Intelligence

SSIS Transfer data ON INSERT - I have 2 SQL servers: SQL_Local and SQL_Remote. I have SSIS on SQL_Local. I want to transfer records from SQL_Remote.Customers...

Parallel processing not supported on Standard edition of Analysis - Hi, We have a SQL server standard edition in our project. for performance tuning, one of the client SME has recommenced...

Process Cube Failed - Help - Hi! Help to problem.... SELECT [dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Bar

Difference between browser in SSAS and Excel cube - EDIT: I'm not sure how to delete topics but I found out what my issue was *** please disregard Hi I posted...

SQL Server 2005 : Development

Reg: Date Inerval - Hi i am working examination schedule project.now my scenario is university already given two dates.examination start date and end date.i...

SQL Server 2005 : SQL Server 2005 General Discussion

SSRS parameters override - There is an SSRS Report with some parameter.for example let us assume abc is name of one of the parameter...

SQL Server 2005 : SQL Server 2005 Security

Cannot run query as job, but can run it as a query - I'm having issues running a job in SMSS 2005. The queries themselves work fine on their own through SQL queries,...

SQL Server 2005 : SQL Server 2005 Strategies

How implement object orientation features in sql server? - I know features of object orientation could not implemented, but i want to find a way to implement them. For...

SQL Server 2005 : SQL Server 2005 Integration Services

Possible to determine the datetime range of loaded data to staging table? - Hi all, I have created a SSIS package. In the control flow i have a Execute SQL task which will truncate...

How to make group of continuous tasks to execute based on single condition - Hello All, Could you please help me out here, i am totally stuck here, i have total 20 tasks in my ssis...

How to know which DTSX file was deployed in a server. - Hi all!!! I inherited an interface process (Oracle text file to SQL Server table) which uses a SSIS 2005 package...

cannot open datafile - Hi, i'm creating a ssis package to access a flat file. i created a batch script to obtain the filename of the...

SSIS Error to use the oracle String - HI all i m getting 1error when i connect my oracle Table using connection String when i click on table ,its...

SQL Server 2005 : T-SQL (SS2K5)

The maximum allowable avg_user_impact for any missing index - Hello everyone, I am required to create some indexes on the table to improve the performance of the database queries i have...

exec master.dbo.xp_cmdshell - [b][/b]:ermm: Its been working all this while but for some reason it has stopped working for only 1 of our...

Starting a Job - Hi, I would like to have a procedure start a job, is that possible to do and to do it...

SQL Server 7,2000 : In The Enterprise

Log shipping a distribution server - Hi, I'm trying to get log shipping to work with a distribution server used in transactional replication. Shipping the distribution...

SQL Server 7,2000 : Replication

The subscription(s) have been marked inactive..... - Hallo, from time to time my transactional (push) replications on SQL2000 SP3a servers send me the following error message: Error Message :

SQL Server 7,2000 : SQL Server Agent

How to migrate sql scripts from 08 to 05 - [font="Times New Roman"] Hi, We have developed some scripts in SQL Server 2008. Now, we need to migrate them to SQL Server...

SQL Server 2008 : SQL Server 2008 - General

SSIS loading only 5 of 42 fields to SQL - Greetings all. I am learning SSIS and having great fun with Sequence Containers, Control flows and Data flows. I believe I...

SQL Server Pivot tables - I have a table that keeps track of a store open, closed, reopen, closed (again) , reopen(again) etc dates. The table has...

PROBLEM WITH DATE CONVERSION - I have a column called "UP_DATE". When the code below trys to read "UP_DATE" I get the following riders.UP_DATE = CDate(reader("UP_DATE").ToString) error....

SSRS Report aggregate and collate pages by items - Here's the scenario: Old system has several documents which are generated in Word and can be viewed individually. If user selects multiple...

Performance Issue: Execution Plan different (involves cursors) - On our production server SQL 2008 R2 10.50.4000 our ERP system runs this query (nothing we can do about the...

SSIS, SSMS extremely slow to start with Network Cable pluged in - Starting last Friday developers starting having trouble with in some cases SSIS and in others SSMS. For instance one developer would...

SSMS not opening - we are unable to open SSMS from the windows box ,however we are connecting to the server from the other...

Not null Foreign key self-referencing identity column - Hi all, I'm wondering if there's a way to do the following: IF OBJECT_ID('test') IS NOT NULL DROP TABLE test GO CREATE TABLE test (nId INTEGER IDENTITY...

send sms to a mobile phone from sql server 2008 - hey everybody ! please can someone tell me if we send sms to a mobile phone from SQL SERVER 2008 if...

Using Varchar(max) to add to a field? - Merry Holidays everyone, I've created a basic table which includes a text field. I then created a SP which basically...

Procedure execution taking longer time - I have a procedure which is getting called on click of button. It takes around 5 sec to complete .The...

Cisco certification exams object - Certification exam candidates require a relatively high must be mastered quite basic computer knowledge, have the computer skills to apply....

Examination proceeds - I think that:To return the training and certification investments by providing a standard to determine training needs and test the...

The value of Microsoft certification exams? - MCP tutorial is the technical manager of the world is seen as a sign of quality.The potential for MCP professionals...

How through Microsoft certification exam - Microsoft certification exam is different from the general computer literacy exam, its main purpose is to develop advanced computer expertise....

how to Pivot this - hi i am having two tables one table is having header details and another is having data Table_id Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 1 Mytab_1_1 NULL Mytab_1_2 NULL ...

High availability related issues. - Hi guys, Logshipping , Mirroring and Replication operate at which level sql server instance level or database level and cluster of sql...

Keeping all three environment(Dev,Stag,Prod) in sync - In our office we run jobs to keep these environment in sync. We get data from outside source and run...

Need Suggestions on creating new table - Hi, I am having a condition that i need to create tables on run time that is Say for customer 1, if...

Scripts of logins are not done properly - Hi Friends, When am generating scripts of my database in sql 2008r2 am not getting the given permission of sql logins...

SSIS PACKAGE DOUBT - Hi Guys, I need a small help, I need to execute package with input parameter. The command which i have...

Unable to connect sql server - hi guys, While connecting to sql server named instance we are getting following error: A connection was successfully established with the server,...

Applying Patches - Hi , I have to apply patches on SQL server in cluster ACTIVE/ACTIVE. On 2008R2 (RTM) need to apply SP2 On 2008...

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

tempdb fileplacement - Hi all, I have a 1T, high I/O database in a SAN environment on which I have planned the database files...

SSIS Expressions - How to write a DATEDIFF function inside a Derived column of an SSIS Package. Im trying to have theses functions...

SQL Server 2008 : T-SQL (SS2K8)

how to fetch records from multiple tables - i have Demo,elections and electionshist tables where i should neglect the pepole who have status='t' from demo table and for...

i want to add column in exiting view - Hi i am trying to use design mode. but it is throwing error . is there other wayt to add column in...

SELECT * INTO Table without propagating IDENTITY attribute? - Using the simplest SELECT INTO syntax, the IDENTITY property of a column is part of the definition of the destination...

Problem related to transaction - Hi all, My friend ask me the following transaction problem. Consider the following structure of tables as [b]Table1(int)[/b] [b]Table2(int)[/b] [b]Table3(int)[/b] Now consider the following problem. [code="sql"] CREATE...

Updating an Entire Column in a 10 GB table - Hi Folks, I have a very big table with size around 10 GB(Data+NC Indexes). There is a need to update an...

Maintenance Plan issue - hi all, while executing the plan [b]Maintenance Plan [/b] it's giving error like the log for database 'tempdb' is not available....

Stored Procedure looses execution plan. - I have a stored procedure and when I run it First time it creates an execution plan and it takes...

NOT EXISTS - tortoise or horse ? - All, I have often worked on the below requirement. There are two tables and fetch the record from TableA which doesn't exists...

Remove the first two characters in a column value if they meet a certain condition - I have a column of data in a table that I need to update and remove the first 2 characters...

Finding a quote in a string - What can I use to find if a string contains a '? I know how to replace the quote with a...

ORDER BY Should be same as my input in IN() - DECLARE @T TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @T VALUES(1,'PPP') INSERT INTO @T VALUES(2,'AAA') INSERT INTO @T VALUES(2,'ZZZ') INSERT INTO @T VALUES(3,'XYZ') INSERT INTO @T...

SQL Server 2008 : SQL Server Newbies

SQL Server 2012 Developer - I have a book that uses SQL Server 2008 and requires at least a developer edition to run SSIS. My...

download sql server management studio RTM 9.00 1399.00 - Can anybody show me the download link sql server management studio RTM 9.00 1399.00 (not express version).

Ignore/Skip data coming from falt file and loading into SQL table - Hi, I have issuse to figure out how to skip a data from a column in a flatfile to SQL table....

Sql Server 2005 doesn't connect for windows xp SP3 - I have installed sql server 2005 in Windows XP SP3. Installed successfully but [b]a) server didn't connect gave error message...

Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34...

SQL Server 2008 : SQL Server 2008 High Availability

EMC Upgrade caused SQL server to crash - I have a two node cluster for my production system that is running SQL Server 2008. We were upgrading our...

Extremely slow commits - I am a .NET dev working with have a high-availability SQL2008-R2 database, which is a bit of an OLTP/OLAP mix....

ODS - Operation Data Source with small data comapring to Production to gain performance. - Hi, we are in the process of building ODS - Operational Data source, ODS will contain only current 3yrs data, whereas Production...

Is database mirroring used much in the industry? - I'm curious because some features of SQL Server are not used that frequently in businesses. What types of business environments...

Suspending Database Mirroring - Hi All Does anybody know if suspending db mirroring prevents automatic failover should the primary fail ? I know that Suspending stops...

SQL Server 2008 : SQL Server 2008 Administration

SQL server Agent has failed - I am installing SQL server on cluster but SQL Agent has failed to comeonline with bellow errors in event log. Cluster...

database Mirroring with suspend ?? - Hi team, we configured dataabase mirroing with in the cluster . Now one of the database in suspend mode . configured mirroing on...

Export data failed? - Hi, SQL - SQL Server 2008 Standard Edition with Service Pack 1 I have tried to export all tables to another new database...

ALTER AUTHORIZATION hangs and causes blocking - Hi, I'm trying to change the owner of a database using a command like the following, but it doesn't complete and...

Define CPU terms/keywords ? - i am bit confused in following CPU keywords 1) dual-core 2) quad-core 3) [b]n[/b]-core processor 4) sockets 5) "number of processors" means ? It's...

MAXDOP setting - I know that this is slighty a 'depends' question but our server is heaver user transactional server. Our SQL 2008R2 server...

JobName Not found error - HI All, I'm seeing a intresting error on one of my SQL Instance. Below is the script I'm running,, connecting to...

Using ::fn_dblog() to find who deleted the rows in a table. - Hi All, Recently some one deleted some rows from a table. I was asked to find out who did it. Since...

Career : Employers and Employees

adivce about career - Hi, my self sudarshan,I am currently working in automation company from last 6 moths on sql server 2008, we generate...

Programming : Powershell

Query local SSMS server group with Powershell? - This may be a totally off the wall question, but it seems like it should be possible to do. Just...

SQLServerCentral.com : Anything that is NOT about SQL!

Hardware investment - I need to talk to someone knowledgeable and with no intere$t - Hi there guys. So i'm shaking... I've been hired as an IS guy, but with time and budget constraints, i was forced...

WordPress Question - I've got WordPress on my blog and need some help. When I'm blogging about SQL Server, I frequently go "below...

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

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

Permission Issues - Hi, SSRS configured on ABC server I do not have permission to access physically to that server. I have to access from...

Need help in Building a parameterized Report. - Hi All, I have a ETL design which has 58 Tasks that it does every day. Each ETL is assigned...

Hide data label for pie chart slices under 10% - I'm working on a pretty simple pie chart in BIDS 08. I have 6 slices of pie and want to...

Database Design : Hardware

Storage Subsystems and RAMDisks - We're setting up a new database server for our OLTP and smaller website databases, and disk performance is a big...

Data Warehousing : Integration Services

ssis 2012 package - I have successfully deployed my first package to sql 2012. The package runs fine in visual studio but when deployed...

how to read dynamically changing source excel files - Hi All SSIS Gurus i need help. i have a ssis package which loop through the excel files and import the...

Data Warehousing : Analysis Services

Failed Cube Process - Help - Hi! Help to problem.... SELECT [dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoQtdBarrasDefeito0_0],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1] AS [dbo_VW_Fato_Inspecao_Barra_DefeitoBitola_Inspecao0_1],[dbo_VW_Fato_Inspecao_Barra_Defeito].[dbo_VW_Fato_Inspecao_Bar