In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Is your SQL Database under Version Control?
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.
 
SQL Storage Compress Compress live data by 73% 
Red Gate's SQL Storage Compress reduces the size of live SQL Server databases, saving you disk space and storage costs. Learn more.
 
SQL Connect Keep your database and application development in sync
SQL Connect is a Visual Studio add-in that brings your databases into your solution. It then makes it easy to keep your database in sync, and commit to your existing source control system. Find out more.

In This Issue

SSC Clinic: Finding the rogue query

When you're getting spikes of high CPU, it's quite likely it's a rogue query. But how do you find out which query? Grant Fritchey explains, using SQL Monitor. More »


Ten Things I Wish I’d Known When I Started Using tSQLt and SQL Test

The open-source Unit Test framework tSQLt is a great way of writing unit tests in the same language as the one being tested. In retrospect, after using tSQLt for a while, what are the 'gotchas'; those things that you'd have been better off knowing about before you get started? David Green lists a few tips he wished he'd read beforehand. More »


Webinar: Temporary Tables in Oracle and SQL Server

Once again Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will host a live discussion on Oracle and SQL Server, this time in relation to temporary tables. Will they agree on some common ground? Or will it be an out and out argument? Either way, be prepared for a lively exchange that will not only entertain, but will teach you key concepts on Oracle and SQL Server. More »


SQL in the City - Seattle 2012

Start the week in Seattle off with a free day of training on Nov 5, 2012 with SQL in the City. Grant Fritchey, Steve Jones and more will be talking SQL Server in the Pacific Northwest. Join us and debate and discuss SQL Server the Red Gate Way. More »


From the SQLServerCentral Blogs - Reading Reviews, Ordering a Laptop, and Lenovo

Since earlier this year I’ve been thinking to replace my aging but still useful laptop, mostly to get something lighter,... More »


Editorial - More CPU Means More Data

Moore's law has held true for computer resources for a long time. I don't know how long it will continue to work in the future, but I am interested to see if it can continue to work for the rest of my career. I don't know that it will, with our newer CPU designs approaching the limits of physics, but I'm sure that research is under way to find ways to vastly improve our processing capabilities. Look how far we've come from the Apollo guidance computer, which landed 3 men on the moon, and had a fraction of the power of almost all of today's cell phones.

I ran across an article that talked about modeling sailing conditions to help sailors design faster and more stable boats. Just like many other types of modeling, this is a way to use computer simulations of various real world events to test designs of new products. This can save lots of time and money as fewer prototypes can be built and many more design ideas can be tested before a final design is chosen. More intensive modeling means more processing power is needed, but it also means more data is needed.

We see this more and more in business. We want to get answers faster, often by running more complex business simulations. To get our answers faster we need more processing power. However as we upgrade our machines and enhance our CPUs, we also add more and more data to our databases. The additional data, while beneficial to making better decisions, also slows down our queries. The additional data is also a challenge to load, manage, and store, which is something we need to ensure we are learning to do better.

Processing costs are going lower and lower. These days with the large clusters in the cloud that anyone can rent for a few thousand dollars, extremely complex simulations can be run. The code to run them, however, is still something that we need to produce. Just as the scientists  have developed complex models that accurately test new designs, each of us must learn how we can write more efficient queries that analyze our business data and take advantage of the constant improvement we see in processing capabilities. Otherwise the advances in CPU power might be negated by rapid data growth.

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

Can the following script be made to run without changes to the script and without error?

DECLARE @Test varchar(30)

SELECT TOP 1 @Test=name FROM sys.databases

PRINT @Test

NotGo

DECLARE @Test varchar(30)

SELECT TOP 1 @Test=name FROM sys.tables

PRINT @Test

NotGo

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 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Yesterday's Question of the Day

Peter Piper picked a peck... 

Running the following query in SQL 2008 R2:

CREATE TABLE #tbl_Avacado (
  veggie VARCHAR(15)COLLATE Latin1_General_CS_AS NOT NULL
  );

