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

How to Make Scalar UDFs Run Faster (SQL Spackle)

Jeff Moden from SQLServerCentral.com

It's a well known fact that Scalar UDFs are the stuff of performance nightmares in T-SQL. But are they really as bad as they say? SQL Server MVP Jeff Moden shows us that they might not really be as big a problem as you might think and what you can do when they actually are. More »

SQL Saturday #161 - East Iowa

Press Release from SQLServerCentral.com

A free day of SQL training in Iowa City o Aug 11. Come meet other SQL Server professionals in the area. More »

PowerShell SMO: Just Writing Things Once

Additional Articles from SimpleTalk

Sometimes, you can tire of writing the same PowerShell code once again. After this happened to Phil whilst keying in an SMO pipeline to access databases, it occurred to him that he should have only one pipeline to access databases in PowerShell, one reusable pipeline More »

Utilize SQL Server Storage Effectively

Press Release from SQLServerCentral.com

The reality is raw storage is cheap, but SANs, software and management of the storage is not. Fortunately, if you plan your database and data management correctly you can build and manage a cost effective yet high performing SQL Server solution. This session will introduce some best practices and options focused on helping you manage your SQL Server storage in an easier way using the native SQL Server toolset. This session will dive into some of the latest trends, database design implications, data archiving and alternatives such as SQL Server compression, BLOB data storage, filtered indexes and more. We’re also going to show you one Red Gate tool that can make you even more efficient in managing your SQL Server storage. Wed, Aug 15, 2012. 11am EDT. More »

From the SQLServerCentral Blogs - SQL Server : Dedicated Administrator Connections

Think about a scenario : People are complaining about your production database server.Many users are getting continuous time out error. Rest... More »

Many years ago I got a summer job waiting tables. The first day I arrived, ready to begin training, two people called in sick. I was thrown into the job, needing to quickly learn the menu and routines to get people their food. The next year I got a different job bartending, and found myself in almost the same situation. No one to train me, work needed to get done, and I had to fumble around learning as I went. In both cases I did well, and I had good managers who both understood I'd make mistakes and appreciated that I learned quickly. I could easily have had bad managers that assumed any problems were failures on my part and would have let me go, even when they hadn't been able to train me.

It's an old saying that sometimes when you find yourself getting out of one problem, you fall into another. That's what happened when I traded one problem, unemployment, for another, getting up to speed in a new job on my own. It's what happens to many DBAs, as evidenced in this Dilbert comic strip. In many of the companies I've worked for or consulted in, I've seen a number of system administrators thrown into the DBA role, and asked to manage SQL Server instances, with little to no training.

SQL Server is an amazing platform, and it can often run itself for a long time without anyone actively managing the system. However there comes a time when things start to break down, and often break down quickly. There's any number of consultants making a good living cleaning up messes from "DBAs" or developers who are in charge of SQL Server instances without really understanding what they should be doing.

Managers get complacent, especially when systems seem to be running fine. They don't think their administrators need any training or help. While I try my best to help educate administrators with lots of basic content, there's lots of work to be done. When you see good basic articles, or fundamentals things administrators should know, pass them along. It's up to most of us to learn on our own, and with a little suggestion and prodding, many of us will learn more.

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. Today there are no podcasts due to technical difficulties, but they will return tomorrow.

You can also follow Steve Jones on Twitter:

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

Today's Question:

How many rows are returned by the query below In SQL Server 2008?

By sreepathi1987

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

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

Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". Grab your copy today from Amazon.

The following BACKUP statement between the CREATE and DROP will not succeed on all versions and editions of SQL.  Choose the version(s)/edition(s) of MSSQL that the statement will successfully execute on. (choose 5)

CREATE DATABASE TestDB

BACKUP DATABASE TestDB TO DISK='TestDB.bak' WITH COMPRESSION

DROP DATABASE TestDB

By Lance Brigham

Answer:

• SQL Server 2008 Enterprise
• SQL Server 2008 R2 Standard
• SQL Server 2008 R2 Enterprise
• SQL Server 2012 Standard
• SQL Server 2012 Enterprise

Explanation: WITH COMPRESSION option supported in Enterprise edition of all versions of SQL. WITH COMPRESSION supported in Standard edition of SQL starting with SQL 2008 R2 and higher (WITH COMPRESSION could not be used in SQL 2008 Standard and lower).

Ref: http://technet.microsoft.com/en-US/library/cc645993(v=sql.105).aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.

Create_sp_help_revlogin_DB

David A. Long from SQLServerCentral.com

Enhanced sp_help_revlogin More »

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

SQL Server 2005 : Administering

