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

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Jeff Moden from SQLServerCentral.com

An "old" subject is revisted where "newbies" can learn the methods and veteran users can get more performance out of the code. More »

SQL Server 2008 Failover Clustering

Additional Articles from SQLCAT

This white paper complements the existing documentation on planning, implementation, and administration of a SQL Server 2008 failover cluster, which can be found in Microsoft SQL Server 2008 Books Online. There are links to relevant existing content throughout the paper, which is intended primarily for a technical audience. This white paper covers failover cluster architecture and concepts for Windows Server (2003 and 2008) and SQL Server 2008; installation of a SQL Server 2008 failover cluster; upgrades and updates to SQL Server 2008 failover clustering; and maintenance and administration of SQL Server 2008. More »

Migrating SQL Server Databases - The DBA's Checklist (Part 3)

Additional Articles from SQL Server Performance

The final part of the series will examine full text catalogs and indexing, database collation, service broker considerations, encryption and high availability. More »

From the SQLServerCentral Blogs - Off-Hours Work: A Guide For Managers

Kendal Van Dyke from SQLServerCentral.com

If you work in software development or IT long enough eventually you're going to find yourself in a situation where... More »

Today we have a guest editorial from Andy Warren, founder of SQLServerCentral and member of the PASS Board of Directors.

You may have heard the phrase “accidental DBA” to describe how many in our profession come to be DBA’s, either full or part time. It’s a challenge that’s common to many businesses as they grow to the point of needing someone to take care of database related work. Did you know that there are also many “Incidental DBA’s”? That’s why I call those managers, developers, and business owners that really have no interest (or time) to do anything with SQL Server that doesn’t have to be done. They own SQL Server because their third party software requires it, or they needed something a little more robust than Access to power their web site, but they really aren’t do anything that requires much time or attention.

It’s easy to forget that not everyone has lots of change, lots of data, lots of ‘stuff’ going on. I was doing a presentation at a user group earlier this year and during the follow up discussion someone had a question about maintenance plans and indexing. I answered with my thoughts, and then someone else spoke up and said that they had been in business five years and as far as he knew they had no maintenance plan and had never heard about index defragmentation. His follow up comment was that since performance had been fine, what value would there be in rebuilding indexes? Now that might horrify you as a DBA, but to me it was a great view into a world different than my own. As much as I believe in index rebuilds as part of ongoing maintenance, here was a case where it didn’t matter, and probably won’t matter in another five years either. Even if it does end up causing a problem, it’s an hour fix once someone with some knowledge gets involved. My best answer – a little lacking I thought – was to think of it like routine maintenance on a car, you do it it to head off problems that you otherwise won’t know about until too late.

The tendency is for us power users is to say that users shouldn’t use a product they don’t understand. But in the real world, I drive a car with a automatic transmission. I get the idea, no clue on the details, and if breaks, I’ll take it someone that understands how to fix it. Sometimes we’re going to get those calls and when we fix something, we can share a few ideas about how to prevent it happening again. Incidental DBA’s may or may not listen because SQL Server is something they touch because they have to, and they would much prefer that they didn’t have to touch it at all.

Andy Warren from SQLServerCentral.com

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

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

create table test2(id int,entrycode varchar(10),entryname varchar(30))
insert into test2 values (1,'BE','BENGALOORU')
insert into test2 values (2,' CH','CHENNAI')
insert into test2 values (3,' DE','DELHI')
insert into test2 values (4,'MU ','MUMBAI')
select entryname from test2 where entrycode in ('BE','CH','DE','MU')
drop table test2

The output of the above is:

By VM-723206

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.

Microsoft® SQL Server(TM) 2005 Analysis Services Step by Step - The popular STEP BY STEP approach provides hands-on guidance to beginning-level programmers and those new to SQL Server 2005. A sequential, learn-by-doing tutorial, this book covers key features and techniques of Analysis Services to help programmers harness the full power of SQL Server 2005. The companion CD includes data sets and sample code. Grab your copy today from Amazon!

SQL Server 2008 introduces several extensions (subclauses), to the GROUP BY clause. Select all those that apply.

By bitbucket

Answer:

• GROUPING SETS
• CUBE
• ROLLUP
• GROUPING_ID

