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

Hidden Tricks To SQL Server Table Cleanup

Edward Polley from SQLServerCentral.com

How to delete millions of rows with virtually no contention. More »

SQL Saturday #194 - Exeter

Press Release from SQLServerCentral.com

A day of SQL Server training in the UK on Mar 9, 2013. Sign up if you can come. More »

Using Temp Tables in SSIS

Additional Articles from SQL Server Performance

emporary tables are created in the Temporary tables are created in the TempDB database, which persists for a particular session. The objective is to maintain that session, until the temp table information is used and dump the data into a physical table.  More »

Help Guide the Future of SQL Compare

Press Release from Red-Gate

How changes are applied to databases differs from person to person and from organization to organization. Here at Red Gate we’re writing the next chapter for SQL Compare and we need your feedback to validate some of our ideas. To help us in this endeavour, and to enter a prize draw for one of five $50 Amazon vouchers, please complete the following survey. More »

From the SQLServerCentral Blogs - Benchmark SQL Server Disk Latency

jsterrett from SQLServerCentral.com

Typically, I am a big advocate of performance monitor but one place I commonly see performance monitor being misused is with benchmarking... More »

I've always enjoyed smaller environments, where people are more free to work in the way they want to, when they want to, as long as they are productive. I applaud the efforts of small companies to design offices or spaces where employees feel comfortable working and enjoy spending time. Some large companies do this as well and maybe this is necessary to offset the grind of long hours, but I believe the owners and founders of smaller companies often cultivate a friendly, close atmosphere and they hope their people to want to come to work. After all there are plenty of places one can work that will exact and demand long hours, often without any recognition of the hardships or without any additional benefits for your efforts..

I ran across this piece on the culture and hiring at Valve, a gaming company that makes Steam and Half Life. They have a very free flowing culture, depending on the individuals to make good decisions for the company and their own teams. I'm not sure how scalable this is, or even how easily this can be replicated to other companies. As I've watched Red Gate grow over the years, it's been an amazing place to work, but it's not without some pain points. We've done well, though I don't know that the ideas from Valve would work there.

At the bottom of the article, however, I was struck buy this quote:  Everyone's desk is on wheels. "There are only two plugs that need to be unplugged in order to shift from one team to another." That's an interesting way to design an office setup. Your desk is your desk, and you can customize it for you, but since it's on wheels, you can move it if the need to change arises. That doesn't happen too often in larger companies, but I do think the idea of virtual teams that come together for projects and then disband would work well in this environment.

Even in the operational world, the option of periodically moving my desk and changing neighbors without the hassles of packing up lots of stuff, would be interesting. It would also make weekend practical jokes much more interesting. Imagine coming in Monday morning and having no idea where you desk, with all your stuff, might have moved.

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 - MB WMV
• Watch the iPod Video Podcast - 21.5MB MP4
• Listen to the MP3 Audio Podcast - 4.2MB 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, rant or editorial, log in to the Contribution Center

Today's Question:

What does this script return?

DECLARE @vchrIsNumeric VARCHAR

SET @vchrIsNumeric='3NAME'

IF ISNUMERIC(@vchrIsNumeric)=1
 SELECT 'Is Numeric'
ELSE
 SELECT 'Is not Numeric'

By Ramana Reddy

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.

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

--QUERY # 1 
CREATE TABLE CLUSTERED_TABLE(a int , b int , c  varchar(50)) 
CREATE CLUSTERED INDEX CK_CLUSTERED_TABLE ON CLUSTERED_TABLE (a,b)

--QUERY # 2 
CREATE TABLE CLUSTERED_TABLE_PRIMARY(a int , b int , c  varchar(50),CONSTRAINT CLUSTERED_TABLE_PRIMARY_PK PRIMARY KEY(a,b))

--QUERY # 3 
ALTER INDEX CK_CLUSTERED_TABLE ON CLUSTERED_TABLE DISABLE;

--QUERY # 4 
ALTER INDEX CK_CLUSTERED_TABLE  ON CLUSTERED_TABLE REBUILD;

