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

Creating a comma-separated list (SQL Spackle)

Wayne Sheffield from SQLServerCentral.com

Learn how to create a comma separated list of values in this short SQL Spackle article by Wayne Sheffield. More »

Understanding Column Properties for a SQL Server Table

Additional Articles from MSSQLTips.com

Designing a table can be a little complicated if you don’t have the correct knowledge of data types, relationships, and even column properties. In this tip, Brady Upton goes over the column properties and provides examples.  More »

From the SQLServerCentral Blogs - Decoding first, root, and FirstIAM in Sysindexes

StrateSQL from SQLServerCentral.com

Have you ever looked at the first, root, and FirstIAM columns in sysindexes and wondered… “What do these values mean?” Recently, I... More »

From the SQLServerCentral Blogs - Your online security is only as good as the weakest link

ArthurOlcot from SQLServerCentral.com

Over the weekend I received an email supposedly from Microsoft Live wanting me to verify my account and provided a... More »

There was a time when I managed two production databases on SQL Server. Two. I had a development version of one database where we paused development for testing, and only two production databases to manage. Since I had to also handle development, application support and hardware repair/replacement, that seemed like plenty to me. I was the accidental DBA, with database administration being the lowest priority of my day.

After that I moved on to administer databases in a number of jobs, sometimes as a priority, sometimes not, but in each case, I learned to work more efficiently and effectively. My goal was to automate as much as possible of the routine work so that I could spend my days adding value to the company. I learned to use scripts, alerts, jobs, and more to keep systems running while I was doing other work.

I'm sure many of you work in a similar manner, or at least I hope you do. This Friday I wanted to ask you at what scale do you need to become efficient, based on the size of your organization. The question this week is:

How many databases does each DBA in your organization manage?

I know some of you manage lots of databases in raw numbers, but also let us know if you need to do much with these databases. Is maintenance automated, or is there much active management you need to do in order to ensure these databases are running on a weekly basis. Let us know the size of your load as well, perhaps the amount of data is a better way of measuring the DBA load.

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 - 18.9MB WMV
• Watch the iPod Video Podcast - 14.8MB MP4
• Listen to the MP3 Audio Podcast - 3.1MB 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:

We have this script:

create table Person
( id int
, name varchar(80)
)
go
select Id as ColA
     , name as colB 
 from Person
select Id, name name
 from Person
select colA = Id
 from Person
select colA = Id
, name = colB 
 from Person

Which of those queries will be NOT executed successfully?  

By Jose Anchieta C. JR

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.

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!

-- USING SQL SERVER 2012
DECLARE @t TABLE (A varchar(25)) 

INSERT @t values ('77.88.99.100')  

SELECT 
  PARSENAME(A,1) AS 'First selected'
, PARSENAME(A,2) AS '2nd selected' 
, PARSENAME(A,3) AS '3rd selected'
, PARSENAME(A,4) AS '4th selected'
 from @t 

The question is: (select 1)

a. Are the values returned  First selected 77,  2nd selected 88,3rd selected 99,4th selected 100'

b. Are the values returned first selected 100,  2nd selected 99,3rd selected 88, 4th selected 77

c. No values are returned.

By bitbucket

Answer: b. 100,99,88,77

Explanation: According to http://msdn.microsoft.com/en-us/library/ms188006.aspx object_piece Is the object part to return. object_piece is of type int, and can have these values:

• 1 = Object name
• 2 = Schema name
• 3 = Database name
• 4 = Server name

» Discuss this question and answer on the forums

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

Date Password Last Set in AD

Mitch Spruill from SQLServerCentral.com

C# CLR routine to convert the pwdLastSet attribute to DateTime More »

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

SQL Server 2005 : Administering

SQL Server crashes when data drive full? - Hi. Has anyone experienced this in SQL 2005; that SQL Server goes down when the data or log drive drives...

Total Server Memory Vs Target Server Memory - Hi, We have SQL Server 2005 EE x64 with SP3. RAM = 16 GB Max Server Memory = 12 GB Min Server Memory = 0 default...

SQL Server 2005 : Business Intelligence