Explanation: http://msdn.microsoft.com/en-us/library/cc721270.aspx Introduction to New T-SQL Programmability Features in SQL Server 2008 SQL Server Technical Article Writer: Itzik Ben-Gan Specifically this paragraph "SQL Server 2008 introduces several extensions to the GROUP BY clause that enable you to define multiple groupings in the same query. These extensions are: the GROUPING SETS, CUBE, and ROLLUP subclauses of the GROUP BY clause and the GROUPING_ID function. The new extensions are standard and should not be confused with the older, nonstandard CUBE and ROLLUP options."

» Discuss this question and answer on the forums

Professional Microsoft SQL Server Analysis Services 2008 with MDX - The new features of Analysis Services 2008 make it even easier to use andbuild your databases for efficient and improved performance. This authoritative book, written by key members of the Analysis Services product team at Microsoft, explains how to best use these enhancements for your business needs. The authors provides you with valuable insight on how to use Analysis Services 2008 effectively to build, process, and deploy top-of-the-line business intelligence applications Grab your copy today from Amazon!

The Ultimate PK-Less / CLIDX-Less Table Finder

Jesse Roberge from SQLServerCentral.com

Lists Schema/Object of tables that are missing a Primary Key or Clustered Index, along with aggregate data for size, rows, indexes, and columns. More »

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

SQL Server 2005 : Administering

Shrinking the data file/log file affect on any performance - If i use dbcc shirinkfile(<file id>,truncateonly) in production hour Would it affect on any performance..If yes tell me any other...

Memory Setting - Respected All Can I set Min & Max Memory in sql server without AWE Enabled means that If I set Min Server...

After setting max SQL 2005 memory I get message - I have a database only server Windows server 2003 Enterprise SP2 SQL 2005 SP2 RAM 12gig Our production system 1) after installing memory I...

SQL Server 2005 Standard Reporting Services Install? - [b]First question:[/b] My disc for SQL Server 2005 Standard Edition shows 3 directories (eg. 2003, 2005 part 1, 2005 part 2)...

