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

Routine Dependency Visualizer

Angel Rapallo from SQLServerCentral.com

This article contains a T-SQL script that can show you the dependency of all objects in your SQL Server database. More »

Free ebook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from SQLServerCentral.com

We spend much of our working life helping solve SQL Server-related performance problems, hands-on, during consulting, or on online forums. We've seen a few weird-and-wonderful issues but, mainly, it’s the same problems and misconceptions time-and-again. This is our attempt to describe, diagnose, and solve the most common problems with SQL Server 2005, 2008, and 2008 R2. And it's free. More »

SQL Server 2012 Integration Services - Unattended Execution of SSIS Packages

Additional Articles from Database Journal

Quite often, tasks accomplished via SSIS are a part of procedures that run unattended, either scheduled to launch at a particular date and time or triggered by some arbitrarily chosen event. Marcin Policht shares a typical approach to implementing such a scenario. More »

From the SQLServerCentral Blogs - The Fundamentals of Storage Systems – Shared Consolidated Storage Systems

Wesley Brown from SQLServerCentral.com

Shared Consolidated Storage Systems – A Brief History Hey, “Shared Consolidated Storage Systems” did you just make that up? Why yes, yes... More »

This editorial was originally published on Nov 26, 2007. It is being republished as Steve is at DevConnections.

These days it seems that the demand for IT workers is still growing, but there is no shortage of companies still looking to lay people off or get rid of employees that aren't performing up to some standard.

I saw an interesting article on how to better ensure IT job security and wanted to comment on a few of the items listed from the DBA perspective. I think that different groups of IT workers have different tendencies, but DBAs often are in a very strange position in a company and they need to ensure that their contributions are recognized as well as their work is valued and understood.

I remember in the dot-com boom days when "quirky" IT workers were tolerated and even valued. The strange people wearing flip flops and t-shirts could perform wonders with computers and their eccentricities were tolerated. Often when they were just competent at their jobs and no one really understood just how much or little they actually could do with systems.

The world has changed and expectations for most of our systems are higher than they were a decade ago. Management is more realistic in their view of their business, with most of our employers not expecting to get bought out by some large corporation and retire. Our job is to provide stability and long terms strategic value to our companies.

As a DBA, you have a varied job. You're in charge of data, need to technically manage the systems, but also work with business users to ensure their data is properly qualified, the meta data is understood (even if not explicitly written down) and you can help them ensure data quality and recognize the importance of the information that is being stored in databases.

This means that you need to better fit into the business as well as providing value. You should respect the dress codes and other habits of the rest of the company. You also need to learn to communicate effectively with others. Don't talk down to someone with technical acronyms and descriptions and make sure that you are trying to solve the business problems, not fit the solution into come cool piece of technology.

By trying to better fit in, you become an asset to the business as a whole. People should feel comfortable asking for your help and appreciate the work you do.

And they're likely to keep you around for the long term.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.comto get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

• MOV Video - 9.4MB Quicktime
• WMV Video - 10.0MB WMV
• MP3 Audio - 5.5MB MP3

Today's podcast features music by Joe Sibol. If you like it, check out his stuff on iTunes or at www.joesibol.com.

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:

I have the following table

CREATE TABLE #Users1(UserId INT IDENTITY,UserName VARCHAR(8),Sales Decimal(6,2))

Which contains the following data:

UserId     UserName      Sales

1          Joe           100.00

2          Baker         700.00

3          Charlie       400.00

4          Able          800.00

5          XRay          1000.00

6          Easy          50.00

I then execute the following T-SQL statement

SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) sales FROM #Users1 ORDER BY SALES

Select the 2 answers that apply

By bitbucket

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

This question is worth 1 point in this category: LEAD. 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.

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!

--create table
CREATE TABLE [dbo].[Musician] 
( [Musician_ID] [int] IDENTITY(1,1) NOT NULL,
  [Last_Name] [nvarchar] (50) NULL,
  [First_Name] [nvarchar] (50) NULL,
   CONSTRAINT [PK_Musician] PRIMARY KEY CLUSTERED 
    ( [Musician_ID] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] 
)
ON [PRIMARY]
GO