INSERT INTO #tbl_Avacado
VALUES ('Pepper'),('PEPPER'),('pepper'),('p3pp3r'),('peppers');

SELECT veggie FROM #tbl_Avacado
  ORDER BY veggie;

DROP TABLE #tbl_Avacado

What shall resulting order shall be?

Answer: p3pp3r, pepper, Pepper, PEPPER, peppers

Explanation: Numbers sort first. When the case sensitive sort order is used, lower case characters sort before upper case characters. In dictionary (as opposed to binary) sort, peppers sorts after all the pepper entries.

As an exercise for the reader, plug in the Latin1_General_BIN collation, and watch the upper case characters sort to the top, and p3pp3r gets mixed into the middle.

Ref: Windows Collation Sorting Styles - http://msdn.microsoft.com/en-us/library/ms143515%28v=sql.105%29.aspx

» Discuss this question and answer on the forums

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.


Featured Script

Query to check whether my Full Database Backup is running fine.

The query will help to find whether my database backup to a specific instance of SQL Server is running fine. If HH> 1, this would signify that the backup is running, else this is a failure. 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

SQL 2005 Express edition installation executes error (Data source name not found and no default driver specified) - Hi guys, I tried installing sql 2005 express edition setup for a test purpose in my test server configured with Windows...

Reg: Not Connecting Named Instance through Public IP - Hi In my production server i installed sqlserver 2005 in default instance and one more named instance also .In this...

SQL Server Jobs issue - We have faced a scenario in which jobs are scheduled to run on daily basis,but one day what happened that...

Partitioned tables - We've a very large table and I'm thinking to partition it. Either one of these 3 main columns(appid, date or...

Where is log for cube process errors ? - Hello, When I process one of the cubes I get errors. Can anyone tell me where these "cube process errors" will...

Deattaching a replicated subscription database - hi, what is the best way of get rid of the log file from subscription database. it is growing big in...

Change Database Status from Shutdown to Normal - HI, We have a database that the status is set to Shutdown. How can I change the DB Status to Normal. Thanks,

Removing mirroring session on a partner database in Mirror, Disconnected / In Recovery state - I am trying to remove mirroring on a partner server database due to a network issue. Here is the scenario. We...

compatability level - Hi, what is cmpt level? why should we change cmpt level if we move database from sql2000 to sql2005 ? thanks in advance.....

SQL Server 2005 : Backups

Backup is taking much time - Hi Experts, Today one of my database took more than expected time for backup i have no idea why it took...

backup file size - Can anybody help in this matter Original database size [b]100 MB[/b] Backup files created, in scheduled time but size was [b]357...

SQL Server 2005 : Business Intelligence

process - Hi All, in my solution we need to process weekly sasles cube through a ssis package. for this purpose there is a...

SSIS Package Fails - Hi All, The SSIS Package Fails saying "The AcquireConnection method call to the connection manager "Excel_Source" failed with error code 0xC0202009" Here...

Analysis Services not available in 'Server Type' drop-down - Hi there, First post for me here. I've just upgraded from 2008 R2 Express to 2008 R2 Enterprise in order to work...

SQL Server 2005 : Data Corruption

How can i drop table? - Dear all, I have an error in my database. I write command that is 'Drop table tb_name'. When i run that command,...

SQL Server 2005 : Development

Neew help in Query please - I need to do a pull in each db.... Thanks in advance for help... --------------------- Server: Msg 55555, Level 16, State 1,...

SQL Server 2005 : SQL Server 2005 General Discussion

Assemblies in SQL server 2005 - Hi All, I have created a new assembly in the sql server database, by using the below code below: --Create the required...

TRY CATCH 2005: SSMS still sees error (2012 does not) - Here's an interesting little thing I've found - quite possibly it's a SET or other connection parameter, but I'm really not...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Execution plan - Hi All, I have 2 sql sevr 2005 instances with same build no,same edition. I restored the database from one...

SQL Server 2005 : SQL Server 2005 Integration Services

Issue with slowly changing dimension component - Hi all, First time i am trying with SCD component. Just a hands on practice to know about the component....

Problems importing CSV files using SSIS. - I have received some CSV files that I need to simply import into SQL 2005 tables. These files open cleanly...

Processing was cancelled due to an interrupt. SQLSTATE=57014 - My SSIS package throws below error while fetching data from DB2 to SQL Server. SSIS package fetch 50 tables from...

SQL Server 2005 : T-SQL (SS2K5)

Table update using a hierarchy - Hi I need a table updating using a lookup. In my example, Table1 has 3 AccType columns which need to be...

How to get week of a mont with input parameter date. - Hi, i have a question, How to get First week of a actual month? In my procedure i will send a parameter date:...

Minimum Value 3 Fields > 0 - This seems like it should be easy but I can't seem to come up with a simple formula. Need the...

Need a SQL query to list the table names used in a view. - Dear All, I have a view with 7 to 9 tables and it is running slow so i need a query...

Is there an equivalent of Excel's NORMDIST function in SQL Server 2005? - Is there an equivalent of Excel's NORMDIST function in SQL Server 2005?

SQL Server 2005 : SQL Server Newbies

Restore database problem in SQL 2008 - Error encountered: RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error:3241) Any inputs?