--QUERY # 5 
INSERT INTO CLUSTERED_TABLE VALUES (1,2,'ABC') SELECT a,b,c FROM CLUSTERED_TABLE

Mark the correct set of resultsets based on the numbering below after executing all the queries sequentially:

(i) Query 1 creates a table with structure (a int null, b int null, c varchar(50) null)

(ii) Query 2 creates a table with structure (a int not null, b int not null, c varchar(50) null)

(iii) Query 3 disables the index and data access to the table

(iv) Query 4 rebuilds the index, and removes the fragmentation

(v)Query 5 Inserts one record in the table ; but select fails , as data access is blocked.

(vi) Query 5 , both select and insert fails as data access is blocked

Note : All queries execute without error, please ignore syntax error, if any; I am sure there is none.

By demonfox

Answer: i,ii,iii,iv

Explanation: Query 1/ Query 2: Default is Null, when not specified while creating a table ;for primary key it's not null.

Query 3 : When a clustered index is disabled, the data access is disabled ;so, Insert or select doesn't happen, including other DML operations . As per msdn(in references) it says "Disabling a clustered index prevents user access to the underlying table data."

And the error it throws on DML operations is like : "The query processor is unable to produce a plan because the index 'CK_CLUSTERED_TABLE' on table or view 'CLUSTERED_TABLE' is disabled."

As I tested furthur on , It doesn't allow to create an Index, Add a column or Modify a column; But, Truncate happen smoothly- couldn't find a reference for truncate operation.

Query 4 : It rebuilds,drop and recreate , the index and remove fragmentation; also enable the disabled index.

Query 5/Query 6: After Query 3 the clustered index is disabled , but , after executing query 4 it's enabled again ; so the process follows as usual.

Refrences :

• Ref #1: http://msdn.microsoft.com/en-us/library/ms188388.aspx
• Ref #2: http://msdn.microsoft.com/en-in/library/ms174979.aspx

» Discuss this question and answer on the forums

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.

Replace Special Character and Keep What You Want

Vingo from SQLServerCentral.com

Replace special char and keep what you want More »

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

SQL Server 2005 : Administering

SQL Agent troubles - The system cannot find the file specified. - Hi All I have a SQL 2005 system. The SQL Service is started with no issues - However, I can't seem to start...

how to execute timestamp querry in sql server 2008 ? - I am T.E. I.T. student & these sem we have dbms subject but i am so confussed how to write date...

Error:-Could not load the DLL xpDataComm.dll, or one of the DLLs it references.Reason: 1114(A dynamic link library (DLL) initialization routine failed.). - I have created "xp_TestDataComm" extended stored procedure but i am getting below mentioned error when i execute "exec xp_TestDataComm" extended...

SQL Server 2005 : Backups

Backup Failed but backup file is created - Hey guys, I have an issue with backing up a 80GB database over the network, Here is the error:A nonrecoverable I/O...

Transferring Backups - we need to copy a backup file between two very slow network Data centers (transfer rate: 350 kbps), Our full...

SQL Server 2005 : Working with Oracle

Remote Query to Oracle very slow - Hi friends, We are troubleshooting performance on a process that is taking too long. I ran a trace using sql...

SQL Server 2005 : SQL Server 2005 General Discussion

Issue with OSQL - Hi Friends, Requirement : i have a table which contains around 40 columns of which name is a column which i use...

SQL Server 2005 : SS2K5 Replication

Is it supposed to cleanup old snapshot folders for trans replication? - I have a server that I'm finding out today that was setup a long time ago with transactional replication and...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Resolving a deadlock - I have a deadlock between a trigger and a stored procedure stored procedure is always the victim of the deadlock....

SQL Server 2005 : SQL Server 2005 Integration Services

Tip needed - Change Connection on several containers at the same time - I have a DTSX package that I need to change the connections on for about 30 containers. Is there a...

SQL Server 2005 : T-SQL (SS2K5)

More help wit Sum, Pivot and so on... - Hi! First thanks for the help I got a couple of weeks ago! :-) Now to my new proble that is to...

SQL Server 7,2000 : Replication

Pull to Push subscription - OK I know I am dreaming but does anyone know of a way to switch a transactional subscription from Pull to...

