In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle Top 5 hard-earned lessons of a DBA
In part one, read about ‘The Case of the Missing Index’ and learn from the experience of The DBA Team. Read now.
 
SQL Backup Pro Low cost, scalable SQL Server backup storage
Use SQL Backup Pro to upload your backups to secure hosted storage, and only pay for the storage space that you need. Find out more.
 
SQL Monitor SQL Monitor v3 is even more powerful
Use custom metrics to monitor and alert on data that's most important for your environment, easily imported from our custom metrics site. Find out more.

In This Issue

Eleven Challenges in Unifying Reference Data

Unifying the reference data across an enterprise can be a bigger job than expected. This article describes one approach to doing so. More »


SQL Server 2012 Cluster with TempDB on Local Disk

With the introduction of SQL Server 2012 Microsoft officially supports local disk TempDB in SQL Server cluster configurations. This tip will show you how to configure TempDB on a local disk when installing your SQL Server 2012 cluster. More »


PASS Data Architecture VC presents James Serra on Introduction to Master Data Services

On Thursday August 20th 12PM noon Central, James Serra will discuss how companies can consolidate their enterprise data with the new feature of SQL Server 2012 Master Data Services. More »


From the SQLServerCentral Blogs - Using XQuery to remove duplicate values or duplicate nodes from an xml instance

Sometimes we need to retrieve a list distinct values from within an xml instance or even distinct nodes and this... More »


Editorial - The Auto OS

Those of you who have followed SQLServerCentral for a long time know that I like cars. I used to do car updates periodically, and still get in car debates on times over Twitter. I love my cars, and despite the age of a few of them, they're all doing well. My Prius still gets 47-50 mpg (depending on weather) and it's proven itself to be a great investment. It's even paid off, and as gas prices rise, I use it more and more.

For much of automotive history, the advancements have taken place under the hood or in the mechanical systems of the car. Computers have been a part of design and even operation for a long time, but primarily to keep the mechanical systems operating at a high efficiency. The interactions with drivers have mostly  come from entertainment or climate systems.

That changed a little with the introduction of navigation systems, that allow a driver to spend less time finding destinations and concentrate on driving. I know that hybrids that surface efficiency information to the driver have changed the way I drive, even when I'm in another car. I suspect the same is true for many people that purchased cars with the idea they would save money on fuel. I suspect in the future we will get more interactions with cars in new ways, perhaps even in ways we hadn't considered before.

Cars are becoming more complex, and many have their own OSes inside. This might be the next OS war, after the mobile phone competitions that are ongoing between iOS, Android, and Window Phone. I suspect that these two are closely intertwined as I think integration between a mobile vehicle and a mobile phone is essential and I hope there are good APIs and standards that allow any phone to work with any car.

Data integrity and security will become more important in the mobile platforms as we use them for more services. I hope that companies are paying attention to these issues, and ensuring they not only hire great developers and software engineers, but also good data professionals to help them build their systems. I suspect that isn't the case, but perhaps a few of you will find jobs in the automotive industry and become strong advocates for data security.

» Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

Is it possible to create a table with unique constraint that allows multiple NULL values from SQL Server 2008 onwards?

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.

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset

As the most influential thought leaders in the data warehousing and business intelligence industry, the Kimball Group has developed pioneering techniques that serve as industry standards for DW/BI system design, development, and management. With this new edition of their bestseller, veteran experts from the Kimball Group get you up to speed with using the new Business Intelligence release of SQL Server: SQL Server 2008 R2. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, the authors follow the full project lifecycle, including design, development, deployment, and maintenance. Get your copy today from Amazon.


Yesterday's Question of the Day

Base off the code below what will the two selects of the id column return?

create table test_trun (id int identity(1,1) not null, somedata varchar(50) null)

insert into test_trun values('a')
insert into test_trun values('b')
insert into test_trun values('c')
insert into test_trun values('d')

select max(id) from test_trun

truncate table test_trun

insert into test_trun values('e')
insert into test_trun values('f')
insert into test_trun values('g')
insert into test_trun values('h')

select max(id) from test_trun

drop table test_trun

Answer: 4, 4

Explanation: Truncate table resets the Identity column back to the seed value. So both selects will return the same value.

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

