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

SQLServerCentral Runs sp_Blitz - Reliability

Steve Jones from SQLServerCentral.com

The second article that examines the output of the sp_Blitz script™ run against SQLServerCentral's database servers. More »

How to write a custom metric

Grant Fritchey from SQLServerCentral.com

You can extend SQL Monitor to track whatever you need to watch on your own system with custom metrics. Grant Fritchey shows us how. More »

Creating SSIS Package Templates for Reusability

Additional Articles from MSSQLTips.com

We often need to create similar SSIS packages which contain some common components such as connection managers, data flow components, log providers, event handlers, etc... So do we really need to create a package from scratch each time and add all these commonly used components in each package again and again. Is it possible to create a SSIS package with a basic structure/workflow and common components which can be used as template to create subsequent packages?  More »

From the SQLServerCentral Blogs - SQL Server Analysis Services KPIs

Jennifer Salvo from SQLServerCentral.com

Key Performance Indicators (KPIs) are measurements used to gauge business performance against quantifiable goals.  SQL Server Analysis Services provides functionality... More »

On SQLServerCentral, there are a lot of common questions that I see asked over and over. Perhaps the most common is the "why is my transaction log so large it filled my disk" question, which seems to occur constantly (C'mon, Microsoft, change the default recovery model).  However there is also no shortage of people asking for help with interview questions.

In general, I don't mind helping people learn more about their craft. I's how I learned, and I think honest effort should be rewarded. If you spend some time trying to learn something or understand a concept and have questions, I am more than happy to try and clarify things. However I don't think that anyone on a phone interview, completing some type of employment exam, or even just memorizing a number of new concepts is doing themselves, or the employer, any good.

Cramming for an evaluation of your skills makes sense. I did it for school exams, and I see plenty of people working in groups or through exhaustive simulations of features in SQL Server to prepare for the MCM exams. However in those cases, it's a refreshing of the concepts and knowledge that one will need to complete the exam quickly. This is knowledge the person already has learned in the past.

As long as you are trying to brush up your skills, and are not deceiving anyone about your talents in an area, I think cramming some facts and ideas into your brain is a good way to present yourself in an interview. Attempting to misrepresent your ability to produce professional work in some area to get a job isn't acceptable. I won't help you, and I would encourage others to avoid helping as well.

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

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 or editorial, log in to the Contribution Center

Today's Question:

You have the following table and data in it:

CREATE TABLE Test (
    RowID INT CONSTRAINT PK_Test PRIMARY KEY CLUSTERED,
    Col1 CHAR(750)
);
-- Insert 15 records into the table.
INSERT INTO Test (RowID, Col1)
VALUES (1, 'One'),
       (2, 'Two'),
       (3, 'Three'),
       (4, 'Four'),
       (5, 'Five'),
       (6, 'Six'),
       (7, 'Seven'),
       (8, 'Eight'),
       (9, 'Nine'),
       (10, 'Ten'),
       (11, 'Eleven'),
       (12, 'Twelve'),
       (13, 'Thirteen'),
       (14, 'Fourteen'),
       (15, 'Fifteen');

Currently, the data resides on two data pages (the first page has the first 10 records, the second page has the remaining five). If you delete the first 12 records, how many data pages will the table have after the Ghost Cleanup process has been run against this table?

By Wayne Sheffield

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

This question is worth 2 points in this category: ghost cleanup. 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.

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.

Using SQL SERVER 2012, I have created the following table

CREATE TABLE #Leaders(title INT,Name VARCHAR(50),Score INT) 

The #Leaders contains the following data

1,'GilaMonster',35452

1,'Jeff Moden',31783

1,'Steve Jones',30901

2,'RBarryYoung',9851

2,'Koen Verbeeck',8842

3,'Matt Miller', 6980

3,'WayneS',6284

4,'someone',3657

I then execute the following T-SQL

SELECT CHOOSE(Title,'SS Champion','SS Crazy Eights','SS Certifiable','Hall of Fame') AS Title 

,Name,Score 

FROM #Leaders

The question is: Is the value of Title returned as an integer or as characters ?

By bitbucket

Answer: Characters

Explanation: CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

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

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 Master Data Services

Harness your master data and grow revenue while reducing administrative costs. Thoroughly revised to cover the latest MDS features, Microsoft SQL Server 2012 Master Data Services, Second Edition shows how to implement and manage a centralized, customer-focused MDS framework. See how to accurately model business processes, load and cleanse data, enforce business rules, eliminate redundancies, and publish data to external systems. Security, SOA and Web services, and legacy data integration are also covered in this practical guide.

Get your copy from Amazon today.

Recreate a temporary table from TempDB

Tristan Cook from SQLServerCentral.com

This Script can be used to pick out a temporary table from tempDB you might want to Check and drop the table before you create it just in case , but I Haven't included it here More »

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

SQL Server 2005 : Administering

Naming new SQL Server instance - They say it's best practice to name a SQL instance even it's the only instance on the host server. My...

Maintenance plan to clean *.bak files older than 14 days - SQL 2005 - Hello, I'm new to DBAs and I have a fairly simple question for you experts. I have a SQL 2005 server...

missing space - one of the drives on our server has a total space of 80gb, out of which 70gb is occupied. But...

Full Backup - Restore point in time. - Hello All, Is it posible to restore to any time in point in restore. i am getting issues if i have...

db autogrowth problems - On two occasions we have had to change or autogrowth setting for our database files from 5 or 6 % to...

How to check whether hyper thread is disabled using SQL Server query - It is confusing from Windows side to check from BIOS whether hyper threading is disabled. is there any easy way...

Missing green arrow on the sql server icon in object explore - Hello everyone I have tried to Google this issue for quite awhile and can't find any definitive solution. Now in my...

SQL Server 2005 : Backups

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

SQL Server 2005 : Business Intelligence

70-467 BI exam - how do I prepare for this? - Hi Plan is to take the 70-467 bi solutions exam. But very disappointing to see that no official guides or kits for this...

KPIs and how to hide them - So I'm coming along developing my SSAS cube. And I have a small problem. I have a shared dimension that has...

SQL Server 2005 : CLR Integration and Programming.

Regular Expressions - I am looking into writing a regular expression for validating emails. I am an absolute novice at this, could some...

SQL Server 2005 : SQL Server 2005 General Discussion

Optimizing XML import in SQL Server - I have this code to import a XML with 50mb, that contains around 26.280 entities. But this is taking to long,...

SQL Server 2005 : SQL Server 2005 Security

SQL Server 2005 Certificates - Hello, I would like some advice on cell based encryption please. I have transferred a database which uses a certificate to encrypt...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Wait_type= Sleep_Task? - Hi, Job is taking much time to execute, when i check the Wait type it shows 'Sleep_Task' and there is no...

Plan Usage for stored procedures - Hi, I've been running the following script to capture plan usage stats. [code="sql"]SELECT DB_NAME(st.dbid) DBName ,OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName ,OBJECT_NAME(st.objectid, dbid) StoredProcedure ,MAX(cp.usecounts)...

Using a View in aa query is slower than using the table directly - Hi all, I have a query that basically links together three Tables and a View which has started running extremely slowly......

SQL Server 2005 : SQL Server 2005 Integration Services

Script task using batch file parameters - Hi all, I have created a batch file to run my SSIS package. I will be able to pass parameters in...

SSIS Script task to change table name in data flow task - Hello, We need to change the table name of 'OLE DB Destination' task. I am not able to use variable due to...

SSIS - to read a NULL values in excel source - Hi, How to read the null values from excel source while doing in the transformation or to stop the package when...

Issues With DB2 CHARACTER Field Source - SSIS 2008 - I don't see a forum for SSIS 2008, so I guess I will post here?? I have an OLE DB connection...

help needed - hello everyone, I am a DBA for last 12 yrs. But I am out of work since a yr because...

Issues while executing the xp_cmdshell through SSIS - Hi All - First let me tell you what my SSIS package does, [u][b]SSIS Package[/b][/u]: - As per the business requirement, i have...

Creating Header AND Footer to a flat file destination file - Hi Guys, I am working on a financial project; the requirement is interface flat file should not contain any duplicated arrangement....

SQL Server 2005 : T-SQL (SS2K5)

set row number to various actions - I'm trying to set row numbers to a table's output. Table looks like [code="other"] AccountNum DataAction ActionType ActionStartCounter 123 11/01/2013 HELLO 1 123 12/01/2013 NONO NULL 123...

Previous week date range - Right, this is prooving to be a right headache, as many date related things in SQL seem to be for...

SQL Server 7,2000 : SQL Server Agent

error agent job : error loading a task - Anyone have an idea why this maintenance backup failed? All rights reserved. Started: 3:00:00 AM Error: 2013-01-01 03:00:14.14 Code: 0xC0010018 Source:...

SQL Server 2008 : SQL Server 2008 - General

trigger problem - Anyone see a problem with this trigger?, rather complex, but the issue may be in the logic of: "WHERE INSERTED.Transaction_Type = 'Transfer'...

Computed Columns & Divide By Zero Errors - Hi, I have some computed columns in a table that under some circumstances fail due to a divide by zero error....

Export data using BCP command - Hi all, I have a request from my client that they need to export data to an excel using BCP command...

Trunacte MSDB tables. - Can these 2 tables in the msdb database, simply be truncated.They have grown quite large and simply need some sort...

Hard disk full - A customer has a large SQLServer 2008 database, it increases size every day because it is basically a statistics database...

Insert multiple lines in a single row - Dear, I want to insert multiple lines in a single row in SQL Server 2008 R2. I have tried. But it's...

Actual estimated execution plan recommends index to apply and increase perf by 25%, but doesn't work - All, When I run a actual estimated execution plan for a query it will recommend an non-clustered index to apply that...

Does Change Data Capture require a table have a primary key - or can it record before and after column changes based on the LSN only? An extract from a file based legacy...

Bulk Inserting Comma separated Quotation Delimited text file - I am receiving a text file with comma separated data with quotation marks around certain fields that I need to...

Problem with using SQL Server linked server to MySQL database - I have a MS SQL Server that does a lot of data manipulation and staging and then I need to...

I'm stumped by why a particular query is slow - I'm hoping someone can shed some light on this. (I'm currently running SQL Server 2008 R2 Enterprise Edition) The punchline, (before...

SQL Security Best Practices - Dealing With User Name Changes - Are there any Best Practices from Microsoft or anyone else on how best to deal with SQL Logins & DB Users...

Modify Job name - How to rename all the job in an sql server 2005? I need to add the servername to the jobnames?

SQL Query - Hi, I have an issue with the SQL query I have created. The result that this query should generate should be...

what is Scalability of Database - What is scalability of a database.I have googled it but I didn't get exact answere.

Local System Account=fast, domain acct=slow - I am an ERP software specialist. I have installed SQL Server 2008 R2 at several sites and generally have MSSQLSERVER service...

SQL Query causing CPU Spikes on SQL Server 2008 r2 - Hi, I have a curious case here: there is a SQL Query that is causing 90% of CPU on one of...

Partitioning and the performance on writes. - Hello All, I get some results I am not expecting. The situation is something like : Full saturation test. Start with clearing the cache. 11...

How to move table partitions to different file groups - Hi Experts, Can you please help me in movin my 4 table partitions of a table to 4 different file groups....

Question about Optional Parameters in Stored Procedures - Is this the standard way to deal with optional parameters in SP? [code="sql"] CREATE Procedure AddMonth ( @EnterMonth as int, @product int = null ) AS INSERT INTO X SELECT...

New more powerful servers are slower than old ones? - We have 2008R2 servers, We are testing performance on new hyper-v servers before we migrate from old 2005 phsyical servers...

Importing a csv file into a database - inserts quotes automatically into every field - We are using a SSIS package to insert a series of CSV files (saved in excel) into a database table. The...

Cluster design comment wanted. - Hello Forummembers, For all of you a Happy Newyear. I have lost a bit my nightrest over the following: First we do not...

Error - I am getting while using database. I was trying to take databse in OFFLine. Msg 952, Level 16, State 1, Line...

SQL Server 2008 : T-SQL (SS2K8)

CTE or while loop or an other? - Hello, I am new to this board so thanks in advance for any help that can be provided here...:-) I would like...

Pivot to produce a timetable - Hi everyone, I'm looking for a fix for an issue I'm working on. I've got a list of locations, start and...

query SSIS-Task-Properties using T-SQL - Hi there, we have several SSIS-packages on our SQL servers, usually stored in the msdb. In these packages you find "execute...

Pivot problem - Hi I have the following table [code="sql"] CREATE TABLE #Test ( ProductID int, MainMasterFeatureID int, --MasterFeatureValue Varchar(100), ChilFeatureName varchar(100), ParentFeatureName varchar(100) ) INSERT INTO #Test SELECT 40,1,,'Pack','Type' UNION ALL SELECT 40,0,'Laminate','Technology' UNION ALL SELECT...

SELECT vs INSERT INTO - I've been searching for pointers on SELECT..INTO vs INSERT..INTO around forums and the web and haven't been successful yet. Basically,...

Has to Be a Better Way - Good Morning Everyone I am performing a code review before sending any thing into QA and on up. I ran across...

Quertion regarding update when there relationship between two tables - Please find the table structures as below USE Ticket GO CREATE TABLE Contact12 ( ContactID INT PRIMARY KEY, FirstName NVARCHAR(255), MiddleName NVARCHAR(255), LastName NVARCHAR(255) ) GO CREATE TABLE Contact13 ( ContactID INT, SalesOrderID INT...

Better Way to Perform this Query - Hello Everyone I hope that everyone is having a very good day. I need to write a query to select row counts,...

Reg: Image Directly Insert into database binary Format - Dear All I am working in University Project.I have 10000 students physical photos in one directory in corresponding register number...

Flattening a Parent Child Hierarchy - Hi, We are starting work on a BI project that is given data in a traditional Parent-Child Hierarchy format. The Problem is...

SQL Server 2008 : SQL Server Newbies

sql procedure - I want to display a various statements according to the condition through stored procedure like, id_student=1 got below remark in subject like Id_student Id_subject grade 1 ...

Torn page issue - Hello Masters, Can torn page issue occurs due to lack of disk space ? I know torn page is the page that...

ORDER BY non-sequential number sequence - I have a table where a certain column contains a status id as an integer. I want to sort on...

Structure of WHERE clause using "and" / "or" in a multiple criteria query - Hi I have a set of data (example script below) and i have to exclude certain rows based on the rules...

View not working since moving database from SQL 2000 to SQL 2008 - Hi All I moved a simple database from SQL Server 2000 to SQL server 2008. It has a couple of Tables...

SQL Server consuming 90% Memory - Hello Masters, My sql server has 12GB of RAM, I got alerts from windows team that SQL Server consuming about 90%...

SQL Server 2008 : SQL Server 2008 High Availability

replication issue - ''replication cannot do anything'' i have Transaction replication with pull subscription. There is no error in replication showing all success. below message...

Question about database mirroring with "high-safety mode without automatic failover" - Hi, In database mirroring, for "high-safety mode without automatic failover" it is mention that : "Every transaction committed on the principal database...

Replace principal server in sql server 2005 database mirroring setup - Hi folks, I am a regular SSC reader but haven't yet made any contributions due to time constraints. I have a question...

SQL Server 2008 : SQL Server 2008 Administration

Default Port - Hey all, A quick question. I recently joined an organization and I see here all the production environments are on...

Large system coming, looking for advice of how to maintain, over 2TB. - We have a new system coming that will hold our restaurant polling detail and we will hold 18 months in...

SQL job error - Anyone have any idea why I receive this error on SQL job? Step ID 1 Server SRIN_SVRA Job Name 1 Year Backup Maintain BDSBPM.Subplan_1 Step Name Subplan_1 Duration 00:00:34 Sql Severity 0 Sql...

how do I know files are backed up in tapes - We have maintenance plan setup for back ups. But we also have tape backup for the backup files. My question is from...

Several questions about SSIS - Hello -- I am hoping to set up a maintenance plan that will check the integrity of one of our databases....

Programming : General

importing data remotly executing. - This must be possible but never looked into. At the Moment I have some FTP software that every hour checks a...

SQLServerCentral.com : Anything that is NOT about SQL!

A reason to get a better-paying job... - So I can put one of these: [url=http://www.dailytech.com/article.aspx?newsid=29641]2014 C7 Corvette Stingray[/url] in my garage... :-) Know anyone who's hiring? ;-)

Employer asking for Social Media login? - I've never experienced this but found it bizarre. If I was ever asked to provide access to my social media...

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

Report Parameters Selected Shown in Report and/or Footer - Hi, I'm reasonably new to Reporting Services and still learning so A) bear with me and B) please excuse any...

Report manager Not able to run for higher values - I have Matrix report which runs in good amount of time in visual studio... but when deployed to the report...

Issue with Small matrix spilling over 2 pages - I have a small matrix that measures 3.5 on the ruler. my report is set as landscape. but it seems...

barcode generation problem in RDLC local report - I am trying to encode [url=http://www.aspper.com/barcode-rdlc/tutorial.html]barcodes in RDLC reports[/url], I am using vs 2008 and C# class library. It works well...

Reporting Services : Reporting Services 2005 Development

Report title with the use of report parameters??? - Hi all, I have a report with its title saying "Report with X - no of persons" I have a filters whereby allow...

Database Design : Design Ideas and Questions

Building my first data warehouse. Advice welcomed :) - Hi all, I've been brought on board to help a company start to build their first business intelligence setup. I'm a...

Conditional joining of Table A nested ID/heirarchyid to table A or B? - It's been a long time since I've dealt with complex database design, and in this case, I'd like to ask...

Data Warehousing : Integration Services

Extract only numbers from string which contains special characters too - Hi, i have a column called phone numbers whose datatype is varchar(50) that means it contains integer values, string values, special...

logging output from Execute T-SQL task - I have a Execute T-SQL task which purged records from table. I would like to record in a log file...

Data Warehousing : Strategies and Ideas

Fact/Dimension design suggestions - I'm from Higher Education and I'm working on a warehouse so our recruitment folks can compare application counts of certain...

Data Warehousing : Analysis Services

multi lingual capacity enterprise vs standard edition - Our SSAS pilot was started using the fully-fledged developer's edition where we added 2 additional languages. We tested the testcube...