SQL Server 7,2000 : T-SQL

DTS - result to excel - Hi Guys I have created several DTS packages, and the data gets stored in csv flat file and get mailed automatically. Now...

SQL Server 2008 : SQL Server 2008 - General

Update and Insert Performance - In Insert and Update, Multiple Index and Inculude columns is effect performance.

transactions - how to update 2 tables in 2 different Db within same transaction ? what if 2 Db are in different server...

SSIS OLEDB source executing but not returning results - Hi There, I have a query in a SSIS package. it has been running fine and returning required results to the...

Copying from one column to another with truncation. - Hi Guys I have a table called Mytable. In it are 2 columns, Description(50 Char) and Longdescription(200 char) I need to copy the...

SQL Server 2008 R2 not starting after moving"model" database - SQL Server 2008 R2 not starting after moving model database. Any ideas? Error: 945, Severity: 14, State: 2. Any ideas...

query performance - I have a table with 6 million rows. The table has around 200 columns. The data can be sliced and...

sp_updatestats and the default sampling rate - Using SQL Server 2008R2 As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue...

SQL SERVER database encryption. - Hello All, I am in a bind here and hoping someone will be able to help me out in this regard. There...

Querying the ringbuffer target - only returning the first half of the data - I am running the query below and seeing deadlocks from 2013-03-01 17:27:44.213 to 2013-03-03 06:51:22.317. It is currently 2013-03-04 15:20....

CLR Stored Procedures - Hi All, I am trying to create a CLR stored procedure. What I was hoping for was a stored procedure that...

Copy a big table (250G) form one server to another - Hi, I need to copy a big table (about 250G, basically used to store images) that I have on a MSSQL...

Remove "SQL Server 2005 (64-bit)" after in-place upgrade to 2008 R2? - Hi all, When upgrading SQL, we normally either detach/reattach or backup/restore. However, we have just been handed a server that had...

date format 7/31/2013 - Hi friends, Im new to TSql - I have requirement to display date as 7/31/2013 but currently I'm getting it from DB...

SQL 2008 enterprise to standard degradation - Hi Team, I have request from client to degrade few servers due to cost of enterprise edition. Can you suggest me the...

How to uncommit the transaction and execute the next statement in the cursor? - Hi Friends, [code="sql"] DECLARE @JurisID int, @CodedID int, @SrcCodeValueRef varchar(100), @SrcCodeDescr varchar(100), @SrcCodeValue varchar(100), @DecNumber bigint SET @DecNumber = @Number declare @result varchar(50), @chars...

SELECT list block with CASE? - is something like this possible? SELECT CASE WHEN (@X = 1) THEN COLUMN1, COLUMN2, COLUMN3 ELSE COLUMN4, COLUMN5, COLUMN6, END FROM TABLE1

Just accepted a Sr. DBA job. Time for a gut check? - Hi SQLSC! Buckle up, I apologize in advance for the long post. In many ways, typing it out is as much...

SQL Agent skipping job execution - I am experiencing an issue with a couple of SQL Agent jobs on SQL Server 2008 R2. The job will...

Publish SSRS 2008 in LAN - How to publish SSRS for internal alone. I have a project which needs to be circulated only to my team members...

fetch tables most often queried - Is there a way to determine which tables of a database are being queried most often, sorted in descending order?