INSERT INTO Musician (Last_Name, First_Name)
 SELECT 'Lennon', 'John'
UNION ALL
 SELECT 'McCartney', 'Paul'
UNION ALL
 SELECT 'Harrison', 'George'
UNION ALL
 SELECT 'Star', 'Ringo'

--1
SELECT First_Name
 FROM Musician
 WHERE Musician_ID in (2)

--2
SELECT First_Name
 FROM Musician
 WHERE Musician_ID in ((2))

--3
SELECT First_Name 
 FROM Musician
 WHERE Musician_ID in (2,3)

--4
SELECT First_Name
 FROM Musician
 WHERE Musician_ID in ((2,3))

Which query (queries) will return an error ?

By Vic A

Answer: 4.

Explanation: SQL Server doesn't perform parsing of the values in the WHERE... IN clause if there is no comma in the list of values. Therefore a single value in the WHERE... IN clause can be surrounded by multiple pairs of parenthesis. Multiple values cannot.

Ref: SELECT - http://msdn.microsoft.com/en-US/library/ms176104%28v=sql.110%29.aspx
IN - http://msdn.microsoft.com/en-us/library/ms177682.aspx

» Discuss this question and answer on the forums

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!

Last Time a Table was Accessed

Jason Clements from SQLServerCentral.com

Last Time a Table Accessed with Create and Modify Date. More »

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

SQL Server 2005 : Administering

sql job - Hello I have sql servers in 7 Office & I neet to check sql agent (Job) from center Office , i have...

Adding an alias name to a clustered instance of SQL Server - Hi there, Does anyone know how to add an alias name to an clustered SQL Server instance? I went onto the SQL...

IS (with nolock) is useful in (multiple join update) Query!! - hi all, IS (with nolock) useful in (multiple join update) Query!! below is an raw example of the update statements. UPDATE xx set...

SQL Server 2005 : Business Intelligence

SSRS Text Box Properties: 2008 - Hi , currently in my reports, i have 3 types of columns. 1. sales 989999 2.sales % 98 3.count 400000 here i need to get the...

SQL Server 2005 : Development

xml - hi well am on fire rite know ,,,,,,:D anyways i am new to sql not as expert as you guys but...

SQL Server 2005 : SQL Server 2005 General Discussion

Using the "IF" function in a column - Is it possible to use the "IF" function in a column at design time as the value for that field?...

SQL Server 2005 : SS2K5 Replication

Mirroring Causing Replication Delay When Mirror Is Unavailable... - Here's the scenario... I have a database which is mirrored to another server. The PRIMARY also serves as a publisher...

SQL Server 2005 : SQL Server 2005 Performance Tuning

SQL client performance - When i'm not at a computer which has sql server express installed I have used winsql My IT dept says that...

Indexes - Hai, 1. what is the use of INDEX's in SQL? 2. Can we create multiple index's on Single table ? if yes, what...

SQL Server 2005 : SQL Server 2005 Integration Services

HOW TO CONTINUE PROCESSING IN A FOR LOOP CONTAINER AFTER ERROR HAPPENDS - ISSUE: HOW TO CONTINUE TO PROCESS THROUGH A SET OF VALUES USING A FOR LOOP CONTAINER WHEN THE CONTAINER ERRORS. SITUATION:...

SQL Server 2005 : T-SQL (SS2K5)

exec @sql - I'm trying to run a simple dynamic sql statment but am receiving the error below. Do I have to create...

Pass variable to Openquery - Hi I am getting the following error message trying to pass a variable into an openquery, can someone point out...