SQL Server 7,2000 : T-SQL

qyery performance - other than sql profiler and database engine tuning advisor any other tool in sql server2005 for query performance.

Querying ADSI - I am having trouble querying one particular active directory attribute, managedObjects.  The error message that I'm getting is: Msg 7346, Level 16, State...

SQL Server 7,2000 : SQL Server Agent

Please Help Solve Why SSIS Package Fails When Run as SQL Agent Job - We have SQL Server 2005 x86 locally. On it we have a SQL Agent Job that runs an SSIS package...

SQL Server 2008 : SQL Server 2008 - General

Archive Disks SQL Server 2008R2 - Good Day, we need to archive old data from a live database to the archive files in the same database as...

SQL 2000 Download - Anyone know where I can download sql 2000 from. Don't ask.(why does he want sql 2000) Thanks

Perform a count and then another count - Hi, I have multiple users per account ie Acc A User1 User2 User3 Acc B User4 User5 User6 User7 etc.... I first perform a count(*) of how many users there are...

The Archaeology of Rules - So I have this old SQL 2000 database that has been upgraded to SQL 2005 and will soon be upgraded...

SQL Replication - Can this be accomplished through Replication? - Hi I would like to know what replication model I should use to achieve the following if even achievable. This is...

Case When Help - I am trying to assign Active or inactive based on member termdate. A member may have multiple termdates. The logic...

Should I configure the second sql instance to use same C:\ drive root directory path as the exiting Default instance? - What are the guidelines for the the root directory path for a second sql server instance being added to the...

Recursive CTE for BOM Structures - Hi Guys, I am exploring the possibility of changing an inherited stored procedure to use Recursive CTE. Basically I have 2...