» Discuss this question and answer on the forums

SQL Server 2012 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Featured Script

Manipulate Horizontal Hierarchy with UNPIVOT

Convert Horizontal Hierarchy to vertical Hierarchy. 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

Executing sql query from select t_sql from table1 - hello guys, can we get recordset from the query that we saved in the recordset ?? [code="vb"] CREATE TABLE [dbo].[t_test] ( [t_sql] [varchar]...

SQL Server Performance Problems - Hi Guys Occasionally at peak times of the day I get I/O errors on my SQL server (v 2005, 16 core...

optimize for ad hoc workload - Could someone please help me? When trying to configure "optimize for ad hoc workload" in a 2005 Developer edition , getting...

Decrypting program name from sysprocesses for agent job - Hey all, Are there any known ways to decrypt the hex shown in sysprocesses on program name when the process is...

SQL Server 2005 : Backups

Restore database to a remote server - Hello Everyone, I am fairly new to this forum and have some experience with SQL. I was recently assigned a project...

SQL Server 2005 : Business Intelligence

How to load one million records into excel using ssis - Hi, I have one million records in sql server global temp table.i want to dump into excel sheet. please suggest me...

Database Diagrams tool in SQLSERVER - Is there any Database Diagrams tool in SQLSERVER ? Hello : We are planning to design new Datawarehousing product. I am taking...

how to reduce the Time Processing for a SSRS Report - I have a SSRS Report which retrives the data from SSAS cube. When i look at the TimeProcessing for the...

renamed ReportServerTEMPDB now errors referencing dbo.executioncache - I know what has caused my issue, it but don't know the solution. I'll explain my issue and hope someone...

SQL Server 2005 : SQL Server 2005 Strategies

How to archive a database - Hey, Each of my customers has his own database. Sometimes a customer quits. When that happens, I want to archive that...

LINQ to SQL and Entity Framework - What do DBAs think of these options? - I am a .NET developer working with a small team of developers who have to also double as our own...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Clustered Index Scan is taking 87% in executation plan - Hi All, My Clustered Index Scan is taking 87% in executation plan. can you please suggest me to how to reduce this...

awe enable? - hello experts, please go through my server configuration windows server 2003 enterprise sdition 32 bit sql server 2005 enterprise edition 32...

Perfmon counters not working for SQL Server 2005 - Hi, Here is the scenario I have a server (win) with both SQL Server 2005 and 2008, while adding the perfmon...

please help me tunnig part of below query? - USE [PD_ODS] GO /****** Object: StoredProcedure [dbo].[sp_replicate_nxtdate] Script Date: 12/13/2012 13:44:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[sp_replicate_nxtdate] @p_siteid varchar(100) as begin...

SQL Server 2005 : SQL Server 2005 Integration Services

How to store the Count(*) value fro datareader to int. SSIS 2005 - Hi all, I have been stucked with this for quite a while. I have a script: [quote]Dim sqlConnStr As String = "Data...

Demanding and difficult logic qns. Sorry but i need help. - Hi pros out there, I am introduced to SSIS to do ETL for repporting in SSRS very recently. I am given...

Using SSIS to automate PGP encryption of text file - I am trying to automate in SSIS the PGP encryption of a text file.  Has anyone done this before?  Any...

SQL Server 2005 : T-SQL (SS2K5)

Need help conveting this from Mysql to SQL server - Select * from openquery([connection],' -- Forum Messages by User use databasebname; select u.name, convert_tz(from_unixtime(m.modificationDate/1000),'+00:00','-08:00') as TimePST, m.subject, m.body, m.messageID, f.forumID, f.name from jiveMessage m inner...

SQL Server 2005 : SQL Server Newbies

Split input string into multicolumn - multirows - Hello all - I am having hard time to split an input string into multicolumn - multirows. Task - Create a stored procedure that...

SQL Server 7,2000 : General

Four-part name for Linked Server DB2 - I am inserting records from SQL to DB2 through linked server using four-part name. I am connecting with DSN of...

SQL Server 7,2000 : SQL Server Newbies

Linked Servers Error - MSSQL Server A: MSSQL2000 - set to Windows Authentication mode MSSQL Server B: MSSQL2000 - set to SQL Server & Windows Authentication mode. My client...

SQL Server 7,2000 : T-SQL

Using the IN operator with two variable - I would like to explain my problem with a simple example. Here is a part of my database: Type Service ----- ----- Freshwater Habitat Saltwater...

Not getting result of MAX() - Hi i have a table having 2000000 rows. and i want a result of : select max(pksrnoN) from Table1 it is numeric...

SQL Server 2008 : SQL Server 2008 - General

Error when updating over linked server - A procedure that has worked for year had just stopped working the other day and I have narrowed down the...

Seriously Quirky Performance Behavior in a Query - I have come across a strange phenomenon when querying data from one of our historical databases and was wondering if...

Syntax for transfering data from one table to another - What is the best way to load a .csv file's data into an existing table that has no data? Is...

how XML plan is convering graphical query plan internally - Hi Please let me know How Xml query plan is internally converting to graphical query plan by sqlserver engine. SELECT [cp].[refcounts] , [cp].[usecounts]...

How to downgrade from SQL Server 2008 Enterprise to SQL Server Standard edition - Hi, How to downgrade from SQL Server 2008 Enterprise to SQL Server Standard edition? If I want to do it on the...

Trigger to update existing records - Hi Team, Am having a trigger, which will update a column when insert / update occurs, for new inserting or updating records trigger...

install sql on veritas cluster - how to install sql on veritas cluster active\active ?

SELECT query with "Writes" ?! - Hi all, I'm currently fiddling with a query (fired from a Dynamics NAV application) which is like this: [code="sql"]SELECT *,DATALENGTH("Picture") FROM "Navision"."dbo"."XYZ$Customer"...

DATA base inaccesible - getting error:- Database 'XXX' cannot be opened due to inaccessible files or insufficient memory or disk space. please help its urgent

sql server SSL ?? - [img]http://social.msdn.microsoft.com/Forums/getfile/210429[/img] ? ???? ????????? ?????????? ??????? ????? ?????????????? ??????????? SQL SERVER 2012 ?????????? ?????? ?????????? ? ????????? ???????????? ?????????? ?????????? ??? ???????? ????????????. ??????? ????????? ????? ?????? ???? ?????? ???????? ???????? ??????????? Valid from ? ???? ?????? ???????? ???????? ??????????? Valid to.

SSIS - Compact and Repair access database - Hi, how do I compact and repair access database using SSIS execute process task? I tried the following values but...

Read data from XML - Hi Everyone, Please see xml format <row id="LD122487292000" xml:space="preserve"> <c1>USD</c1> <c2>20120904</c2> <c2 m="2">20120904</c2> <c2 m="3">20121004</c2> <c2 m="4">20121102</c2> <c3>20120904</c3> <c3 m="2">20120904</c3> <c3 m="3">20121004</c3> ...

Update trigger without primary key - Hi, Below is my table structure.. USE [SalesOptimizer_New] GO /****** Object: Table [dbo].[AutopilotCriteriasInUse] Script Date: 12/19/2012 15:44:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[AutopilotCriteriasInUse]( [AutopilotID]...

Date format - Hi Team, using below query, am assigning a Product_name and its expriry_date to a variable Prod [b] SET @Prod=@Product_name + RTRIM(@Product_name) + ' (Expires ' +RTRIM(DATENAME(MM,...

Deductions Of Columns Values - hi All , I have a table and columns with data is mention below :- Table :- ORDER COLUMN and values :- ROWNUM ORDERID BALANCEQUANTITY 1 19 5 2 ...

how to know Graphical Execution Plans works through xmlcode in sql server? - i am trying to figureout this issue if anyone know please let me know the process of this.. Issue with Graphical...

HOW TO GET THE VALUE OF SQL TABLE ROW INDEX - How can you get the value of a table COLUMN INDEX.

ADD VALUES TO A NEW COLUMN - I have two tables (TRIP and TRIP_DETAIL both have the same primary key called TRIP. There is no FK relationship....

Will failed login attempts cause SQL Server performance issues? - I'll be honest I really know jack about the inner workings of SQL, so any help would be appreciated. Trying...

FKs with indexes... - Hi, I've read several articles stating that FKs should have indexes on both tables (primary and referenced) and it has quite...

Passing a parameter as a filename - Hi there, I'm trying to pass a parameter as a filename in a restore database statement. In my example code...

HOW TO TABLE DATA VALUE CHANGES MANUALLY DONE BY A PERSON - we have a database of attandance system in which daily attendance recorded. there is a table in which one of...

CXPACKET queries - Hi all Looking at my waits I can see lots of CXPACKETS, and I am wondering what the best way is...

Using Substring and Charindex to split a text in to columns - Hello everyone, I am struggeling with below: I have a field in ReportServer DB called “C.Path AS OrgInput (VARCHAR)”. In this...

Pulling count of records from Teradata - Hi, I am pulling count of records from a Teradata table. I have used Execute SQL Task in SSIS to get the...

weird memory usage on sql server - Hi Folks, I got this weird issue on sql server. I am running window 2008 r2 enterprise 64 bit with 8 gb...

Dynamic query - I am writing a stored procedure with dynamic query to find the record counts from two tables. The table names...

Access table from different instance - hello experts, i want to write a select query which will access table from different instance, but cant create linked server...

Exporting a TEXT (or long varchar) column to an Excel file with the OPENROWSET command. - Hi, I'm using the OPENROWSET command to export data from a table into an Excel file. I'm using this command because...

Distributing the greatest value by rank in SQL - This seems as though it would be terribly simple...but I am stuck. I need to distribute as "average sales" value among...

Filtered indexes issue -URGENT HELP REQUIRED - Dear all, I have created one filtered index on column a, b, c and covering columns are d,e,f and the...

Permission denied on the object sp_send_dbmail (but, not what you think) - OK, so I'm trying to send an e-mail from within a job, using sp_send_dbmail. The job is pretty simple - just...

Script Out Database Mail Settings? - Anyone bother to have created a script to Reverse Engineer / Script out your EXISTING database mail settings? I set up a...

Has anyone used Data Domain storage for SQL 2008 backups? - We are creating a Microsoft Failover Cluster with Windows Server 2008 and SQL Server 2008. Our storage administrator would like...

SQL Server 2008 : T-SQL (SS2K8)

Iterating through records and ignoring unwanted rows - I have table set up similar to this: [code="sql"] CREATE TABLE #Visits ( [TargetID] [int] NOT NULL, [TargetName] [nvarchar](255) NULL, [Interaction] [nvarchar](15) NULL, [Created] [datetime] NULL ) ...

Substring Query to pull firstname and lastname out of one column - I Have a table with a field called name that is formated as the examples below. I need to pull...

Correlated Subquery - Stream Aggregate - Hi All Some DDL [code="sql"] --Table 1 CREATE TABLE [dbo].[Indexing2]( [Col1] [int] IDENTITY(1,1) NOT NULL, [Col2] [int] NULL, [Col3] [int] NULL, [Col4] [int] NULL ) --Indexes CREATE UNIQUE CLUSTERED INDEX...

If value is null, use previous records value - Hi, I have a table with exchange rates between 2 countries. There's 90 days of history for every exchange rate. I've...

Changing the table structure - Hi, The below table is my source Skill BU1 BU2 BU3 Skill1 0 0 0 Skill2 2 6 0 Skill3 0 0 7 Skill4 4 0 0 Skill5 0 7 8 I would like to convert it into the below table BU Skill Value BU1 Skill1 0 BU1 Skill2 2 BU1 Skill3 0 BU1 Skill4 4 BU1 Skill5 0 BU2 Skill1 0 BU2 Skill2 6 BU2 Skill3 0 BU2 Skill4 0 BU2 Skill5 7 BU3 Skill1 0 BU3 Skill2 0 BU3 Skill3 7 BU3 Skill4 0 BU3 Ski

generating a column for each value in foreign key - hello, i am providing these small example to make myself better understood. [code="sql"] CREATE TABLE #Groups ( group_id bigint NOT NULL, name varchar(100) NOT NULL, grouptype_id...

Append query results to existing table - Hi, I currently run a query daily and have to results going into a table using insert into.. select... The problem is,...

Need an example for Indirect Recursive Trigger? - hi, I need an example simple indirect recursive trigger. Thanks.

IF statement with subquery problem - I have a sub query within my IF statement that evaluates to one single number that I am using for...

Get continuous date with count - hi all, i am having a requirement like this input EmpID reportdate reportname noofdays 47 11/29/2012 Thursday 1 47 11/30/2012 Friday 1 47 12/4/2012 Tuesday 1 47 12/5/2012 Wednesday 1 47 12/7/2012 Friday 1 47 12/10/2012 Monday 1 48 11/29/2012 Thursday 1 48 11/30/2012 Friday 1 48 12/4/2012 Tuesday 1 48 12/5/2012 Wednesday 1 48 12/7/2012 Friday 1 48 12/10/2012 Monday 1 48 14/10/20

Finding combinations of values - My table structure CREATE TABLE trefClientShares{ intLMid int NULL, intMLid int NULL, intISid int NULL, intFRid int NULL } I need to figure out...

query to get the name of a product with only incremental values in the quantity column - i need a query to get the names of the product which has incremental or equal values every year 2009-2012(in...

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

Case Statement for Where Clause - Good afternoon everyone! I'm not a regular developer so please excuse my question for being easy for what would be a...

SQL Server 2008 : SQL Server Newbies

Send email from SQL Agent Job error - Restarting SQL Server Agent service with mirroring? - I have setup Database mail and can send emails from SQL server with no issues. However when emailing a notification...

Huge Index growth - Hi All In the last month, I have noticed massive growth on one of the tables in my database. The total...

COUNT and compare - For some reason I've always gotten confuses when using COUNT! I have two tables, one for Orders and then an Assiged...

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

Retrieve second of three values separated by spaces - My brain isn't working right now. I have data such as 'HEYE-B Euro-IPA 69793' and 'HEYE-B RFE-IPA 70940'. I need...

SQL Server 2008 : SQL Server 2008 High Availability

'This server supports version 612 and earlier' failover problem - We upgraded a SQL2K5 standard edition cluster to 2K8. Now receive error during failover "The database 'master' cannot be opened...

SQL Server 2008 : SQL Server 2008 Administration

moving data files of system databases in cluster - Environment:sqlserver2008R2 instance on windows server2008R2 2-node cluster. I want to copy data files of system database in a seperate folder,but...

Fill Factor and Backup Compression - How does the Index Fill Factor influence the size of a compressed backup? Is the fill factor space that is...

Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch - Hi, Just run SQLIO on my new Cluster and got very interesting results that I would like to share and/or get...

Slow Week - Often, the weeks leading up to the holidays are quite slow. Lots of people on vacation, so little gets done...

SQL Monitor - Is anyone using SQL Monitor to monitor their SQL instances? Per this thread [url=http://www.sqlservercentral.com/Forums/Topic1394075-1550-1.aspx][/url], I'm looking to monitor about 40...

Index Performance. - Hi all, I search a lot for this issue but I can't realize what happen. The case is, randomly (no very...

How to find allocated space and used space - Hello Team, How to find allocated space and used space for all databases in sqlserver instance ? Is there any query. Thanks in...

Shut down server for maintenance - We have some server maintenance needed at weekend. We plan to shut down web server first, then application server, then sql...

Problem with Datetime Function - I have SP which contains function convert(varchar(12),[datetime],112) =convert(varchar(12),getdate(),112) this takes lots of time during the Market hours and it is very...

SQLServerCentral.com : Anything that is NOT about SQL!

What are you planning to do in 2013 in the database domain? - 2013 is on the way. Warm greetings to all of you first. I am posting the questions here to collect 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...

Data Warehousing : Integration Services

Error: The package is encrypted with a password. The password was not specified, or it is not correct. - I get the following error with one of my packages: [code="plain"] Error: The package is encrypted with a password. The password was...

Data Warehousing : Strategies and Ideas

SQL Server 2012 VLDB Data Warehouse Design Question - Appreciate feedback to this question. I'm working on a design for VLDB that will be in the several TB size...

Data Warehousing : Analysis Services

Moving Cubes from one drive to another - hi i am trying to move my cubes from on drive to another (2005). i have stopped the analysis service and...

Loading change data from AS400 (or other source systems) - Greetings all! So I will keep this concise as I can; I have a source as400 system I am extracting data...