Can't drop primary key? - Hi I'm struggling to drop a primary key. I get the error: An explicit DROP INDEX is not allowed on index 'MyTable.PrimaryKey'....

return parent child relation - PRNT CHLD POS 1050000 1000000 1 1050000 5000000 2 1050001 1000004 1 1050001 5000002 2 .............................................. .............................................. 1000000

Linked Server vs Firebird - I need to create a linked server from Sql Server 2005 vs a Firebird database. How can I do, using free...

Prevent users from impersonating sysadmin using runas /netonly - So I just learned that some of our users are using a VM to impersonate a sysadmin and logging into...

i dont have rights to access the location of a file - Hi!! i know nothing about p.c's and i'm freaked out.. I formated my laptop (windows 7 pro.), After back up,i transferred...

microsoft sql server management studio is unable to load this document. Class not registered - I tried to open a maintenance backup plan and received the following error "microsoft sql server management studio is unable...

How to view subscriptions to a publication - Hello all, I am attempting to delete a publication in SQL Server 2008. When I attempt to delete it, I...

SQL Server 2005 : Backups

Backup of standby database - OK, before I ask if and how to do this please let me explain why I’m attempting to do this....

SQL Server 2005 : Business Intelligence

split table data into multiple execel sheets in ssis - how to send table data into multiple sheets in ssis. supoose table data like id ,name,deptno 1 ,jai ,100 2,ravi,360 7,ran,100 ...

ssis execel - I'm working with a set of data from SQL Server that I'd like to get into a group of Excel...

SQL Server 2005 : Development

How to get current week as 0 for any year using week - Hi, I need to display current week as 0 for any year in the report......using week column in week table. I have...

