Join Operations – Nested Loops

Jason Brimhall from SQLServerCentral.com

This is part I of III in a series on the physical join operators in SQL Server. More »

Silent Truncation of SQL Server Data Inserts

Additional Articles from MSSQLTips.com

There are certain circumstances where SQL Server will silently truncate data, without providing an error or warning, before it is inserted into a table. In this tip we cover some examples of when this occurs. More »

SQL Intersection in Las Vegas This Spring

Press Release from SQLServerCentral.com

Come join Steve Jones, Grant Fritchey and an amazing lineup of speakers at the spring SQL Intersection conference in Las Vegas, April 8-11, 2013. Register now and you get a Surface RT tablet. More »

From the SQLServerCentral Blogs - SSRS 101 – Prerequisites

Before I actually start explaining and demonstrating how to build SQL Server Reporting Service (SSRS) reports there are few things... More »

From the SQLServerCentral Blogs - SSIS 6 Million Rows in 60 Seconds using standard laptop

KatieAndEmil from SQLServerCentral.com

SSIS is a very powerful tool and can be very fast however I frequently come across situations where performance could... More »

You come into work one day and as you sit down, your phone rings. It's one of the business groups complaining that the database is running slow. You check the server and find CPU at 80%, 800 pages/sec, disk IOps of 230 and 124 transactions/sec. Is the database the problem? 

Good DBAs know that baselines are essential. If you don't know what values to expect from your server, it's often hard to determine if the system is running slower than normal. Normal is something you need to define for each system, preferably in an automated way that updates your baseline over time.

When building a baseline, however, how do you average out the information?

That's the poll this Friday. Let's assume that you are examining the CPU percentage for a SQL Server and you have data points from every 5 minutes across the last month. What's the average? Do you take the straight average? Do you break this down to hourly segments and then create further analysis that looks at different business periods?

It can become problematic very quickly. Many of us have slow and busy periods. Do we want an average that's perhaps lowered by the slow periods in our workload? Do we want to break out the averages for maintenance periods separately from normal operations? If you are looking to compare today's values, do you look at yesterday's for the same time period? Last week? An average of all points across the last week?

Let us know what methodology you use and if you'd like to describe it in more than a paragraph or two, we'd love to have some articles published here on the site.

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 - 22.0MB WMV
• Watch the iPod Video Podcast - 17.1MB MP4
• Listen to the MP3 Audio Podcast - 3.5MB MP3

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

Today's Question:

--
-- Oops! SQL column swap or SQL column clobber?
--
create table #temp (PK int identity, c1 int, c2 int);

insert into #temp values (1,2);
insert into #temp values (3,2);
insert into #temp values (2,1);
insert into #temp values (5,2);
insert into #temp values (5,3);

select * from #temp order by PK;
--
-- Swap columns c1 and c2 on a
-- row-by-row basis if c1 > c2
--

update #temp
 set c1 = c2, c2 = c1
 where c1 > c2;

--
-- Will column c2 clobber column c1
-- BEFORE column c1 can replace column c2?
-- Do we need a temporary column
-- or variable to correct this?
--
select * from #temp order by PK;
--

drop table #temp;

By Thomas Kovarik

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.

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Can we create another computed column that depends on already created computed columns?

Consider the following table creation

CREATE TABLE COMPUTED_COL
(
A INT,
B INT,
c int,
d AS (A+B)
,e AS d+C
)
;

By Srikanth Soundararajan

Answer: No

Explanation: This is not possible. You will receive the error:

Msg 1759, Level 16, State 0, Line 1
Computed column 'd' in table 'COMPUTED_COL' is not allowed to be used in another computed-column definition.

Ref: Computed columns - http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx
Also, this thread: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/55b5daee-cc48-4864-b633-63a4c07a4903/

» Discuss this question and answer on the forums

SQL Server 2012 Integration Services Design Patterns

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

Get your copy from Amazon today

Generate Repair Statements for Not-Trusted Foreign Keys

Orestes Zoupanos from SQLServerCentral.com

Generates "check this foreign key" statements for all not-trusted foreign keys on a server. More »

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

SQL Server 2005 : Administering

Issue in deadlock in our production server - We are facing dead lock issue on prod database, and checked sp_who2 lot of blocking is there, more than 25...

