In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle ‘Six Scary SQL Surprises’
Brent Ozar joins the DBA Team, for Lesson 3 of their ‘Top 5 Hard-earned Lessons’. Gain valuable tips from the pros - Read now.
 
SQL Data Compare Save time when comparing and synchronizing database contents
"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.
 
SQL Monitor SQL Server monitoring that works right out of the box
SQL Monitor’s defaults are based on advice from SQL Server experts, so you can start monitoring today without an elaborate setup. Start monitoring with a free trial.

In This Issue

Deleting large number of rows from a table in a VLDB

For enterprise systems, purging data is a reality. Today, we will see some strategies that I recently implemented to make this process work efficiently. More »


Brent Ozar's Six Scary SQL Surprises

In lesson 3 of the Top 5 Hard-earned Lessons of a DBA series, Brent Ozar exposes six common but scary surprises that can lurk behind the façade of SSMS. Read it now, and learn hard lessons the easy way. More »


Using the SQL Server MERGE Statement to Process Type 2 Slowly Changing Dimensions

The MERGE statement is powerful and multifunctional, yet it can be hard to master. SOmetimes the MERGE statement that just doesn't do what it's needed to do, like process a Type 2 slowly-changing dimension. Check out this tip to learn more. More »


Help Guide the Future of SQL Compare

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 - The Saddest Thing I Ever Heard a Manager Say

image source: DazzleJunction.com I am currently going through the process of looking for a new opportunity for my career. I’m... More »


From the SQLServerCentral Blogs - Dust Off That Resume

Since I started regularly attending SQL Saturday events some five years ago, I’ve sat in on a number of professional... More »


Editorial - Writing

This was originally published on May 9, 2008. It is being re-run as Steve is at the Red Gate free Micro-Event for Database Professionals.

One of the great things about SQLServerCentral.com is that so many of you in the community share your work with others on a regular basis. We get articles from all shapes, sizes, and skill levels of DBAs, on all manner of topics. And most of the time, these articles are inspired by things that actually happen in your jobs.

They're not contrived or made up examples, or situations that only mimic a portion of the real world. You're writing about things in the real world.

With that in mind, we have an easy poll for you this week:

Why Do (or Don't) You Write?

I expect that if you don't want to write articles you might not respond here, but I'm still curious to see what thoughts some of you have.

For me, it's an easy question to answer: I really enjoy writing. The challenge of coming up with new thoughts, new ideas, trying to explain things to people, getting feedback in comments, it is just very exciting..

Often my wife will look up and see a goofy smile on my face after I've read some comment and ask me about it. I've even laughed out loud at times. And I've been ready to curse people at others.

But overall, the thing that I hated most in high school has become the thing I enjoy doing most of the time. I think about writing all the time and spend almost every day working on some editorial here or there.

So let us know why you write, or why you don't. Maybe I'll be able to convince a few more of you to participate and share your knowledge in the next year.

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


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.comto get better bandwidth and maybe a little more exposure :). 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.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

Which of these are true for subqueries? (select 3)

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

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

SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Get your copy from Amazon today.


Yesterday's Question of the Day

We have following 6 sql statements. Run each, one at time on SQL Server 2008. Which queries will fail? (select 2)

-- Query #1
USE master;
GO
IF DB_ID('DBDB') IS NOT NULL DROP DATABASE DBDB
GO
-- Query #2
USE master;
GO
IF OBJECT_ID('DBDB') IS NULL CREATE DATABASE DBDB 
GO 
-- Query #3
USE DBDB; 
GO 
IF OBJECT_ID('dbo.TBTB') IS NULL CREATE Table dbo.TBTB (tb1 int)
GO
-- Query #4
USE DBDB; 
GO 
IF OBJECT_ID('dbo.uspUSP') IS NULL 
 CREATE PROCEDURE dbo.uspUSP AS SET NOCOUNT OFF
GO
-- Query #5
USE DBDB;
GO
IF OBJECT_ID('dbo.TYTP') IS NULL CREATE TYPE dbo.TPTP AS TABLE(TYTB int)
GO
-- Query #6
USE DBDB; 
GO 
IF OBJECT_ID('dbo.udfUDF') IS NULL 
 CREATE FUNCTION dbo.udfUDF(@f int) RETURNS INT AS BEGIN SET @f=1 RETURN @f END 