Querying XML - Hi everyone, I have the following table: CREATE TABLE [dbo].[XmlImportTest]( [xml_data] [xml] NULL ) -- I add the following data in: insert into dbo.XmlImportTest (xml_data) values ('<ROOT> <Customers> ...

SQL Server 2005 : SQL Server 2005 General Discussion

Installation of SQL Server 2000 after the installing SQL 2005 and 2008 R2. - Hi, I have installed SQL Server 2005 and 2008 R2 on my machine. Can i now install SQL Server 2000 (Client...

out of memory - hello, we are on: Microsoft SQL Server 2005 - Ent Ed 32-bit SP4 mutltiple dbs running used for web applications On Jun-9: - SP4...

Insert a character in a string - Good afternoon, I have part numbers that begin as RA99999-99. I need to update them all to read RA099999-99. Thanks for...

Converting Null Sum to zero in MS SQL Server 2005 - I used "Blahs" just for this example: One of these queries will sometimes add up to be zero (No payments...

SQL Server 2005 : SS2K5 Replication

There is already an object named 'FK_OSS_KBN_PC' in the database.(Error: 14151, Severity: 18, State: 1) - Hi All, I am getting this below error and i can't figure out why this is happening.if anyone has ever encountered...

Database Mirroring - Hi Gurus, Due to some network glitch mirroring connection is disable. Is it possible establish the connection automatically once network connected...

Dealing with a database corruption on a merge replication subscriber - Merge replication is being used to provide inter-site high availability for a 7*24 database with 99.999% availability requirements (we have...

reinitialize suscriptions - What does this exactly mean? Is it almost the same as restoring the subscriber to match with the snapshot data?...

Same article in multiple publications - We have a merge replication scenario with pull subscriptions. Currently there is only one publication but we are adding a...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Upgrading from SQL Server 2000 - I have a great deal of stored procedures and data integration that need to be optimized. All the SQL code...

SQL Server 2005 : SQL Server 2005 Integration Services

Issues with cluster SQL Server 2005 installation - I had my windows admin to configure the windows part of cluster(virtual group name and map the cluster disks) But he...

SSIS Decrypt 7zip - Recently I downloaded 7zip to use to decrypt archives. Has anyone an example of how to pass in the password...

SQL Server 2005 : T-SQL (SS2K5)

creating a view to include fields which are NULL - Our webiste has several contact forms which all go into the same table. We therefore have to create views to display...

SQL Server 2005 : SQL Server Newbies

Deadlocks: Determining the victim with little info from Profiler... - Hello all - So, I have been hunting down and resolving deadlock issues over the past week, but have come across...

SQL Server 7,2000 : Backups

Backing up multiple databases on the Daily baises - I want to backup multiple Databases on Daily biases and create a file for each Database. I would like the...

SQL Server 7,2000 : Data Corruption

How to repair the table - when i running this... DBCC CHECKDB('PMSdb') WITH NO_INFOMSGS Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page...

SQL Server 7,2000 : T-SQL

What is the best way to iterate XML elements in SQL? - I'm having problems trying to determine the best way to iterate through multiple instances of an element so that I...

sql time intervals problem - Hi, I want to sum the times for each machine state for each shift. state0-machine stooped state1-machine with alarm state2-machine running I have table...

SQL Server 2008 : SQL Server 2008 - General

Detect hex 0x00 in nvarchar column - Hi, this is my first post I have a 3rd party adapter in SSIS - which gives an error "'.', hexadecimal value...

Database Mail - Hi, Could you please tell me how to check if Database mail configured or not in sqlserver 2008r2 Server. Thanks

Recursive select? - [code="sql"] CREATE TABLE #tblItems ( ItemID int, ItemDescription varchar(50) ) GO INSERT INTO #tblItems (ItemID, ItemDescription) SELECT 1, 'Item 1' UNION ALL SELECT 2, 'Item...

keeping dev and live databases in sync - Does anyone know of any ways to keep a dev database up-to-date with changes on a live database without doubling...

Backup and recovery model - i have asked this question please let me know what to answer on this, Q is : one database is there...

SQL - i have two tables 1) issue and 2) actions. in Action table i have employeename(ramabai) and employeeid(4402) and ISSUEID(521779) which common...

replication- PPTR - PPTR replication is failing with following error. I am kind of new to replication. Please advise how to proceed. thanks Violation...

SQL - I have two sql table Actions and Issues: i require which query will execute following questions. 1. Number of complaints attended...

full Backup slow after Restart - Dear all, I have a database on SQL Server 2008 R2 enterprise edition whose backup suddenly running slow since a server...

can any one plz give some idea about SQL Server Integration Services (SSIS)? - here i am having two database and i want to mapp the data related to that tables

How to see created(using sp_addlinkserver) AccessDb64 in sql server 2008 by - plz, replay me!!:)

Differences in Query execution plan between 2 servers - Hi all, I have a server running on Amazon which was duplicated on Data Center. Databases are identical, statistics is uptodate,...

Record linkage advice required– Long read (YOU HAVE BEEN WARNED!) - Hi, I have the following table structure: [code="sql"]create table dbo.PreMerge ( master_id int, Title varchar(20), Forename varchar(50), Surname varchar(50), CompanyName varchar(200), CompanyName2 varchar(200), Address1 varchar(200), Address2 varchar(200), Address3 varchar(200), Address4 varchar(200), Town varchar(100), County varchar(100), Postcode

Subquery returned more than 1 value. This is not permitted - I am getting the above error when running the below code. This has started happening on this bit of sql after...

Understanding restore and recovery in sql - I want to know exact mechanism used while restoring a database.Not getting the three phases of restoring i.e copy,undo and redo. what...

Select Image From Hard Disk - [color=#000000][/color]Hi, How can I write sql query like select c:\1.jpeg as 'Image' to capture picture from hard disk and represent that...

how to get count from another table - Hello... I need report whose 4 fields are from one table sat id, name, type. And for each type, need total count...

How to Retrieve the image data from database table and show in SSRS - Hi 1) My Table is Create Table Image_table (id int,images image ,crdate datetime default getdate()) Insert into Image_table (id ) Select 1 UPDATE dbo.Image_table SET images...

subscriber reinitialization (Replication) - Posted this under sql server 7,2000 replication category and was wondering why I don't get any replies. Anyway here is...

temp tables work oddly - Dear All, I have one doubt that I want to clear out by help of your thinking and reasoning. A stored procedure...

Database Redesign - I have to redesign the database based on the new business requirements Could someone please help me with the steps...

adding login with renamed AD account - I am trying to add a login to a SQL server 2008 R2 SP1 active/passive server on windows 2008 R2...

autogrowth settings not inherited from model - When I issue a 'CREATE DATABASE mydb' statement, I would expect the database settings to come from the system database...

Differenciate FOR LOOP and FOREACH LOOP IN SSIS - Hi Could anyone kindly explain the difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS. Thanking you, Regards Ram

SQL Server 2008 : T-SQL (SS2K8)

Query throwing error when converting to Numeric - Hi Experts, Please help me in this. I have a table where i am storing numeric value in Varchar column. when i am...

Query help - I have two tables with folowing sample data: tabA Id ParentId 1001 NULL 1002 1001 1003 1001 1004 NULL 1005 NULL tabB Id Value 1001 123 1002 25 1003 30 1004 85 1005 218 Now I want a result like this: Id Value 1001 178 [Aggregate values of 1001+1002+1003] 1004 85 1005 218 As clearly mentioned, 1002...

Updating Multiiple value - Suppose i have 2 table as Emp and Emp2. select empid from Emp where deptid=2 The output of the above query is empid 1 2 3 5 7 9 10 16 17 Now,Insert...

What can be used as Arrays in SQL Server? - Hi All, I am new to SQL Server.Prior to this i had done some programming in C. Consider the below query, select empid...

Group By Help - I have a script that uses the GROUP BY clause and returns the SUM much greater than expected. Here is...

practical application of REVERSE function? - Will people kindly share practical applications of REVERSE sql function? I do not see any posts on this forum addressing...

stored procedure- error - Hi, I have attached two files regarding execute SP at sql server 2008 databases,... It was working fine with SQL 2000...

repeatin value in my procedure - Hi friends, I m write procedure for the problem is account trans date amount ========================== E2674 Cr 02apr02 3000 R7890 Dr 06jun11 5000 E9700 Cr...

Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE - I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed...

Calculations using the Money data type - I have what ought to be a simple problem, but is more subtle than I thought; what data type to...

SQL Server 2008 : Working with Oracle

Queries running long - I am in the process of upgrading several old servers to SQL Server 2008 R2 and am having a problem...

SQL Server 2008 : SQL Server Newbies

How to loop each id in sql server 2008 - Hi All, From Front End i select some checkboxes it comes to database with comma separation like '1,2,3'. Now i want to...

Log file growing.. - Hello Masters, My log files are growing too much... I am taking regular backup but I am recieving disk space critical...

Need some help with some SQL or table design - Hi, I've been trying to work out the best approach to retrieve some data from some tables. Its basically based on...

Re-entering the DBA field - Looking for Advice/Suggestions - Hello Everyone, I'm a marketing professional looking to get back into the IT field that I left 10 years ago. I...

Cannot connect to SQL Server 2008 R2 remotely - I've attempting to create a Development box that host a SQL Server 2008 R2; however, I'm unable to connect to...

SQL Server - Identity - Hi There, Can anyone tell me what is an Identity? Thanks

How often SQL Update Statistics job recommend to run - Hi, Just a quick question. Probably would hve answered earlier in this forum, but I couldnt found an answer which...

SQL Server 2008 : Security (SS2K8)

Monitoring Changes to Users in databases. - Hi All, I have a peculiar issue. I got a complain for some developers that their permissions are being removed every...

SQL Server 2008 : SQL Server 2008 High Availability

Problem in Log Shipping - we have Setup Log shipping in SQL SERVER 2008 environment.Today we faced with a problem in our secondary database.Every time...

SQL Server 2008 : SQL Server 2008 Administration

Negative Spids - Recovery - Hi All I'm confused about the the topic of Deferred transactions (-spids) and database recovery I read and tested the steps in...

Index Usage - Deleted - Hi All I'm using the below script to track index usage on a particular table. Why does the leaf_delete_count show zero? I...

Full DB Backup of filestreamed database slow after restart - Dear all, I have a database on SQL Server 2008 R2 enterprise edition whose backup suddenly running slow since a server...

Career : Employers and Employees

Technical Interview - I feel dumb now.... :) - Hello, I wanted to post this out to the community to guage my level of "dumbness". Let me start by...

Career : Resumes and Job Hunters

How can I attract Senior Database Administrator candidates? - I have a few full-time DBA positions open on my team, one of which is for a Sr. DBA. We...

SQLServerCentral.com : Anything that is NOT about SQL!

Discussion: is e-Commerce making a mockery of SQL Server? - Some of you may have heard of my latest tales of woe, in that I am in an environment that...

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 ISSUE - i have developed two dump reports in ssrs 2008 r2. in that report one report have 31 columns and rows...

Database Design : Design Ideas and Questions

Pros and Cons - Table with loads of uniqe columns VS Row based keys with row data type - Hi, Which one is a better design strategy with respect to flexibility, performance and maintainability? A1: Defining Nullable columns in one table OR A2:...

Data Warehousing : Integration Services

ssis do you need a path for both lookup outputs? - I am relatively new to ssis although i am getting better :) I seem to struggle with lookups tho, a lot...

handling nomatch output from Lookup in SSIS - Hi All, I loaded my lookup values into a sql database. i am using lookup task to compare my source file...

SSIS 2008 - Download File Base on Last Modified Date Property - Hi all, I have a table in my developing database that has URLs. Each URL is a link to a source...

Join operation over String non-index fields - Hi all, I have to perform some joins between a Fact table (with millions of records) and some contextual tables....

Data Warehousing : Strategies and Ideas

Treating measure aggregation values as dimension attribute values for slice and dice - At my company a decision has been made to make use of a third party analytical product (consisting of SQL...

Data Warehousing : Analysis Services

Report Builder 3.0 Subreports - Error: Subreport could not be shown - Hi all, So I continue to delve into the bowels of Report Builder 3.0. My last post was asking about how...