5 Reasons, SMB’s Should Invest in Business Intelligence Solution - [size="3"][font="Tahoma"]BI turns data into actionable information which supports business in strategic decision making. A strategic deployment of a [url=http://www.sigmainfo.net/services/business-intelligence-dw]Business Intelligence[/url]...

SQL Server 2005 : Development

UDF AND Stored Procedures - Hi Guys, I know, the question is Funny, but i want to know the details of stored procedures and UDF I have...

Excel Connection Manager error in SSIS 2012 - Please assist with error below: Could not retrieve the table information for the connection manager 'Excel Connection Manager'. Failed to connect to...

SQL Server 2005 : Working with Oracle

Disappearing Oracle Provider - Hello all, I am experiencing an odd problem with my systems and hope someone here can shed some light on my...

SQL Server 2005 : SQL Server 2005 General Discussion

several lines and kind of datas to one line and different columns - Hello, I need to affect datas from two differents line to one line. Let s imagine ***ORDERS TABLE ID Name adress -------------------- 01...

SQL Server 2005 : SS2K5 Replication

THE PROCESS COULD NOT CONNECT TO A SUBSCRIBER - I am receiving the following error when I try to replicate from SQL SERVER 2005 to ORACLE 9.2.0.8: THE PROCESS...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Performance Dashboard: Unique Compiles - In running the Missing Indexes report from the Performance Dashboard I get one which has a very high value in...

SQL Server 2005 : SQL Server 2005 Integration Services

insert data from file to sql table - hi i have flat file like this TH*4.2*876545*P~~IO*3456*color~ now i have two tables create table table1 (id nvarchar(10), t1 int, t2 int primary key, t3...

IS it possible to have dynamic sql in ole db src where the table is the part that is dynamic? - I am trying to create a for each loop and inside the loop add a dataflow where I pass in...

How to compare current row with previous row - I'm not sure how this can be done in SSIS, but I would like to compare the current row to...

SQL Server 2005 : T-SQL (SS2K5)

urgent@@Help in Modify the query - Can any one help with below query. i am getting error saying that Msg 8120, Level 16, State 1, Line...

Not allowed to use xp_cmdshell - We are not allowed to use xp_cmdshell for security reasons. I currently extract data (real-time) from Oracle tables in my sql...

SQL Server 7,2000 : General

Should I become a DBA?!? - Hi All, I've been offered a job as the sole MSQL DBA at an up and coming online retailer. I'm from...

SQL Server 7,2000 : T-SQL

Date Calculation Excluding Weekend & Holiday - I need to be able to find out the number of days between two date excluding weekend and holidays. For...

Creating an view with Option (Force order) - Hello Sir I have a query which has an option(FORCE ORDER) ie select * from tab1 t1 inner join tab2 t2 on t1.c1...

SQL Server 2008 : SQL Server 2008 - General

The client was unable to reuse a session with SPID 799, which had been reset for connection pooling. - Hi SQL server version : Microsoft SQL Server 2008 R2 (SP1) - 10.50.2772.0 (X64) Enterprise Edition Windows version : Windows NT 6.1 <X64>...

sp_send_dbmail problem - Hello, I've got a very strange problem, I can send mail with the following statement: declare @Error int EXEC @Error = msdb.dbo.sp_send_dbmail @profile_name = 'SQL...

Difference between Profiler and SSMS - Hello, I run a profiler trace to identify bootleneks. I found a stored procedure which have many reads, but when I...

Login restriction - Hello All, I have a scenario where i am looking to restrict a login only to read from the tables but...

Deadlock on the same resource with incompatible locks - Hi, I have a table named ClientContext that has only the clustered index. I have a procedure sp1 that call other...

Find all clustered indexes - Hi, I am looking for a script to find out all clustered indexes which are not in primary file group....

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

How to find relationship between tables - Hi, we are having more than 3000 tables in our DB, But none of the tables are designed with foreign key...

Where clause on indexed column using a variable - I have a SQL 2008 R2 table with a few composite indexes and a few single column indexes. I've narrowed...

Use SUM function inside Update Statement - How do we use SUM in side update statement. Please review the below query and help me select [September 2012...

Linked Server to SQL 2000 - Hello, sorry, newbie with question. I'm pulling my hair out and hoping someone here smarter than me can point me...

small lock appearing on MS SQL 2008 System database files - I want to migrate MS SQL R2 databases to a new storage on a Clustered environment. There are small locks...

Very Small Tables - I was recently asked for my opinion on adding an identity column and primary key to a very small table....

Removing only contiguous/adjacent duplicate records from a rowset - In SQL 2008, is there any way to remove only adjacent/contiguous duplicate records? Only if a record repeats immediately, then...

Retrieve data between 2 '.' - Hi geniuses! Hi have a field (VALUE), which contains data like: MNN.helloworld GFF.goodbyeworld SSW.seeyoulaterworld.oops And I want to retrieve data when there's only one '.' in...

xml as varchar datatype - Hi guys, I need some help (well a lot) with XML. I've never used it before so am basically green in...

Query performance change - The following query was executing within 1 minutes for the past 2 years without any issues. update dbo.INT_AS_VENTE_TITRE set P_STATUS...

Fetching data from two tables - Hi All, I'm having 2 tables named "Folders" and "Permissions" I want my result to be all the folders from "Folders" table...

Split without delimiter based on position - I am having a query i need to split the query based on the position i specify Say string is 12345678910111213141516........ i...

SQL Developer edition in test environment - Can I use in a test environment the SQL Developer edition? To the test development will have access me (the...

Outlook Data to SQL Table - Hi Everyone, I want to be able to automatically populate an SQL table with data from emails I receive in a...

HOW TO FIND A VALUE IN ALL COLUMN OF ALL TABLE IN A db. - HI ALL, As i Have a task to find 9 digit value in all column of all table in a DB...

How to create your own file of servers and groups to import into Registered Servers - we have all of our 1000 instances stored in a central DB. I can easily pull these out, and group...

Upgrade from SQL Server 2000 with Linked servers and Replication jobs - Hello All, I am in the process of Upgrading from SQL Server 2000 Enterprise Edition (x86) to SQL Server 2008 Enterprise...

How to link sql server to windev? - That's about it, been googling for 1 hour and I found nothing besides the native windev Hyperfile sql which allows...

SQL Server 2008 : T-SQL (SS2K8)

Need to filter large CTE in Join or where clause - So I have a fairly large CTE in a stored procedure. I want to filter my results based on an...

Why won't this query return data? - [quote] -- create the tally table SELECT TOP (30) IDENTITY(INT,0,1) AS N INTO #Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 -- Add an index ALTER...

Urgent Query Help - The below query has to give Y if it's equals to zero but it gives always zero. can you modify...

VARCHAR vs. CHAR - Hi, Is it best to use varchar or char for small text columns, max 5 to 10 chars? An example is...

Index with Include - Hi hope this is the correct forum to this question if not please redirect to me the correct forum recently my company...

Data dictionary procedure issue - Hi..I have lots of lots of (around 1000) tables for which I need to create a data dictionary..The create table...

Query Help - Hi guys, Need help in sql query, Let me let you what i need, during the S.P i want to...

Group or sum date column (ATTLOG) - FILE ATTLOG.txt TARJETA | HORA | FECHA | RELOJ 30 | 02/08/2010 08:14 | 02/08/2010 | 1 30 | 02/08/2010 12:57 | 02/08/2010 | 1 30 | 02/08/2010 14:01 | 02/08/2010 | 1...

SQL Server 2008 : Working with Oracle

OraOLEDB.Oracle vs. MSDAORA, SQL 32 bit, 64 bit - After upgrading to SQL 2008 R2 on a new 64 bit server, linked queries are running 3 to 4 times...

SQL Server 2008 : SQL Server Newbies

Convert DD-Mon-YYYY to DD/MM/YYYY - Hi experts, I have a table called temp in which i have date columns as dt_of_birth [varchar](15),dt_of_joining [varchar] (15).The dates are...

Loop through table to group associated records - Hi All, I have a table containing about 1mil records that i need to loop through, find associated records and insert...

Dyn SQL - Need results to show when count is 0 group by datetime - So the guys in charge of this place made some interesting requests and pegged me as the person to do...

Convert DD-Mon-YYYY to DD/MM/YYYY - I need to convert dt_of_birth [varchar] (15) which is in the format DD-Mon-YYYY to DD/MM/YYYY dt_of _birth is specified in...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Server 2012 "Always On" "Listener" not working with named instance - Hello, I have setup "Always On" on named SQL Instances. I have setup the listener as well. I can not...

Syncing Logins and Jobs - I am looking for a quick procedure where I can sync my primary and dr servers for logins and jobs...

Database Audit - Hi, I just sent up an audit on a database to collect all the select queries on a table. Everything is...

SQL Mirroring error did not auto failover - Hi all Last night our principle instance alerted us to the fact some logs has not been sent over to the...

SQL Server 2008 : SQL Server 2008 Administration

mirroing - Restoring - Hi, We have mirroing setup for DR purposes. i.e. server mdr is mirroing server m Question: 1- When I go to the mdr machine in sql...

SQL SERVER target memory vs total memory - Hi All, I ran this query SELECT object_name,counter_name,instance_name, (cntr_value/1024) as Memory_in_MB, cntr_type FROM sys.dm_os_performance_counters WHERE counter_name IN ('Target Server Memory (KB)','Total...

shrinking log transaction file (ldf) will break logs chain? - Hi!, I've read several post about logs & shrinking, but i don't know -i can't decide- where I must ask about...

Can i create an initial snapshot in transactional replication with Standard edition - I don't know much about transactional replication and we're going to implement this soon on a SQL Server 2008 Standard...

batch file script need - I need a batch file script to read the hostname, and replace the hostname dynamicaly where we see 'Hostname' in...

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

Plan Cache - Adhoc - Usecounts 1 - Hi All On my SQL Server instance, I have +- 26000 Adhoc plans and +-23000 of those plans have only been used...

Database Mail Error - This is a recent install of SQL Server 2008 R2 in a Windows Server 2008 R2 64 bit environment (which...

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

DB Backup Maintenance Plan Won't Execute - I've created a back up plan on one DB using the Maintenance Plans Wizard and the regular way (New Maintenance...

memory not releasing - HI I have a server with 8 Gb Ram I installed Sql server 2008 R2 on 64 bit platform Enterprise edition,in...

Career : Certification

Would using the MS Press books be sufficient? - After being an MS SQL DBA for over a decade I'm finally looking at getting some certifications. Unfortunately though it'll...

Preparing Guides for exams required for MCSE-BI Platform(SQL Server) 2012 Cetification - Hello, We are some BI Developers preparing for the exams required to get the MCSE BI Platform-2012 Certification. But we couldn't...

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

Non unique id xml node -flatten and import in to sql - Hi i need to import the xml in to sql. there is no item or value in invoice node its...

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

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

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

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

SSRS methods and tools for end users - Hi geniuses. What are the methods and tools available for end-users to modify or create new and standard reports, that you...

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

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

Database Design : Disaster Recovery

3rd party replication software - I'm in the process of putting together a DR solution and was wondering who has used 3rd party software for...

Database Design : Design Ideas and Questions

EAV's and NVP's are bad. So... What do YOU use as an alternative? - I've just read a dozen articles/posts where people absolutely condemn the use of EAV's (Entity, Attribute, Value) and NVP's (Name,...

Data Warehousing : Integration Services

ETL testing - Hi All, I want to know if there is any automated tool which can be used to for post migration ETL...

Visual Studio 2010 Shell and SSIS from SQl Server 2012 - Hello, i tried to migrate Packages from our stable 2008 R2 SQL Server with SSIS and Visual Studio 2008 to a...

WMI task and WQL query - Hi guys, I am using a WMI task to watch for files. We get about 20 txt files. when they...

OLEDB destination redirectin rows to error output for no apparent reason - Hi, This is in SSIS 2008. I have a package that inserts rows to an sql server table. The destination table has...

Data Warehousing : Analysis Services

SSAS partitions - Everything I read says you need Enterprise edition to partition a measure in a SSAS cube. I'm running 2008 R2...

Joining two queries in MDX - I have two queries that has been implemented in MDX for different dates: SELECT NON EMPTY { [Measures].[Quantity Received] } ON COLUMNS, non...

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