GO

Answer:

  • Query #4
  • Query #6

Explanation: The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch. The same rule applies to Functions as well, but this is not noted in MSDN.

Ref: http://msdn.microsoft.com/en-us/library/ms187926(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/ms186755.aspx

» Discuss this question and answer on the forums

SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Get your copy from Amazon today.


Featured Script

Identify Obsolete Indexes

Drop indexes no longer being used. More »


Database Pros Who Need Your Help!

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

SQL Server 2005 : Administering

UPDATE failed because the following SET options have incorrect settings: - I Created a UniqueIndex on a Test Table with Some condition as below and user were getting error msg as...

sql server 2005 issues with nightly jobs - Hi, I am just starting to learn about these things, and i was hoping someone could give me some pointers to...

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

SQLServerAgent could not be started (reason: Error creating a new session). - I can't start the SQL Server Agent Service on a new installation. During the install I chose not to start the...

shrink fails with error - File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty - This is an fyi (no response necessary). I got this error and researched it and found no good answer for how...

SQL Server 2005 : Backups

Differential backup question - I do FULL backups every two weeks and daily differentials on a number of SQL instances. The backup command I use...

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

When would be the order by faster ? - [code="sql"]use DOMAIN_event go CREATE table email_destination_bk ( msg_stub ut_stub not null , acct_id int not null , evt_stub ut_stub not null, evt_code nvarchar(20) ) create clustered index idx...

SQL Server 2005 : SQL Server 2005 General Discussion

Reducing Initial LDF/MDF size - Hello, I have a database (version 9.0.4060) that has an initial log size set to 322GB. I would like to change...

SQL Server 2005 : SQL Server Express