How to maintain two digit values in sp? - Hi friends, IF OBJECT_ID('Sp_Coded') IS NOT NULL BEGIN Drop procedure Sp_Coded End go CREATE PROCEDURE [dbo].[Sp_Coded] (@ParentTableName varchar(50), @ColumnName varchar(40), @CodeID int, @Number...

Cannot open database requested by the login. The login failed. Login failed for user - hi! i have create a vizual c# program linq to sql ..when i whont to open the databse from vizual i...

What is the purpose of "Register as Data-tier Application" ? - As I can use "Extract Data-tier Application" to generate a dacpac file, what is the purpose of "Register as Data-tier...

SQL Server 2008 : T-SQL (SS2K8)

Help with execution Plan - Hi all, I am trying to learn Execution Plan reading. I executed the below query [code="sql"] USE [AdventureWork2008] GO SELECT * FROM Sales.SalesOrderHeader H INNER...

TabLock and TablLockX Confusion - I am trying to figure out how this works. Locking difference between TabLock and TablLockX. They seem to do the...

how to write a column expression in a query based on other table value exist - Hello Please help me here., i have below tables called student (sno, sname, address) StudentDrillEnrolled (sno, DrillClass) StudentDrillNotEnrolled (Sno, Reason, Viever) how to write a...

6 places after decimal without rounding - I have a sample table CREATE TABLE dbo.Analysis ( ID INT IDENTITY, Margin [numeric](21, 6) NULL, Gallons INT, Freight [numeric](21, 6) NULL, AccMargin [numeric](21, 6) NULL) INSERT INTO...

date problem - Hi not sure if this is the correct forum but I need some help with dates in SQL. I have been...

DB Normalisation Help required - I have the data as below. I need to normalise the table with this data.. can someone help me how...

Select full month number (with 0) - Hi, I use [code="sql"]DATEPART(month, myDate)[/code] or [code="sql"]MONTH(myDate)[/code] the resut is: 2 (if myDate is 2013.02.03). I would like return: 02 Do you have any solution for...

Grouping records by time interval - Hi everyone, I have been searching for a few days now to try to solve a problem, but without success. What...

Hierarchy example - Ken Henderson's book - not working - I’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL. He wants to show two things....

UDF Help - Hi Guys, I want to create UDF. Below is my logic. Please guide me where i am wrong. Alter Function [dbo].[udf_GPList](@EID int,@PID...

Time - Adding minutes and seconds - I have the 2 columns with time datatypes. select top 5 [Connect Time2],[totalTime2] from dbo.verizonDetails Connect Time2 totalTime2 08:05:44.0000000 00:13:00.0000000 08:05:57.0000000 00:01:00.0000000 09:07:42.0000000 00:03:00.0000000 09:07:46.0000000 00:09:00.0000000 09:08:08.0000000 00:01:00.0000000 I want to add the...

SQL Server 2008 : Working with Oracle

Using OPENQUERY to joing to an Oracle table in an SSIS view fails - I'm having a strange SSIS (SQL Server 208 R2) issue using OPENQUERY. I have created a Linked Server to our...

please help me convert this part into SQL Server. - IF OUT_PAT_VISIT_ID_V = 'Collected' and NO_OUT_PAT_VISIT_ID_V = 'Collected' THEN :NEW.PAT_VISIT_ID := 'Collected'; ELSIF OUT_PAT_VISIT_ID_V = 'Collected' THEN :NEW.PAT_VISIT_ID := NO_OUT_PAT_VISIT_ID_V; ELSIF NO_OUT_PAT_VISIT_ID_V = 'Collected' THEN :NEW.PAT_VISIT_ID...

SQL Server 2008 : SQL Server Newbies

Create Insert data script for views - Dear All For one table I need to create insert statemetn with data. For this I used SSMS-Task -Generate script - Data...

Trying to make my lookup table unique - I am running the following query, what I am trying to do is only have in my reference table the...

Problem with a query - wrong update results - Hello experts, I have 2 tables. One of the tables has the data I need to know which user input it. On...

How to split query result into seperate columns - I have one table that contains all my data. I can group into a result as below (the weight would...

SQL Server 2008 : SQL Server 2008 High Availability

Help moving SQL instance folder structure from one SAN to another (cluster environment) - At the office, we have a 2-nodes cluster configuration running a SQL Server instance on each node (active/active). We need...

dbmirrroring - can two mirroring sessions on single production server between diferrent sql instances use same endpoint and same port numbers eg: DBprincipal1....DBMirroring1 endpoint...

SAN to SAN hardware level (Block Level) replication solution for MSSQL 2008 R2 databases synchronization between DR and Production - Hi; Appreciate your suggestions and solution for following scenario Scenario: ====== 1) My SAN team already implemented SAN to SAN replication between disaster recovery...

Installation Error: Volume does not belong to the cluster group - I am installing SQL 2008 R2 cluster in windows 2008, I got the following error: The volumne contains SQL server data...

how read from mirror server ? - Hi all, I have a production server with 3000 online users, I set up SQL server database mirroring, Now I want to...

Log Shipping Looking for Backups in wrong location - I have log shipping set up (initial set up) and the backup and copy jobs are working fine. The restores,...

Current SQL Server servicepack on passivenode - Hi All, Is there any way to find out sql server service pack level on passivenode, on active node sp2 hasbeen...

How to stop SQL services on both cluster node without failover - I need to clean stop SQL services and dependent services on Cluster both node without failover for adding new SAN...

Adding 2nd Node to SQL 2008 Enterprise Failover Clustering - Hello, I followed this link for installation: http://www.mssqltips.com/sqlservertip/1721/install-sql-server-2008-on-a-windows-server-2008-cluster-part-4/ Everything was alright for the 1st. Node. Then, I try to add 2nd node, in the...

SQL Server 2008 : SQL Server 2008 Administration

rename SQL server - After we changed computer name, and I renamed SQL server name too following ms articles. by using sp_dropserver, sp_addserver. But I...

resource governor with multiple cpu on server - is it possible to use resource governor when there more number of cpu or it is a multiprocessor environment and...

Problem with Database mail - We have SQL SERVER 2008 R2,which Database mail does not work properly.When I try to send test mail,it says it...

Does the adrenaline rush ever subside when rebooting a DB server. - I work in a small shop as the accidental DBA and for the first time rebooted our production DB server...

System-health extended-event session does not capture latest deadlocks - While running the following query to capture the latest deadlocks recorded in the default system-health extended-event session, I noticed that...

Database Backup Issue - Hello All, I'm running SQL 2008 R2. I have an intermediate issue with my backup for this one database. Some days...

Career : Certification

Should I go ahead with 70-432 - I have been preparing for MCTS 70-432 for the past 1 year. I was planning to take the certification exam...

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

Programming : Connecting

Execute a sql job from AS400 command line - I am trying to use this command to execute a sql job from an AS400 command line. I am having...

SQLServerCentral.com : Anything that is NOT about SQL!

The Beer Cooler Thread - On popular request - at least one! - a dedicated topic about beer. I shall start with my favorite beer: Duvel. It is...

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

Reporting Services : Reporting Services

Installing ReportServer on separate server. - Hello All, We currently have report server and database engine installed on same machine. The problem we are facing with is...

MERGE problem -> when I need to reenter an entry which is already in the history - Hi, I am having a BIG problem with the merge statement in SQL 2008: When I have an entry (for example...

MultiValue Parameter not working - Hello all, I have a table that displays different statistics about "Properties". The table only has 1 row, this single row...

Reporting Services : Reporting Services 2005 Administration

SSRS Report on Local Server works fine but not through Remote server - Hi, I deployed a SSRS (2005) report on my Report Server and is working fine, but when tried through Remote Server...

Report Server not recognized email aliases - I have setup the report services and most of it seems to be working fine. When I create a subscription,...

Reporting Services : Reporting Services 2005 Development

Help with FilterExpression in BIDS 2005 - I'm going crazy - Hi all, I hope someone can help. I'm about to pull my hair out. Something as simple as a filter expression...

Colour co-ordinating increasing/decreasing values on a matrix report - Hi, I just created a matrix report with consecutive dates running along the y-axis and sales people running down the x-axis. Is...

Combining two data sets into the same table? - Hi all, Got the following situation: Server A contains information about a particular field of a table that I want to populate...

display dataset query in report - Hi, 1.How to display report dataset query in main report.pls any one give right information. Thanks, Vivek.

Data Warehousing : Analysis Services

Different aggreation results with and without a dummy WITH clause - I'm trying to diagnose a bug in a calculated measure in a SSAS cube, and while debugging I get this...

How to attach/install an analysis services database - Hi guys, I need some help with attaching an AdventureWorks analysis services database. I am trying to attach it but am...

Adding time dimension in the project - I have two dimensions Contact and Sales and fact table fact table contains following attributes OrderDate,TotalDue,TaxAmt,SubTotal,Freight My Source table does not contain any Time...