Remotely Connect and Query a db. - Hello. I have a SQL 2000 database used by a scheduling application on (we'll call it), [b]Computer A[/b] . I also have...

Tempdb : ONE Data File per cpu - Good Day, w had an auditing team running Microsoft Assessment on our database servers . This product recommends that the principle of...

guide me for 70-451 preparation please. - Hi guys, i am done with 70-433. could you please guide me for preparing 70-451 exam, as i could not find any...

Default Language Setting of Database - Hi All, Our default language setting for a DB is set to US-english , but our client insists now that it was...

Install SQL Server 2012 in multiple machines using Configuration.ini - Dear Friends, Recently I went through an article about installing SQL Sever 2012 using a configuration.ini file. This procedure basically standardize...

SQL Server 2008 : T-SQL (SS2K8)

Performing a ALTER TABLE then modifying data in same transaction scope? - I'm writing a SQL change script and finding it difficult to get the changes performed in an atomic operation. We typically...

Need Help In Query . - Could you please help me find records described as per below scenario. Campus code,address,CampusID(PK) Data:- DEL,ABC,1 CHN,DDJ,2 MUM,PQR,3 BNG,WRT,4 PUN,BNM,5 Enroll StudId,CampusID,EnrollDate,LstAttendDate,Enrolid(PK) 1,2,15-06-2012,NULL,1001 1,5,31-05-2011,31-05-201,1002 ChangeStatus StudId,Enrolid,NewStatId,PrevStatId,ChangeID(PK) 1,1001,1,0,5001 1,1001,2,1,5002 1

Need help in T-sql - hi, i am having data in 3 tables need to join the data and get the result set, when getting there...

Query for treeview - I have a query which is working fine. Is it possible that if the table3's column(Child) is only related to table...

Not in clause vs list of <> - Taking the examples: EXAMPLE A Select AColumn from ATable where AnotherColumn not in(1,2,3) VS EXAMPLE B Select AColumn from ATable where AnotherColumn <>...

NOT IN query very expensive, 100% CPU - Hello all. Hopefully someone will be able to help me out with this query. The below query takes 42 minutes, returns...

Pulling back a distinct column - --Basically this query pulls back all the outstanding invoices for sold cars, the main issue I am having is that...

[Help Needed] Looking for a solution for this case - Hi all, I have a task which required me to sum value of all children level from bottom to top...

ALTER COLUMN fails when changing datatype of PRIMARY KEY - I am currently working on a transaction table that has an IDENTITY column of type INT, which also has a...

T-SQL Help Needed - CREATE TABLE Issue ( id int not null identity(1,1) primary key, name varchar(10) ) CREATE TABLE Jrn ( id int not null identity(1,1) Primary Key, issue_id int, created_dt datetime ) ALTER...

How to insert empty row - I need to insert empty row in a temp table within a stored procedure. The data types are varchar, varchar,...

rCTE vs LIKE for Hierarchy - Hi people, I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID,...

UPDATE when the values are the same - Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating...

OPENROWSET and Microsoft.ACE.OLEDB.12.0 ERROR - Hi all, Hope you all are doing fine... (unlike me..).. I am trying to run the following query [code="sql"]SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel...

SQL Server 2008 : SQL Server Newbies

Unable to log into SQL Server after joining to Domain (issue is resolved, informational question only!) - So I have an SQL Server at home for "playing" with. I recently joined this system to a domain I...

Compile SQL Reference Table query into VS 2010 - I am completely new to SQL server i know a few of the basics. I have a VS2010 asp.net c#...

Bulk insert of large files (more then 450MB) - Hi everyone I have to populate a table with a pretty large text file and I'm happily using the very good...

log_reuse_wait_desc column says LOG_BACKUP - what should i do ? - I'm using Sql Server 2005. When i tried to compile a stored procedure i got the below message The transaction log for...

CONVERT varchar (50) to Decimal (4,4), 2012 Server Express - Greetings, I'm about as newbie as they come. I am using SQL Server Management Studio Express 2012 on a Win7 machine. BACKGROUND:...

SQL Server 2008 : Security (SS2K8)

How to Secure the MDF and LDF File with Password - hi, I've been working on sql server. I want to protect the mdf file with password. I develop a windows application with sql...

SQL Server 2008 : SQL Server 2008 High Availability

Mirroring Error 1479, 1474 - Hi, OS - win 2008 R2 x64 enterprise edition SQL - MSSQL R2 x64 enterprise edition with sp2 Pl. suggestion me, how to avoid these...

setting up Clustering - SQL Server 2008 R2 - I have a couple of questions about installing SQL Server 2008 R2 Standard in a 2 node active\passive cluster First, a...

Would a smaller number of VLFs in the log speed up the redo phase of recovery of a mirror snapshot? - From time to time, when an intensive log operation takes place on the principal database, we observe very long snapshot...

Tlog backup on princple server? - Hi, one of the production server TLog backup taken every 30 minutes. the same server going to implement HA for database...

SQL Server 2008 : SQL Server 2008 Administration

Query Performance Investigation - Hi All I'm using the following script to investigate the plan cache and query performance I wanted to confirm something, What is the...

page writes/sec high on mirrored server - Hello I have a mirrored server in which the SQLServer:Buffer Manager: 'page writes/sec' is high, it averages around 280 with the...

SQL Jobs monitoring tool - We have a large number of jobs running on SQL DB servers. Is there a tool to find out what...

Auto encryption of new databases - I am using SQL 2008 R2 Enterprise with database encryption. Performance is fine. I just wonder if there is a way...

SQL Server Audit - I have a requirement 1. when a user logon I would need to log the below information in a table....

"Client Tools Connectivity" needed? - I'm having problems with getting SQL Server Agent to run after a in-place upgrade. When doing the upgrade, I didn't...

Finding port numbers - Hi, can any one tell me the script for finding the port number of sqlserver

Job Failed. - While i was starting the job i got following error. JOB RUN: 'Full OnlineLogging' was run on 9/13/2010 at 3:03:52 AM DURATION: 0 hours,...

Career : Certification

MCM Info - Hi All Does anyone know where I can find braindumps for the Microsoft Certified Master SQL 2008 Exams? Just Joking!!!! :w00t: Anyway, what...

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Programming : XML

The argument 1 of XML datatype method nodes must be string literal.plz help - declare @starttag varchar(100) declare @root varchar(100) set @starttag ='XML' set @root ='XML' Declare @xml xml Set @xml = '<XML> <Provider> <providerID>1</providerID> <Address>address1</Address> </Provider> <Provider> <providerID>2</providerID>...

SQLServerCentral.com : Anything that is NOT about SQL!

Crazy Interviews - Reading the topic 'Stupid Interviewer Tricks' reminded me of an interview where I couldn't tell if I was crazy or...

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

Reporting Services : Reporting Services

SSRS No Longer Printing! - Hello, Having just started it with SSRS on 2008R2, I have now come across a serious problem which prevents me from...

Report Naming Standards!!!? - I have inherited a mess in the SQL Server Report Monitor and the current company that I have recently started. There...

Expand Drilldowns automatically when print report - Hi people, do you know a way, when a user prints a report, to expand automatically all drilldowns? All this in...

SSRS/BIDS Indicators - I want to expose indicators based on a specific text, like "True" for example. If the value in the textbox is...

Grouping Values in a Report?? - Hi, I have an SSRS report to build that looks like this. Name Count Total BIKE COMPANIES 50 3000 HELMET COMPANIES 10 200 The problem...

SSRS splitting Data into 2 columns (newspaper style) - Hi, Ive been having some issue with splitting my data into two columns on SSRS. I was hoping to be able...

Database Design : Design Ideas and Questions

Database Design - Using a single common table for all 'Entities' - Hi I am after a bit of advice.... I am considering the design for some tables in my database and wondered...

Data Warehousing : Integration Services

Excel Import - row order - Hey all, When you import data from SSIS - how does it work with the row order? Does the row order stay the...

SSIS Package Fails - Hi All, The SSIS Package Fails saying "The AcquireConnection method call to the connection manager "Excel_Source" failed with error code 0xC0202009" Here...

Need to implement the Business rule row by row - Hi, How can we agonize,Row by row like cursor in SSIS, what controls will be using, Here am reading data from...

First Time Migration of a File System 2008 SSIS package from Dev to Prod - Hi All, I developed SSIS packages in our development server. I used my windows id in the development. The packages are...

Integration Services contain Analysis Services Processing Task. Fails but works in Analysis Services - The dimensions have processed Successfully prior to the updating of the SEN Cubes The attribute key cannot be found when processing:...