Remove Time from DateTime - What is the difference between these 2 lines in SQL Server 2005 Express? [code="sql"]DATEADD(d, 0, DATEDIFF(d, 0, @Today));[/code] and [code="sql"]DATEADD(d, DATEDIFF(d, 0, @Today),...

SQL Server 2005 : SQL Server 2005 Integration Services

Help Passing a variable using OLE DB source - I'm having trouble passing information from a variable to a query. I currently have 3 variable for this: _QueryVariable, _ExcludeInfo,...

Setting Package protection level - Hi All, I have setup the package configurations(for both source database and the SSIS database) from SQL Server table, as they...

IS IT POSSIBLE ?--NOT to CREATE Flat File If it is empty? - I have a SSIS packages that does a SQL command on OLEDB Source and finds the records and outputs to...

SQL Server 2005 : T-SQL (SS2K5)

Query to find items purchased from multiple vendors - Hi All, I want to find item that purchased from more than one supplier, so i am running following query :- SELECT...

SQL Server 2005 : SQL Server Newbies

scheduling job - Hi friends, We need to monitor a user session for locks to troubleshoot a problem in the program. When the...

SQL Server 7,2000 : Administration

SQL SERVER services get stopped - Hi, Everyday we notice SQL SERVER services get stopped and we have to manually then start both services and agent.We...

Memory Error - Failed to reserve contiguous memory - I had the following warning in the sql log file 'WARNING: Failed to reserve contiguous memory of Size= 65536'. (SQL...

SQL Server 2008 : SQL Server 2008 - General

Insert Exec Performance issue ?? - Hi, I have a procedure which has a code something like My SP is dynamic something like WHILE(@i <= @max) BEGIN SELECT...

Fixed column names for Dynamic PIVOT Result - Hello everybody, I'm using two dynamic Pivot queries(which are basically the same except for the filter on date) to build a...

Populating fixed destination table with different source tables - I have a stored procedure which basically takes a table, performs some data manipulation then inserts the data into a...

Query for extended properties - Hi, Can anyone help me with a query which will select all the stored procedures with their Extended Properties. Thanks a lot.

sql server stored procedure logic problem - HI friends i have small doubt in sql server.plese tell me how to solve this issuse i have 2 tables...

IN Vs INNER JOIN - Which one is more performance oriented query? 1. SELECT 1 FROM table1 WHERE Id IN (SELECT Id FROM table2) 2. SELECT 1...

Sql Server 2008 Stops when dbo.Decrypt('') used. - Hi there, We use Microsoft [b]SQL Server Enterprise Edition (64-bit) Version- 10.50.1600.1[/b] in our company. Whenever I use dbo.Decrypt() with blank...

Result of dynamic sql with parameteres into a variable - Hello, i have problem with dynamic sql in cursor and i want to set to variable first I had problem, when...

Asking If I can Create a table for prices - Hi, I wanna ask if I can put just one table for both prices an cities. In the beginning I...

Droping or moving old tables - How to validate the last updated date for each of the tables. I want you to check if the tables are...

SQL Instalation Issue - Hi All, I have tried to install SQL 2008 Ent evalution edition on Win 2008 R2 Datacenter edn. and SQL Engine...

SQL server version Requirements For installing Dotnetnuke - What are the server requirements in order to install dotnetnuke? Can any one kindly help me to evaluate the technical requirement...

Insert help - I am trying to insert records into the database as follows insert into lookuptable select * from lookuptable1 which produces Msg 2627, Level 14, State...

Granting Explicit View Definition Permissions on Stored Procedure to dbo - The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they...

Read a backup file to find the version - Is it possible to read a backup file directly and determine the version of the server that created it? I've...

Custom print? - Has anyone built or seen a custom Print function for SQL? I'm running into the 4000 character limitation and before...

Synonyms and performance - How does the use of synonyms effect performance? More specifically, are synonyms replaced with fully qualified names in execution plans? It...

SQL migration - Whats the best way to copy logins, users, logins with password along with exact status of enable/disable from source 2008...

LiteSpeed impact on msdb database? - We have a customer who wants to use LiteSpeed for backup process on few SQL Server 2008 EE machines. We...

create procedure which contains special caracters from .sql file with sql powershell - Hello, I have a .sql file which is actualy the body of a stored procedure. The procedure containts characters like [b]'é,...

Alter User Defined Data Type - I'm trying to alter a column in a table that corresponds to UDDT, this column has a primary key and...

Virtualization - yet again ... - I spent some time searching this forum and the interwebs and haven't found a lot of definitive insight/agreement on virtualization...

Formatting issue in SQL - Hello all, I have seen some strange things in my SQL Server 2008 enterprise edition. whenever I do sp_helptext for...

Query to get rows if value contains result of another query - hi, I have searching for a query that returns a row if column value contains result of another query. Its like...

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

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

Memory leak on witness instance - We have a high availability sql server environment with synchronous mirroring and witness which has been working mostly fine for...

Getting minimum of top n rows without using subquery - Hi, We use SELECT min([date]) FROM table WHERE [date] IN (select top(100) [date] from table order by [date]) query for selecting...

Virtualization reviews - i have to Assess server landscape –Server reviews –Virtualization reviews –OS/patch level reviews can any one help me out regarding Virtualization reviews. what does it...

Archiving - MSSQL2008 with 1 TB Harddisk space Databases: DB1 - data for 2012. DB1_Arch - data for 2011. *** Inherited this server; looks like he idea...

Failure to calculate super-latch promotion threshold - I saw this error message in the error log of one of my SQL Server 2008 R2 servers today (with...

Msg 911, Level 16, State 1, Line 1 Database 'databasename' does not exist. Make sure that the name is entered correctly. - Dear All, Would anyone be able to let me know how to resolve the following error message please? Msg 911, Level...

Error 7391; Unable to Begin a Distributed Transaction - Hey All, I am trying to get a list of error messages on a server using the sp_readlogerror stored procedure. I...

SQL 2008 DDL Auditing - A Full Self Installing/Updating Solution For Whole Server - [size="4"][b]Introduction[/b][/size] We will see a totally free DDL Auditing solution (and source code) for the whole server regardless of SQL Server...

SQL Server 2008 : T-SQL (SS2K8)

Cursor replacement help - Morning, Thank you for your help in advance, I have built a cursor to run through list and with the...

ORDER BY = Bubble Sort ? Quick Sort ? Insertion Sort ? - Hi All, When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer? 1)...

sql server stored procedure logic problem - HI friends i have small doubt in sql server.plese tell me how to solve this issuse i have 2 tables...

How to Convert Semi colon Separated Values into Column - I am working on a integration project where I am receiving three string parameters ItemCode ItemName Amount and values will be...

remove duplicates IF matches - So I have the following statement: SELECT ca.Caseid,[weeknum],[monthName], Response1_Num , Response2_Num, Response3_Num, Response4_Num, comments,[userlogin], [supervisor], [customer id], comm_ID FROM HMD hm join...

Performing strange conditional COUNT - Hey guys, Can someone show me how to go about solving this problem? I thought it would be pretty straightforward...

Delete large data from live table - I have to delete data from live table( Size of DB:terabytes) (system is becoming slow). without affecting the end users...

Add login, user, roles in mult db's dynamically - I am attempting to create T-SQL that I can re-use on different db’s, users, domains, roles, etc… The trouble I’m having...

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

SQL Server 2008 : SQL Server Newbies

Import records in MS Access table to native SQL Server 2008 table - Hi SQL server experts I am trying to use the SQL Server Import and Export Wizard to perform this simple task....

Query hangs on table variable - I have a query that consistently runs fast and returns the expected output, but when I try to insert the...

Stuck on new fault with my update - Hi Professionals I am running the following query as advised previously which updates the source table based on a column...

View results into string - I have a table where I need to take any number of financial distributions and stack the accounts into a...

Move Database From One Server to Other - Hi All, What I am trying to do is that I am trying to copy database from Other Sever. I am...

SQL Server 2008 : SQL Server 2008 High Availability

sql server mirroring synchronization not happening - Hi all, I have mirroring setup, Seems to everything fine.But synchronization not happening.i don't understand what was the problem. Please any one...

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

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

SQL Installation on Windows 2008 - Hello, I have task of SQL 2008 installation (2 installation) on windows 2008 cluster server ( 2 Node). There are 2 approaches comes...

Transactionla Replication - Very first step while configuring the Ditribution.. error comes on last step :w00t: Property LoginSecure cannot be changed or read after...

The Recycle Bin is corrupted on :/drive do you want to empty recyclebin on the drive - We have the windows server 2008 R2 enterprise in the cluster and i each time i login i am facing...

SQL Server 2008 : SQL Server 2008 Administration

#Files and FILEGROUPS - Hi, In an 8 CORE machine with 3 RAID10 (data files, log files and tempdb) what's the appropriate number of files...

Data Archival/Purging - through table partition switch IN/OUT - Hi All, I have a daily partitioned table 'TBL' and I hold data for 30 Day's. I archive data to TBLHistory...

Duties of a DBA - Could someone please brief me about the daily duties of a production support SQL Server DBA?

Database getting into recovery mode - Database getting into recovery mode is every morning.

Question about DBA authority versus responsibility - Hi, I'm looking for some advice for how I can assert myself in this situation without alienating my colleagues or causing...

Mirror failover - Hi All, I have an issue with mirror failover 1. I configured mirroring on 3 databases. if in 3databses one got failover...

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

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

Starting up in Single user mode - I am testing a disaster recovery process but am unable to restart any 2008 systems in single user mode so...

Programming : General

Web dev suggestions - In some circles this could be as touchy a topic as asking which religion to follow. I'm a full time...

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

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 doesn't run from Reporting Server - Dear All, I'm trying to run a Report on the Reporting Server but for some reason the reports executes but hangs....

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 Development

dynamic sql with IN operator error - In my dataset I have this script. Report parameter @acct is set as multi-value. When I select a single value...

Line hight for barcode - font 3 of 9 Barcode - I am developing a GRN label print report and have installed font 3 of 9 Barcode. I want the barcode...

Database Design : Design Ideas and Questions

Database describing databases and references to tables - Hello, I asked a question at dba.stackexchange.com [url=http://dba.stackexchange.com/questions/35855/database-describing-databases-and-references-to-tables]here[/url], that's not getting a lot of interest. Now that it has fallen...

Data Warehousing : Integration Services

Problems with Excel source extraction - I have an excel file with sheet containing 210000 records..I am trying to extract this file into a SQL table. In...

Discover the column types of a DB2 DB without Data Dictionary and Documentation - Hi all, I just want to discuss a method with you to discover the column data types of a table in...

Documentation in SSIS - I am just starting learn and use SSIS, and was wondering how you document changes? In a stored proc I...

IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found - I have a Macro which works in Excel. It basically just formats headers and creates a validation list so the...

Integration services Configuration - Just wanted to find some information on SSIS configuration. Consider this scenario: I have a datasource on server A in...