Any recommendations on this - Manage Statistics ? - I would like to get some feedback on how other DBA's would manage db's in this situation: We have databases (sql...

10 physical disks available - best configuration for data/log/backup? - I have 10 300GB SAS 15k rpm 6Gb disks available to me on a HP P2000 SAN. I have a...

SQL 2008 R2 - Can't see services in SSCM or status of server in SSMS - I was getting a WMI error launching SSCM so I followed the advice of this article and ran the mofcomp...

java.net.SocketException: No buffer space available (maximum connections reached?): connect - Hi, I am using MSSql server 2005 SE with Windows 2003 Server 64Bit SP2 with 8GB RAM. I am using Java...

pass variables between SQL Server tasks in a JOB - I'm using Sql Server 2005. I'm trying to pass a variable between the tasks in a job. NOT SSIS. We...

SQL Server 2005 : Business Intelligence

How to set user for deploying reports? - When I deploy my report from BIDS (2008) it doesn't ask me for a user name, it picks a user...

SQL Server 2005 : Data Corruption

Found a database error and need advice - I was testing some backups on a test server and found that a database generated an error when running CHECKDB...

CheckDB fails Sunday, but ok Monday without repair - Hi all: Early Sunday morning, our daily check of database integrity failed on one of our databases. The task is not...

SQL Server 2005 : Development

Query tuning - Hi, I had a performance issue in our stored procedure. Later I found out that the below simple query took 36...

SQL Server 2005 : SQL Server 2005 General Discussion

Barcode 128 generator (using scalar function) - I'm looking for a barcode generating Function that uses the standard Code128. Apparently no function that fixes this has been...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Join's and Index's - I am fuzzy on this.. so looking to see if my thoughts are correct... When looking at the execution plan,...

SQL Server 2005 : SQL Server 2005 Integration Services

Identity column import - I need to import data of a table from prod environment to my dev DB. This table is having one identity...

SSIS very slow compared to DTS - I have a simple package in SSIS that runs extemely slow compared to a DTS package that does the same...

Returning Values from a Stored Procedure (SQL TASK, SSIS) - Hi all, Here the problem: I have the following stored procedure: [font="Courier New"][size="2"] ALTER procedure [admin].[up_SetLogicalDate] @QueryDate datetime ,@LogicalDate datetime OUTPUT ,@LogicalDate_Start datetime OUTPUT ,@LogicalDate_end datetime OUTPUT --with execute as caller as begin DECLARE ...

SQL Server 2005 : T-SQL (SS2K5)

Can I Provide SP to 3rd Party without tell them DB name - I assigned to prepare SP API for vendor, but I concern about script and DB security. I have to provide 2...

Getting Child-Top Level Parent Relationship without looping - Hey all, Given the following: [code] CREATE TABLE #Transfer ( ID INT IDENTITY PRIMARY KEY, OldKey INT, NewKey INT ) INSERT INTO #Transfer (OldKey, NewKey) VALUES (1, 2) INSERT INTO #Transfer...

Adding comma separator to INT datatype - Hello to all gurus here! Please excuse the dumbness of this question, but if I have an INT value of 999999...

INFORMATION_SCHEMA.COLUMNS reference - How do you reference INFORMATION_SCHEMA.COLUMNS from a linked server? When I try doing that, I get the below error message: select * from [server_2000].dbst.information_schema.columns Msg...

SQL Server 2008 : SQL Server 2008 - General

Why I need to assign Grant/Deny Permission after every database restore ? - I need to restrict user which was achieved by grant and deny permissions using TSQL in sql server 2008 R2...

Script to send alert when jobs runs more than 24 hours - Can someone post the script which would send out an email notification if any job on a server runs longer...

backup logins - I got below error message when I run backup logins using schedule job….When I run manually from the Job, instead...

Proper way to concatenate values in a set from 1 column - I was asked today to look at a colleague's code on concatenating values returned from 1 column. He said that...

Need to copy data for a period of time. - Hi, I need to copy data from PROD to QA between 29th Jan 6am and 10am for few tables. The Tables...

Stored Procedure as Data Source with SSRS - Hi All, I have a stored procedure with multiple paramaters like Start Date- custom calendar End Date-custom calendar Status - they contain multiple values Group namethey...

"kind of bug" with sql server 2008 - Seems like I posted my original post in wrong topic so putting it here again. Please ignore my old post...

I need to read the xml which is passed as an variable in stored procedure ?how to achieve that ? - I need to read the xml which is passed as an variable ,and insert its values to temporary table @msgchunk...

logical consistency-based I/O error - Hello, when i want to see properties of one data base ,or run maitenancetools or backup sqlserver give me this...

Avoid getting messages while executing Stored Procedure - Hi, When i execute an sp am getting the below message. [quote]"Database name 'tempdb' ignored, referencing object in tempdb."[/quote] In this sp...

Assign the variable based on the input - hi, I am having a requirement like this say if two table name is the input i will take 2 tables and...

What Gives Better Performance? - Hello Everyone I hope that everyone is having a Fabulous day. I was in a discussion with a DB2 developer that has...

Batch script - Need help on batch script to delete files located on different servers and should be addressed in one script.Lets say...

Script object into a text file - Hi All, After this: EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE --This works [code="sql"]declare @str varchar(100) select @str = 'declare @i int' print @str select...

Seems simple, must be hard!! Cluster Instances, Linked Servers -- adjust Max Memory?? - This seems like it should be so simple, but apparently not! I have a 2-node, 2-Instance SQL cluster. Each server has...

Behaviour in partitioned database. - Hello all, At the moment I am trying to come to grips with partitioning. I am trying a number of scenario's to...

Help explaining query results. - Hi Guys, I was working on a script that would show all records in Table A that didnt have corresponding records...

Failed Jobs report - Hi Guys, I need a script that will send me a complete list of failed jobs by looping through all the...

Shrinking log is bad? - Hi All, I am aware that shrinking leads to fragmentation and doing it often is a very bad practice. Shrinking mdf...

database restore 2008 r2 - Hi all, approximately how much time the sql server 2008 r2 will take to restore 66GB of .BAK file. Environment: windows...

An easy way to synchronize data - We are about to begin User Acceptance Testing. The business users want to use a copy of "live" production data...

Deadlock Issues (Why Certain Locks?) - I am having some problems with frequent deadlocks in a vendor-supplied database and I have been analyzing the database and...

Unable to install sql server 2008 Management studio - Dear Sir/Madam, I downloaded Visual Studio 2010 from microsoft so its automatically installed sql server 2008 R2. In Sql server 2008...

set SSRS date parameter default value to previous date(getdate()-1 - Hi, What is the expression to set the default date of date parameter to getdate()-1. I want the SSRS report to...

2008 R2 upgrade advisor error - Hi I have just started getting issues with the SQL 2008 R2 upgrade advisor. This tool has previously worked ok for...

SQL Server 2008 : T-SQL (SS2K8)

Searching through text field - I have a text field (nvarchar max) that contains a chat transcript. I need to find the first chat entry...

Max function without group by - Hi guys I have two tables: tbEtapeProjet and tpEtape tbEtapeProjet(idetape,idprojet,debut,fin) contains 1 1 2011-07-01 00:00:00 2011-09-01 00:00:00 1 2 2012-05-01 00:00:00 2012-05-10 00:00:00 1...

FILESTREAM freetext query issue. - Hello all, I've created my first filestream store on SQL 2k8 Express adv. Created my Full Text Catalog, and index...

identity table use case - SQL 2008R2 How to [b]insert / retrieve 3 identity values[/b] without using GO 3 ??:-) Also insert top(3) ... does not work either ... :w00t: Please...

Split String into 3 parts with a twist! - I have the following sample data (original and desired results) [code="sql"]with cteOriginal (CompanyName, CompName1, CompName2, CompName3) as ( select 'A V H S L...

Import xlsx file to 2008 r2 express - hi folks, i am tired to breaking my head in this simple task, trying to import an xlsx file to...

T-SQL Help with a loop (I believe) - Here is my very basic code below. The results are below that. What I need is a query that reads...

Help on how to count guests in hotel every day - I have a table keeps guest records in a hotel. For each guest I have check in date and check...

SQL Server 2008 : Working with Oracle

export/import error - I am trying to load tables from sql server to oracle using import/export wizard in SQL Server. Only 2 tables...

SQL Server 2008 : SQL Server Newbies

create table script generation - Dear All I have created table with column constrains, default values. When I generate the script for this table from SSMS...

Worktables & Hash Tables - Hi All After testing and researching - I want to make sure that my understanding is correct regarding Worktables & Hash Tables >Worktables & Hash...

RedGate Data Compare Multiple DB's - First off I have no idea where this should go, I figured id get more forgiveness for putting it in...

Dev Licensing questions!? - hi all, trying to get my head around licensing. specifically in a dev environment. Could i get some feedback on...

SQL Server 2008 : Security (SS2K8)

Cross DB Chaining Madness - At the risk of posting yet [i]another[/i] message on cross chaining... I'm at my wits end. I've read and read...

Script Logins - Hi All Is there any way to script out all the Logins on a SQL Server instance, including server level privileges,...

Encryption basics. - Hi all. I am researching the feasability of encrypting a few columns in one of our databases. I have done...

SQL Server 2008 : SQL Server 2008 High Availability

Jobs not running in cluster - Hi , We have cluster environment with mirroroing . We have configured jobs to take the fullland log backups(all jobs in that...

SQL Log Shipping out of sync - Comparing Log shipping Primary and Secondary Servers - Hello all, I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional...

Mirroring Log Issue - Hi All, I have a db with mirroing in place and log is growing about 49GB.....Log is growing since log backup...

SQL Server 2008 : SQL Server 2008 Administration

How to find the amount of memeory SQL Server is consuming - Hello I need to build a report on capacity and am trying to find a counter that I can use to...

SQL08R2 Database Mail - I am so ready to scream, I set up DB mail and restarted the SQL Agent services but it fails...

SQL Server 2008 Enterprise SP upgrade - We are planning on doing a SQL service pack upgrade soon. Should we take the databases offline, or can the service...

Deadlock Graph from the xml from system_health extended event - How to get the Deadlock Graph from the xml extracted from system_health extended event. I'm using SQL Server 2008R2 SP2, and...

Low Performance - High CPU Problem and others... - Hello everyone, I got the Problem, that my SQL Server has always 97-99% CPU usage. We have snapshot isolation mode enabled,...

Red Gate SQL Monitoring fails - I just started a new DBA job and was introduced to SQL Monitoring from Red Gate. The program is not...

Converting BLOB to different datatypes - Hi Experts, Is it possible to convert a java object which is now stored as a BLOB field to character values....

Service Pack Update error - Hi I have installed SP2 on 2008 R2 successfully,but when i tried to connect got error Login failed for user '<Domain...

SQL Maintenance job - Manual run/Scheduled run - Hi All, We have a weekly full backup job, daily differential backup job and hourly transaction log backup on our servers....

An existing connection was forcibly closed by the remote host. - We are going live tomorrow in a completely new environment for our PeopleSoft HR and Finance systems. We are getting...

SQLServerCentral.com : Anything that is NOT about SQL!

Ignite Confio for SQL - Has anyone used Ignite for SQL Server monitoring? I have it installed for my SQL Servers but not allot of...

Web application design query - Hello, I am new to web application designing and using .net (MS Visual Studio 2010) to design an application. The issue I...

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

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

SQL report returning slowly - I have a report running on our production Reporting Web server that is just spinning. When I run the procedure...

How to schedule SSRS Report - Hello How to schedule SSRS report to run (in email delivery option) every Monday and Tuesday and every hour between 6:00...

Reporting Services : Reporting Services 2005 Development

Line hight for barcode - font 3 of 9 Barcode - I am developing a GRN label print report and have installed font 3 of 9 Barcode. I want the barcode...

Database Design : Design Ideas and Questions

SEQUENCE Problems - Hello. I've started using a SEQUENCE in a table instead of an identity. I seem to be experiencing problems of the sequence...

Data Warehousing : Integration Services

A small SSIS Package executing with errors. - I am facing trouble while executing an SSIS(2008) Package which loading (90000 records) data from SQL Server 2008 to Excel...

Combining BSIDS Visual Studio and C#...? - Is there are way to include the C# development environment into BSIDS (Visual Studio)...?

How to consume WCF service in SSIS with SQL Server 2005 SP2 - hello All, Can someone explain me how can u consume a WCF service in SSIS using sql server 2005, the requirement...

Max size in DataType Properties - Advanced Editor - Hello there, ssis 2012. is there a way to make this column more than 4'000? the thing is, this field is Huge (contains...

Add a column with a value from another source - Hello there, i'm using SSIS 2012 . i have 2 sources ( xml files) in 1 data flow and from each source i...

SSIS Config Wizard failing on SQL Server config - We have a SSIS package that has been developed in VS2008. Our company standard is to have package configuration enabled...

Data Warehousing : Analysis Services

How Aggregation is calculated in Cube in SSAS - Hi, I am new in ssas. I want to know the why do we need to create aggregation in measure while...

SSAS browser missing Data - Hi all, I have a new SSAS Cube that I have designed and I am finding that the data doesn't seem...

increasing Excel 2007 "Show Details" limit above 1000 rows? - Hello everyone. I am using the Excel 2007 Pivot Table "Show Details" functionality and find it limited to 1000 rows. I thought...