Recursive RunningTotal calculate - Hi All, I need to calculate running total (totalInterestedPaid) for the following temp table (#tmpInterestedPaid) tran_date Calc_interest_accrued Interest_paid TotalInterestPaid 20120908 54.06 NULL 0 20120910 54.06 NULL 0 20120911 24.04 112.35 0 20120913 23.67 ...

Calculating Yield - Hi all, I'm running into an issue calculating yield. [code="sql"];with totalcount as ( select cast(count(sn) as decimal) as total from completedUnit ) select (100 - (cast(count(distinct...

SQL Server 2005 : SQL Server Newbies

Inserting Comma Seperated Values in Sql Server 2005 - I'm Using Asp.Net with C#.. i'm having One ListBox control in which I'm getting Data Dynamically.. I want to Insert the...

SQL Server 7,2000 : SQL Server Newbies

lock table during index creation - Hi, I need to create 2 indexes on a large table (~200 million records). I wanted to lock the entire table during...

SQL Server 7,2000 : Service Packs

FN_GET_SQL missing from my SQL 2000 SP4 installation - I've just discovered that system function FN_GET_SQL is missing from my SQL200 production server. I have it on my SQL...

SQL Server 2008 : SQL Server 2008 - General

Help with slow query - I have a query that is taking 1 minute to complete. I would like to imporve its performance. I have...

tool that can give the list of depricated features in sql 2000 - Hi All, we are in the process of upgrading our SQL servers 2000 (SP1) to sql server 2008 r2 version. I...

Converting HTML entities to Unicode Characters - Hi Experts, I am here with another question. I have a string where i have some HTML entities in between string([b]Please...

How do we calculate the Space used per row - Hi Everyone, Just have a doubt is there any way that we could calculate the space used per row. Thanks

count of non-holiday weekdays - Hi Expert, I want to populate records in my one of the column as per below, count of non-holidays i.e....

HELP HELP Database Error - Microsoft SQL Native Client error '80040e07', Conversion failed when converting the nvarchar value 'Dave Smith' to data type int. /include/aspfunctions.asp, line 1243 ??? - Error is displayed within the excel sheet the database has exported the data into. Only quater of the data that...

100 Most famous interview Questions and Answers - Please go to the following link:- http://sqlcheatsheet.wordpress.com/2012/10/26/sql-server-2008-2008-r2-cheatsheet/ Download the PDF (sqlserver2008r2_cheatsheet_v1-01.pdf). Password: Harinam This is very good document for the freshup your memories...

Index scan on foreign key reference - I have a table Tab1 with a column that is a foreign key to the primary key of table Tab2. I...

SQL Server Import Error - Hi Experts, Need your help in fixing this. I am trying to import data from .XLS file to a table using...

SQL Server data file For Database Reporting Environment - Hi Fellas, my Name is Leo. I will create new database environment for my reporting task. Not use SSRS, but only...

question about checkpoint - a question about the checkpoint operation i heard some ppl say that sql writes down the dirty pages directly to disk,...

need help..cant understand how to approach - Customer CustomerID CustomerName Birthdate 1 John Doe 1/1/1970 08:31 AM 2 Jane Doe 1/1/1971 01:18 PM 3 Jon Public 1/1/1972 11:58 PM 4 Jane Public 1/1/1973 07:00 AM 5 John Smith 1/1/1974 08:31 AM Order OrderID PO Number OrderDate 1000 ABC123 1/1/2012 01:00...

Unable to connect SQL Server using AD group access - Experts i needhelp on this : I am using SQL Server 2008,we have Active Directory groups created and added to database.My...

Question on Lock wait types - Hello Everyone, Our production server experiences lot of blocking and locking from application. I have attached the top waits from...

Do not wait for Query to completed - Hello Experts, I have an application that does many things, at the end, it calls a stored procedure to perform some...

Hexadecimal to Binary Conversion in SQL Server 2008 R2 - Hello, I am using the following code to try to convert my hexadecimal string to binary format: [code="sql"]DECLARE @MYHEX AS VARCHAR(MAX) SET...

Automatic backup task - I recently completed setting up what I thought was a pretty good system for performing automated backups, but I can't...

Stored procedure execution from within a data retrieval select statements. - I have a stored procedure that calculates a quarter number. It accepts an input date and returns a quarter number...

Connot connect to server via name but IP works, not a SQL Browser service issue :) - Ok, here is the issue and my steps to troubleshoot: I can connect to my SQl server using SSMS by IP...

Conditional Formating - I am trying to use this code, keep getting error. Can somone please help. =SWITCH(IIF(Fields!summary_name.Value LIKE 'CH_*' AND Fields!MY.value < 0.9850),...

Lock Issue Advice - I am going to start this with an apology - I have a fairly good amount of understanding of SqlServer administration...

Removing constraints for performance. - First I consider constraints as safetybelts. I think constraints should be used as much as possible. For normal operations they...

Business Intelligence - Training Center? - I wanted to check if anyone has recommendations for training on Microsoft's BI tools. I can get the entry level...

Trigger not firirng in sql server - hi all I have ON INSERT and UPDATE trigger for my table,if i update any column or inserting any rows...

Why does TRUNCATE TABLE hang? - I'm trying to empty a staging table using TRUNCATE TABLE in a stored procedure, but the sp just hangs. The...

Parsing out email address in FROM field - Hello, Can someone please help me to trouble-shoot my query? Here is the scenario. I have FROM field in my table...

Create select statement for the query - Hi, I have a requirement to create a CASE statement for the following condition: SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is...

How do I join tables, but select top 1 from 1-many tbl? - Essentially I have a Contact table and a History table. I'd like to get the join the tables, but only show...

SSRS Save file to folder (Windows File Share) with date as prefix to filename - Hello - I am creating several reports. Each report runs daily and a team uses each of the reports on a...

multiple relationships - how we can solve this problem Cross Product T_VOL_USAGE VOL_name NODE-Name V1 B V12 F V3 F V2 C V2 D V3 E i want a query that...

hight wait type of PREEMPTIVE_OS_AUTHENTICATIONOPS & PREEMPTIVE_OS_LOOKUPACCOUNTSID - Can anyone explain what these wait types are? PREEMPTIVE_OS_AUTHENTICATIONOPS PREEMPTIVE_OS_LOOKUPACCOUNTSID

how to get date difference in years month and date - Hi all, how to get difference in year month and days from 2 date values? Ex:10/aug/2010,12/oct/2010 then result is Res:0...

Create FTP Connection through script task in SSIS 2008 - Hi All, Please help. I'm new to SSIS 2008. I'm using the code below to FTP File, Question :- 1) Seem...

SQL Server 2008 : T-SQL (SS2K8)

Suggestion to avoid Cursor - Hello comunity I need to replace a Cursor with an example using a WHILE and IF condition, for testing if the...

Is there a Coalesce LIke function that returns the lowest/highest value of a set of params passed to it - Coalesce() returns the first non-null value passed to it. Does T-SQL have anything like this but that does this for...

Need help for a Query - HI, We a have 2 tables.I need to insert values form DAT_STAGE table to DAT_DES with out duplicate records. I have used...

t-sql - How can track database server startups/shutdowns via t-sql and save this information in a table.

Best way to handle Null values - I need to write a script that I carry a variable for the order number. However, that field may be...

Alphanumeric number generation - Hi, I need to write a SQL query to print the following aphanumberic sequence in SQL 2008. 0001, 0002, ... , 0009, 000A, ... , 000Z,...

Is there an accurate script for datediff format in years:months:days? - I've tryed several tsql scripts and none of them have proven to be accurate. If you got a neat script up...

SQL Server 2008 : SQL Server Newbies

Return rows with youngest DateTime - I have a dataset for a ticketing system. The dataset contains the Ticket #, Assigned Technician, and the DateTime they were...

SQl Joins - Hi, I am new to SQL. I have question regarding joins. Let us say i have 3 different tables Table Workplace Workplacecode (PK)...

quotation and punctuation in sql server - Does anyone have some rules by which quotation in SQL Server works? An example: Select Year, Max(Case When Name='Tour de France'Then Coureur...

Stored Procedures and updating multiple tables - Was hoping someone could kick me in the right direction in regards stored procedures. My issue is if I have a...

sqlserver 2005 express installation - Hi, I have installed sql server 2005 express management studio. After the successful installation, I try to open it and the connect...

Multiple Rows in One Resultset - Hi All, Let's say I have a table like the following: row1 row2 row3 row4 0 6 0 0 0 0 30 0 4 0 0 0 0 0 0 18 but I want to return a resultset like: row1 row2 row3 row4 4 6 30 18 Does anyone have any...

connecting sql server over the internet - Hi all, i'm new to sql server 2008 and i would like help with connecting two pc's over the internet i've...

SQL Server 2008 R2 Express Sync - Hello I have been tasked with finding a method of synchronizing offsite branches to the HQ. It will be one-way...

Just taken new Job as a SQL Server DBA, can anyone recommend some learning material? - I come from a SQL developer background but have now taken a position as a DBA. Recovery plans , security policies...

SQL Server Database Mail extra profile - I just installed database mail on SQL Server 2008 Enterprise Edition. While doing the install I couldn't help but notice...

SQL Server 2008 : SQL Server 2008 High Availability

Database Level Clustering in SQL 2008 - Hi Techs, is it possible to have DB level clustering in sql 2008? if so how can we achieve this? I...

SQL Server 2008 : SQL Server 2008 Administration

SQL SERVER 2008R2 - hey, i have installed the sql server 2008 enterprise edition in my system.i am planing to install the sql server 2008...

Disable job in maintenance plan fails - Hi there, I have a problem with a maintenance plan task which executes a query to disable and later on enables...

Quesion On Space used by a specific Datafile - Hi I use the below script to find out the space consumed by a particular DB. It works fine for...

transaction log file full - How do I solve this error please? Thanks

Log space full 100% - Two of my databases are showing 100 % log space. I have no space left on the disk to increase the...

lightspeed - can someone pls provide silent lightspeed install command.

XML Query help .... - Folks: I need help with XML query below. It is working fine but runs very slow if the #output table has...

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

CDC and sys.dm_db_task_space_usage.internal_object_alloc_page_count - Gurus - I have CDC setup (2008 R2) and the job is set to run continuous. The job calls the cdc...

SQL Server Consolidation (25 servers) - Our company plans to consolidate SQL Servers. Currently there are about 25 servers. They try to minimize this number to 5-6....

Career : Employers and Employees

Production DBA Vs Project/Development DBA - Hi, Does anybody have any reference points for how the role of a production DBA may differ from that of a...

Programming : Service Broker

Service broker filling up Tempdb - I’m new to SB. Never had issues with it previously. I have 2 service broker tasks/queues one works fine and I...

Programming : Powershell

Scrip to connect to remote windows box to get SQL version - Dear All, I am very much new to power shell scripting where i need to find out what version of SQL...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

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

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Parameters Users Utilization - Hi geniuses! I have a report with 3 parameters. PARAM1 PARAM2 (multivalue and depending on PARAM1 selection) PARAM3 (multivalue and depending on PARAM2...

Consecutive execution of n parameters in a report - Hi all! I have one dataset called DatabasesOnServer: SELECT name FROM sys.databases Report Parameter is string, from query, and from Dataset above - DatabasesOnServer. The...

Sum with condition not working - Hopefully someone here can help me solve this problem. What I have so far is a list with two tables in...

'Negative sizes are not allowed' !? - Hi, While clicking in a textbox, wich is suppose to redirect me to another report, I get this error message: 'The value...

Data Warehousing : Integration Services

(Rebuild index) or (drop and recreate index)after ETL Fact Load - Hi, (Rebuild index after the fact load completed) or (drop index before load starts and recreate index after fact Load ends)...

Upgraded Package from 2k5 to 2k8 with configuration having problems with driver swap - Alright, let me setup the scenario. I have a series of packages that run in a job that we're upgrading...

Data Warehousing : Analysis Services

"Avoid defining measure groups with zero dimensional overlap" how to add overalp? - I am newbie to SSAS I am trying to understand the following sentence from SSAS cube i just built "Avoid defining...

nable to build SQL Server OLAP Cube when there is no rows corresponding to foreign key in a table - I am new to SQL Server OLAP Cubes. I am having the following issue like ex I have purchase order and...

Microsoft Access : Microsoft Access

Ms access add record to table via unbound text box - Front end MM Access adp file. On SQL server 2005 Form bound but user must not change data directly Unbound text box...