RE: SSIS or Import - Hello all, I have to figure out a way to analyze the EDI (electronic data interchange) files concerning ASN (advance ship...

sysperfinfo user connections != sysprocesses - Hey all, A little stumped. I wrote a quick audit procedure to basically monitor connections to every instance in my environment...

Slow performance - Hi, 1-what could be the right answere if some one ask you in interview that users are complaining about slow performance? I...

Trace filter not working correctly - Having two filters works fine in profiler but when i script and run as a trace the filter is ignored....

Best Practice to maintain the grwing Databases - Hello, I used to Backup the Database in SQL 2000 and then truncate the Log File and later shrink the log...

SS05 /PAE on a 6 GB server - SQL Server 2005 SP3 Standard Edition on Windows 2003 (Enterprise Edition SP2 32-bit) Primary database is 17GB. My server was running...

Adding index to every column - I have a report table I need indexes on (non clustered) already have clustered. My problem is that this table has...

Getting a row count from a text file using T-SQL. - Does anyone have an idea of how I can open a file using T-SQL and determine the number of rows...

Doing both Differential and Transaction Log Backups (regularly)? - My environment (coming in with it set) for SQL Server 2005 dictates that I have the multiple back-end SharePoint databases...

concurency or locks - Hi experts, need some valuble suggessins to fix this issue Process : We have one procedure which will process the .csv file...

Stored Procedure SQL 2000 to SQL 2005 - Hi, I have this stored procedure: CREATE PROCEDURE dbo.GetObsliquidacao @num_liq varchar(50), @ObsBloco varchar(50)=null out, @Obsarea varchar(50)=null out, @Obs varchar(50)=null out WITH ENCRYPTION AS declare @a int exec @a = dbo.IsDliInObsliquidacao...

Getting error in create Trigger - use [Test3] GO create trigger tr on Emp for update as select * from inserted; when i execute it i am getting this error Msg...

CU5 for SQL Server 2005 SP3 - Hi, We have Share point & BizTalk databases in SQL Server 2005 with SP3. So far we do not have any Cumulative...

Not able to reverse engineering over a database - Hi Guys, I found a bunch of SQL databases without a diagram, I am not aware on how they were built,...

Mixed Mode Authentication - Hi, I'd like to find out the general feeling amongst dbas as to whether to go mixed mode authentication or not? I've...

audit trigger on all columns in table - Hi, how can i create an audit trigger to capture all changes (insert,update,delete)in a table columns with the old value and...

Some interesting thoughts on SQL Server - Recently I was talking to an Oracle DBA and I was telling him about some of the architecture features of...

Down Grading SQL Server 2008 - hi all, i have task to Down Grading SQL Server 2008 from enterprise edition to devloper edition due license issue. I am...

sp_updatestates is only sampling 1% of my data. - Hi, Can anyone help? sp_updatestates is only sampling 1% of my data when executed. I recently discovered some poor performing queries...

installing SQL Server 2005 - Hi, I'm installing SQL Server 2005. I have the following drives: C -OS, D -Data, E -Log, T - Tempdb, Z -Backups. So...

SQL Server 2005 Patching from RTM to SP3 - Hi Experts, I have a test database which is now on RTM (9.00.1399.06 RTM Standard Edition). I am testing the patching process...

Disaster revovery when no master database - Everything I read about restoring the master database requires the instance to be started in single user mode then restore...

Changing database collation sql 2005 - Hi all, I just have a question regarding changing the database collation after a database has been used for while. What is...

How to increase amount of log.trc files in SQL Server 2005 - SQL Server 2005 has an always running trace - called "default trace". All object drop/alter/creation is audited (among other things). It...

SQL Server 2005 : Backups

Scheduled backups for LARGE server installations - Hi people, I need some advice. We have a very large installation of sql server express, about 200 clients at different...

Restore one file from a backup device - Hi all. I frequently restore the hourly backup of our live database to a test area to have fresh data to...

Selecting databases to backup - do not include tempdb - Hi Guys, I have created a maintenance plan which runs nightly. One of the tasks it does is backup a bunch...

MS SQL 2008 Standard backups cause application jdbc timeouts - I have a situation where the backing up 16 databases is taking around 1 hour. This doesn’t bother me so...

Determining how long a SQL Job takes - Easy question, and I'm sure the answer is equally easy. We're getting ready to move our agency, and thus our...

SQL2005 Backup to network fileserver failing (operating system error 5) - Good morning, The problem I am having appears to be a problem others have encountered, and I have worked through their...

SQL Server 2005 : Business Intelligence

Where is the SQL Server project template? - Hi There, I have to create a CLR stored procedure for that it needs a "SQL Server project template" in...

SSIS with a Stored Procedure - Here's what I have: I have an SSIS project that reads an XML file and uses two of the three...

SQL Server 2005 : Data Corruption

5 consistency errors in table 'sys.syscolpars' - DBCC results for 'Test sks'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service...

SQL Server 2005 : Development

SP reference - Hi, I have written a set of 3 stored procedures. And each of the SP will be used in different set...

SQL Server 2005 : SQL Server 2005 General Discussion

Working with Locks--Please Help Big Time - I have a stored procedure that updates a row. Here is the problem. A web form has text fields : name,...

DELETE command - ANSI/SQL Compliant vs Transact-SQL - Just looking for some opinions/thoughts on using the ANSI compliant version of DELETE verses the TRANSACT-SQL version. We are using...

unicode - I was wondering how one can tell the language of the text in an nvarchar column. My client software seems to...

Mistery between SQL Express and Workgroup Edition - I've a problem for long time with dirty reads of data, finally i've seen that the problem is only in...

Foriegn Key constraint Removal - I am trying to remove records from a large database by truncating the tables. However, I have run into issues with...

Do nothing in a sql statement--help - How do you write a "do nothing" in sql. I have a statement where if two variables are 0, I...

How to set up Auto-Increment in SQL Server 2005 - I have a database table. I need to set a particular data field to allow for Auto Incrementing. Basically I need...

Unable to load client print control - My users have a problem printing reports from Report Manager.  The message "Unable to load client print control" comes up...

Cannot execute stored procedure on linked server - I have two 2005 servers that are linked.  Replication is working between them.  When I run the following in a...

Failed to notify ''operator name'' via email? - Following command sends email fine: EXECUTE msdb.dbo.sp_notify_operator @name=N'operator name',@body=N'test message' However, SQL Job with a notification to above operator doesn't work. Job history...

SQL Server 2005 : SQL Server 2005 Security

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. - [b]Problem Statement[/b]: I have some users that are not a part of any server role (except the default public). For each...

Password Change Date not current - We run a report on the first of the month to list the status of SQL Server logins on all...

SQL Server 2005 : SS2K5 Replication

for large database processing - hi i have initially 10 millions of data in single table of sql server 2005 with 10 millions rows. now i...

Pls help me in SQL 2005 Migration - Hi All, I have installed SQL 2005 in my server. The service pack installed is SP1. After installation the server...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Sql 2000 latest hotfixes - Can some one please send me the link to get the latest hotfixes for sql 2000 sp4? Also please provide...

view performance deteriorated significantly - Hello! I am using SQL Server 2005 64 bit Standard Edition. One of the processes I inherited selects all records...

Checking for Fragmentation level in sql server 2005 database - Hi, We have 260MB database. We keep issuing one or two update statements for every one day or two days. I...

Qry Performance - [quote]select empid, empspec into tmpempids from dbo.vwEmp as a inner join tmpeligdates as b on a.CustID=b.CustID, tmpEmpRange where (servcdate between job_start and job_end) and...

SQL Server 2005 : SQL Server 2005 Integration Services

breaking on null dates? - I'm using the import wizard SQL05... I"m importing a txt file, all fields (including date fields) are importing okay until I...

SSIS + .NET assembly: Server does not exist or access denied - Hi, I have a SSIS package that calls a .NET assembly from within a script task. The assembly is responsible for...

"VS_ISBROKEN" error message - hello, I created an import package in Sql server 2005 that gets the data from db400 database.when i ran it the...

SQL Server 2005 : T-SQL (SS2K5)

Need helping looping through records to send emails - Ok here is some test data.... ---===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#ChangeHistory','U') IS NOT NULL DROP...

Parent Child Hierarchy CTE query - Hi, I have managed to construct a CTE query to get a hierachy of parent-child related items from my table. (not quite...

Restore does not restore all stored procedure permissions. - Hi SQL 2005. I have a production server which I backup and restore to a test server. However, whilst most of the stored...

Which ranking function should be used ? - Sample Input : ----------------------------- EmpID----Date-------Status ----------------------------- 1-----01-Jan-09-----Present 1-----02-Jan-09-----Present 1-----03-Jan-09-----Absent 1-----04-Jan-09-----Absent 1-----05-Jan-09-----Holiday 1-----06-Jan-09-----Present 1-----07-Jan-09-----Present 1-----08-Jan-09-----WeeklyOff 1-----09-Jan-09-----WeeklyOff 2-----01-Jan-09-----Absent 2-

How many triggers can be implemented in a single table - [b]1. Well the question is simple. How many triggers can be implemented in a single table. 2. If a table has...

Nested Case statement - SELECT 'OPEN' as [AREA_DESCRIPTION] ,'OPEN' as [METRIC_DESCRIPTION] ,C.[IPC_SITE] ,C.[VALUE_STREAM] ,C.[AREA] ,C.[CAPABILITY] ,D.[USER_NAME] ,CONVERT(VARCHAR(11),[CalendarDate], 120) as DATE ,sum(case when (datepart(YEAR, date_closed) = datepart(YEAR,CONVERT(VARCHAR(11),[CalendarDate],120))) then 1 else 0 end) as [YTD] ,sum(case...

Running DOS Command Prompt(cmd) command thought SSMS - Hi All, Is it possible to run DOS command prompt commands through T-Sql Statement or by some procedure? I have...

Open XML from website or consume webservice, URGENT!!! - Hi All, Is there a way to open an XML file from a website directly from SQL Server? For example, http://someSite.com/someXML.XML. Or...

CTE's are useless - Firstly, Forgive me. From my understanding CTE's replace subqueries. But when working with large data sets, is it not more effecient...

SQL Server 2005 : SQL Server Newbies

Date format conversion question - I am trying to run a simple convert statement for use in a stored procedure. I just want to display...

question on primary key (unique clustered index) - Hey guys, i am pretty new to sql server and am trying to wrap my head around the difference between...

Insert Word Document - Hello Everybody, I want to insert a word document ( .doc ) into my table which keeps for example , applicant's name and his/her...

delete cascade trigger - Hi! again :-D table A has data i need to delete. But this data is refered to another table; B. So...

Does a table need to have a primary key in order to implement a trigger? - I have to update some columns in a table when they are inserted with missing values, and the value that...

SQL Server 7,2000 : Administration

Create a new log file... - *sigh* Well, allow me to start off by saying 1) This problem is not in my database; 2) I had...

SQL Server 7,2000 : General

DTS workflow precedence from On Success to On Completion - [font="Verdana"]Hi All, I have one DTS package where I have to shoot a mail whenever DTS get fails. Currently the workflow...

How can i increase datafile dynamically - Declare @requied_size as int set @requied_size=256 alter database test modify file(name =test,size=@requied_size MB Unfortunately this is not working.What i need to change...

SQL Server 7,2000 : SQL Server CE

sql ce 2.0 hotfix - I have read about a memory problem with the sql server ce 2.0 on http://support.microsoft.com/?scid=kb%3Ben-us%3B827837&x=16&y=12 and a fixed ssce20.dll file. Does...

SQL Server 7,2000 : Performance Tuning

Poor scan density on an index - I have a table that has over a million rows. Every night this table is truncated and repopulated. It has a...

SQL Server 7,2000 : Replication

Error SQL Server 2000 Replication process - Violation of PRIMARY KEY constraint 'XPKMDT_OPERATING_MODE'. Cannot insert duplicate key in object 'MDT_OPERATING_MODE'. (Source: TMInfo (Data source); Error number: 2627) Is there...

Not replicating DELETES & locking tables! - Hi I have a table that I'd like to use Transactional Replication, but not replicate delete statements but also not lock...

SQL Server 7,2000 : T-SQL

does ident_current() work for uniqueidentifer also - I have a table where uniqueidentifer is the primary key and is generated with newid() in default value. How do I get...

Stored Procedure Metadata - I want to write some ADO code automatically from the definition of my (many) stored procedures. This depends on being able...

Help writing a SQL - I have a situation described below - let me know if more information is needed. create table multrecs ( acct bigint, cdt smalldatetime, val varchar(20), ndt...

converting datetims variable string to datetime sql server - I try to insert into table a string variable date to column datetime: my Query: "INSERT INTO tblDate (myDate , WorkerNum) VALUES ( ' + d...

SQL Server 2008 : SQL Server 2008 - General

Linked server from 64-bit SQL 2008 to Sybase - Does anyone have a solution to our problem? We are using a 64bit version of SQL server and need data...

SQL Server 2008 : T-SQL (SS2K8)

very large update - I have a table which has 19 million rows and is 2.6TB in size. I need to update a varbinary(max) column...

SQL Server 2008 : SQL Server Newbies

How you seperate comma values in one column to more columns - I have table select vcLoginHistory from ResultsHistories where id =11507 Return values at column = vcLoginHistory in one table. 2009-09-17 15:03:51.840622,2009-09-17 15:08:48.6269,2009-09-17 15:28:31.794258 I...

SQL Server 2008 : SQL Server 2008 Administration

MSDTC - SQL Server 2008 on Windows Server 2008 Active Passive Cluster - Does MSDTC have to be setup as a clustered resource or can i simply enable local DTC on both nodes...

SQLAgentUserRole and Domain/Group as Login - I have a database that has one login (Domain/group1) Domain/group1 is assigned to SQLAgentUserRole. Domain/group1 has 2 users (Domain\user1 and Domain\user2). Now. Domain\user1...

Peculiar Instance Performance Problem - Hi. We have a named instance with +- 55 databases on it. Most of them are relatively small except for one that...

Programming : Service Broker

Synchronizing Databases Across Domain Boundries - I have a database servicing an outward facing web site that is in a domain that isn't trusted in our...

SQLServerCentral.com : Anything that is NOT about SQL!

Job conflicts-- "Scheduling Software" recommendations needed - We backup the Sql servers around 5pm. We image them later that night. On Monday nights we apply Windows Updates...

Reporting Services : Reporting Services

Security Issue - I am receiving the following error when I try to run a deployed report. "An error has occurred during report...

32 Bit vs 64 Bit Issue - Good morning all, I am having an issue making connections in SSRS. We have two servers set up. Both are running Windows...

Installation Problem? - I recently completed a Microsoft course on SSRS 2005. I'm attempting to get it installed in my test environment, and...

FYI:Printing Landscaped Reports in Reporting Services - Just wanted to share something I learned today in debugging an issue with reporting services not properly printing landscaped reports. ...

Reporting Services : Reporting Services 2005 Development

charts space issue - Hi Friends I have 5 charts in report. Here is the situation exactly.. I have report contains 5 graphs and 1 parameter. when i...

countdistinct of 2 null values returns 1 - Hello, I have a problem which I'm failing to understand. In a matrix, I'm doing a countdistinct of two fields (i.e....

Data Warehousing : Integration Services

exec a stored procedure in SSIS - I am executing a Stored Procedure in SSIS using OLE DB Source. EXEC xsp_abc 'Monthly' When I do preview, the stored...

Microsoft Access : Microsoft Access

Complex query in msaccess - Hi, I hv a table called myTable which hold record like this, BLOCK_ID TOTAL 1 0 2 19.22641 55 100 55 87.40159 55 62.51066 1 3.219 2 0.099 55 3.219 1 31.0688 2...