In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Get your priorities straight with SQL Monitor
“SQL Monitor gives me the ability to quickly see what my priorities should be, and delivers the information I need to make the right decisions,” Aaron Kolysko, DBA. Start monitoring with a free trial.
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL Backup Pro "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.

In This Issue

Search a value to complete database

Learn how to search for a value throughout all database tables. More »


An Incremental Database Development and Deployment Framework

Often, an existing database application must evolve rapidly by incremental steps. Alex describes a tried and tested system to provide an automated approach to deploying both new and existing database systems, whilst dealing with common security and configuration issues. More »


Expand Your Skills at the PASS Business Analytics Conference

Join top business intelligence and analytics experts in Chicago April 10-12 for 60+ sessions on the best practices, prescriptive guidance, and strategic vision you need to get the most from your data. Use code BACSSC to save $250 today! More »


From the SQLServerCentral Blogs - A Rickety Stairway to SQL Server Data Mining, Part 10.1: DDL with DMX

by Steve Bolton                 As users become more familiar with software, they typically follow a progression that begins with learning the... More »


Editorial - The Gambler

In April of this year, the SQL Intersection conference is coming to Las Vegas. I'm speaking, along with Grant Fritchey and many others. It's a fun event, in a city with a huge variety of things to do in the evenings after a full day of SQL Server sessions. At night I tend to look for networking chances to met new people and catch up with friends at night, though there have been a few times a comedy show has enticed me away from my hotel. I like Las Vegas, though I'm not a gambler. Despite the fact that most people think of visitors looking for their chance to sit at a table with dice or cards, there are many of us that go for other activities.

I was at in a session recently and heard a speaker recommend that the audience run DBCC checks regularly. That's good advice, and it's what I recommend in my sessions as well. A person in the audience raised their hand and politely disagreed, saying that they almost never run DBCC CHECKDB. This person found it to be a waste of resources since they'd never encountered corruption in their career, and hadn't known anyone in over a decade that had experienced on a SQL Server system. This person asked the speaker how many times the speaker had seen corruption (five was the answer) and then said across thousands of days of backups, it just wasn't worth the resources to run DBCC CHECDB.

If you feel that way, then you're a gambler. You are accepting a higher level of risk than I do, and higher than I recommend. Consistency checks are designed to help us catch corruption. Since we never know when it will occur, we want to detect is ASAP so that we avoid, or at least minimize, data loss. If you run those checks and never experience corruption, those checks are insurance payments you've made and never needed to file a claim. However if you don't run those checks, and experience corruption, you've placed a bet you've lost. Whether or not that cleans you out depends on the data loss your organization experiences and their tolerance for that loss. I'd seriously consider this a career limiting, or employment terminating, event, especially if the best practice recommendation from Microsoft and many experts is to run DBCC checks.

I don't recommend skipping your DBCC checks, but if that's how you feel, think about coming to SQL Intersection (register with the code "Jones" to support me). You might enjoy that gaming tables at night, and I know the other speakers and myself would welcome the chance to change your mind about skipping DBCC checks during the day.

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

Everyday Jones

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


Question of the Day

Today's Question:

DECLARE @Datetime datetime = '2013-02-24 23:59:30'
SELECT Day(Convert(smalldatetime,@Datetime))
SELECT Day(Convert(Date,@Datetime))

Do the select statements return the same day? What will be the outcome?

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

This question is worth 1 point in this category: Rounding. 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 Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Yesterday's Question of the Day

The following statement is used to create a temporary table:

Select
       1 As [RowId]
     , 'True or False: The HAVING clause is the 4th phase of logical query processing?' As [Question]
     , 'True: 1-FROM, 2-WHERE, 3-GROUP BY, 4-HAVING, 5-SELECT, 6-ORDER BY.' As [Answer]
     , '15 Jan 2013' As [PublishDate]
     , {d '2012-11-15'} As [SubmitDate]
  Into
       #TempQoD;

What will the data types be for the PublishDate and SubmitDate columns?

Note: This has only been tested in SQL Server 2008 SP3 with the SQL_Latin1_General_CP1_CI_AS collation.

Answer: PublishDate is VarChar and SubmitDate is Datetime

Explanation: After running the Select Into statement, the following code shows the data types.

SELECT
       c.name AS [Column]
     , t.name AS [Data Type]
     , c.collation_name
  FROM
       tempdb.sys.columns AS c
          INNER JOIN tempdb.sys.types AS t ON t.system_type_id = c.system_type_id
 WHERE
       object_id = object_id('tempdb..#TempQoD')
 ORDER BY
       c.column_id;

I could not find a specific reference for this behavior. The mention of data types in the SELECT INTO documentation is rather lean. This may be the kind of knowledge that can only be gained through experience.

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

» Discuss this question and answer on the forums

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.


Featured Script

List all tables and their respective information.

List all tables in a SQL Server database with the details of them. 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

Automatic Alert for Full of Primary file group in Sql server. - hi guys, is there any ways to have a automatic alert when primary file group is full in Sql server...

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

My MSDB Database is over 25 GB on production server - The following tables are consuming alot of space in the msdb database. LakeSideLockLogger._LakeSide_DbTools_LockLog-------------------------14.5 GIG LakeSideLockLogger._LakeSide_DbTools_LockExecStack-------------------7.5 GIG LakeSideLockLogger._LakeSide_DbTools_DeadlockLog---------------------0.4 GIG LakeSideWaitsLogger._LakeSide_DbTools_WaitsLogger_WaitsDBLog--------0.3 GIG Please hel

SQL Server 2005 : Backups

Windows Server 2008 Backup Utility - H

SQL Server 2005 : Business Intelligence

SSRS: How to make two tables have the same number of row - hi experts I have the following dataset1(MDX) (always has more rows than dataset2) dataset2(MDX) tablix1(source is dataset1) tablix2(source is dataset2) what i need to accomplish is...

Hiding an SSRS 2008 report in Report Manager list view thru code - I'm trying to find a way to hide subreports in Report Manager. I know I can go to the report...

SQL Server 2005 : Development

how to store text file content into sql server 2005 database - Hi, i have to design an application to read all text files from a folder and store in sql server 2005. each...

Performce SP - Hi , One of SP is having below code and it is running more than 2 hrs. This job is daily...

SQL Server 2005 : SQL Server 2005 Performance Tuning

sql server 2005 (std edition 32 bits) on windows server 2003 enterprise 32 bits - Hi Guys, I have 8GB RAM on windows server 2003 enterprise 32 bits. From my computer, right click properties I can...

Can Someone Explain These Wait Times to Me? - So I know what wait times are, the amount of time SQL spends waiting on a resource. What I don't...

High Index Count/Space versus High CPU and Logical/Physical Reads - We are working on trying to reduce the high number of IO waits on one of our production servers, a...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS & Environment Variables - I've been struggling w/ some quirky issues lately regarding use of environment variables with package configurations in my SSIS packages. When...

SQL Server 2005 : T-SQL (SS2K5)

CASE in WHERE clause - I am converting embedded SQL (in Java code) to a stored procedure. They are building SQL WHERE clause dynamically in JAVA. What...

Converting Hex String to Int - When converting a constant hex string '09FF000' to INT. It is simple as below Select Convert(Int, 0x09FF000) --just added 0x However when...

SQL Server 7,2000 : General

SQL 2000 - The selected package cannot be opened - The DTS Designer has been closed - I have two SQL 2000 servers, Db1A and Db1B. There has been NO SQL 2005/2008 is or has ever been...

SQL Server 7,2000 : T-SQL

Merging two select statements for MTD and YTD - Hi Guys, i have two select statements that i want to merge in one with union. the first query should return YTDSALES...

Inserting TAB into string - Hi all, This is for SQL 2000. I am trying to add a tab character to a string using char(9) but...

SQL Server 2008 : SQL Server 2008 - General

Possible to recover / retrieve a server-side trace definition? - I'm trying to find what is being traced from a server-side trace on a couple SQL servers I'm managing. The...

T-SQL which cause Table or Index Scan - Trying to find a comprehensive list of T-SQl that will cause a table scan. 1 Such as Select * from xxx, ...

Not able to modify existing maintenance plan? - Hi, Tlog backup failed and no errors are written error log and application logs as well.. when try to editing existing maintenance...

Login history - Hi Friends, Let us consider I have a login ABC & I have given db_reader access for XYZ Database. But now that...

Access issue - I have got access to Production Domain but I don't have access to Development Domain means I ca not create...

Slow query - optimisation help required! - I have the following query: [code="sql"]INSERT INTO dbo.Load9_PotentialOverlaps_(Master_Id, Master_GUID, Master_SubmissionID, Duplicate_Id, duplicate_GUID, Duplicate_SubmissionID, MatchKeyType) SELECT a.id, a.GUID, a.SubmissionID, b.id, b.GUID, b.SubmissionID,...

How SQL query result insert in to table? - Hi Team, Daily am taking the size of databases using "EXEC SP_Databases" and updating in a excel file manually, [b]DATABASE_NAME | DATABASE_SIZE | REMARKS[/b] now i want...

SQL Server Templates - My own created SQL Server templates have suddenly disappeared from Template Explorer in SSMS ? Anyone know how i can find...

Linked Server Replication - Good Morning I have been struggling with data synchronization between SQL SERVER and MYSQL for ages, I have tried all possible...

Cross Join Trick - Create table Amount ( YoA int , Currency char(3), Amt decimal (9,2) ) Create table Master ( YoA int , Currency char(3), ) Insert into Amount select 2008, 'CAD' , 3400 UNION...

When delimiter is part of the field - How to handle in SSIS - I have a CSV file with field delimiter as [b]COMMA (,)[/b]. The package is working without any issues for few...

EXEC Master.dbo.xp_DirTree - hello all. I use this command for my purpose:EXEC Master.dbo.xp_DirTree 'D:\Reports ',1,1 now I want to have *.rpx file that exist in...

retrive data from path on another computer - hello all. I have som rpx file on one drive on server and I want to select rpx file name to...

invalid character in input file - Hi all, I'm being told I have to scrub invalid data in an input file. I have a 30 character...

Recommended index causing query to run twice as long / HASH vs NESTED LOOP - Hey all, Been working on some performance tuning and running into a wall at this point. I've taken some initial passes...

Incorrect syntax error when using group by - I'm trying to do a join and then a group by and order by, but I'm getting an "Incorrect syntax...

inserting data from one table to another and want to create primary key at same time - I am creating a table by using an insert into from another table then I am deleting any duplicate rows from...

Getting the files stored in a database table? - Hi All, I am struggling to find a solution / guidelines / 3rd party or internal database tools that allows me to extract...

Is this is the Best Practice to select E: for the SQL Server root directory? - Hi Experts, Is this is the Best Practice to select E: or any other driver(not C:) for the SQL Server root...

Find month totals between date columns - I have a sample view with some dates. How would you find the numbers of items open per month. Say...

Create CLUSTERED Index - Hi I want to change the order of two fields in Pk of a table,I need to drop it and recreate...

Diagnosing Page Latch Issue - I'm working with both a Vendor's DB and a purge script they've provided, and I need some outside opinions. This purge...

How to get Below T-SQL qury Output..? - [size="3"][font="Courier New"]Hi All, This is the query I have written DECLARE @FromDate DATETIME DECLARE @EndDate DATETIME SET @FromDate = '2013-01-01 00:00:00.000' SET @EndDate = '2013-02-13 00:00:00.000' SELECT year(sd.FKDAT)...

Executionlog2 table - How much time Executionlog2 table and catlog table in ReportServer database hold the data? Does it store all historical data and...

sqlcode vs sp - hi friends i have small doubt in sql plese tell me how to Determine when to use stored procedures vs....

SQL Server 2008 : T-SQL (SS2K8)

Performance Problems converting data into columns - Hello, I am in need of some help with an SSIS package (SQL 2008). I am trying to extract data...

How to make triple-pass UPDATE single-pass? - I'm attempting to optimize some code that I was just handed, and I'm not exactly sure if what I want...

Insert Performance - Hi, Which will perform faster/better inserting million rows to a table or put all insert in a transaction then commit? Does it...

Reporting on report execution over the last 12 months - Hi guys, I'm looking to create a report that will detail how many times each SSRS report was executed over the...

Converting Rows to columns - i want to convert rows to columns 2 2 0 output 2 2 0

Advanced String Manipulation - I have a string that needs some serious help... I need to rewrite many strings with the aggregate value of anything...

Very frustrating performance tuning! - So I have run into this most aggravating of issues :-D Here is what I have; I am loading sales order...

How do I optimize a query with a text column? - I am looking for a way to optimize a SELECT query that includes a column with a data type text....

Split a String - Hello Everyone I am working on some old data that should not be store this way, but it is. My data is...

How to create a query which kill CPU? - Hi For some tests I need to create a query or set of queries which overloads my test machine which is...

Data Insertion into Access DB using T-SQL - Hi Guys, I am trying to insert data from SQLServer into an access database,using T-SQL I used OPENROWSET to Select the data...

Time Problem - Hi, I'm trying to figure out a way to calculate a weighted average, but having trouble with the time part. Here are...

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

Complex SQL QUERY with DateDIFF - Hi all, I need to script a query to discard access logs that not exceeding a predetermined margin (eg. 2 seconds)....

SQL Server 2008 : SQL Server Newbies

Sum values in a select statement - I know this is a simple question. However, I'm not sure where to start. I'm new to SQL and would...

Part Table or Procedure not updating - Hi all, I'm fairly new at all this but I know a little, I've recently taken over our database from a...

supplying a schema in queries, performance? - Hi A random question, if you write queries and put the schema before objects will it affect performance in any way...

How to find database,object,schema level permissions on a server - Hi...any one can provide the solution please it's very urgent i need a tsql script for SQL SERVER 2005 Requirements: I have 2...

I am looking to gain work experience here in London UK - Please HELP!!! - I am a certificated SQL Server DBA in both 05 and 08 since 2010, but has had no joy successfully...

Microsoft Access 2010 & SQL Server 2008 Express - What's the best way for clients to connect to an sql server? Should all clients have their own seperate Access...

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

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

SQL Server 2008 : SQL Server 2008 Administration

How to find cause of Replication delay? - Hi everyone, I just have a quick question about finding the cause of a delay I saw with one of my...

SSIS Job fails using Domain Account - I get this error when I attempt to run the Package using the Domain Account, SQLServerAgent. [quote] Message Executed as user: MyDomain\SQLServerAgent. Microsoft...

Replication from SQL 2008R2 to SQL 2012 - Gurus, I have weird replication issue. Can't figure out what the issue is here. Please help me with ideas/known bugs(if...

DR/HA proposal - I need to write DR/HA proposal any suggestions?

Negative Available Space for tempdb - I'm checking all my databases and when I look at my templog setting in the Shrink File window I see...

Career : Certification

How to start Database and sever Career HELP? - Hello all I am in need of some serious help. At the age of 29 I decided to go to...

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

Career : Employers and Employees

Client does not want to pay overtime. How to deal with it ? - I am hourly-paid employee of a consulting company, but work all the time at client site. Sometimes I have to...

SQLServerCentral.com : Anything that is NOT about SQL!

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 reports logic - HI friends i have small doubt in ssrs report.plese tell me how to solve this one If i generate the report...

need help on Expression syntax - Hi all, I am trying to create the Expression equivalent of the following query: SELECT count(TasksName) FROM [db].[dbo].[testdetails] where TopLevelProjectName = '40 Project Folder' and...

Regex Expression to remove RTF tags - Hi There, I have a file that I get from pulling out values from a Microsoft Lync 2013 conversation that has...

Hide the sub reports in ssrs - i want to hide the sub reports in the tree view of reportviewer application.I can hide these through reportmanager by...

Reporting Services : Reporting Services 2005 Development

Startswith Eqivalent in Reporting Services? Need help please! - I have a Crystal Report that I am converting to Reporting Services. The report lists accounts by SIC Code, which...

Is it possible to alter the .XML in and RDL file via SQL script? - Hi Guys, We have a 3rd party developing our data warehouse, and we do the SSRS devevelopment in house via...

Subreport with uniqueidentifier or guid parameter - Hi, I am adding a subreport to a report and I need to pass a uniqueidentifier or Guid to the subreport....

Data Warehousing : Integration Services

SSIS task perform PGP encryption with remote server - Hello, I have an SSIS package with a task that I need to perform PGP encryption. I am running the package...

SSIS task taking time but SP called is quick - Hi, We have a huge process that we perform using SSIS, which is mostly just calling SP's using SQL task. When...

Conditional Lookup - Currently working on a project for a client which involves me building a matching solution in SSIS. One of the...

Passing Multiple variables to dtexec in execute process task - Hi, how to pass multiple variables to dtexec in execute process task. it is working fine when i pass one variable for...

Disappearing scriptmain.vb from a script component - Hello, I am doing some research into moving to SQL 2012 from SQL 2008 R2. As a part of this I...

Update Data in Prod table. Lookup ? Merge? - Hi, I am in need of a solution. I am supposed to load the data of a table from PROD server...

Data Warehousing : Analysis Services

Out of Memory problems creating an SSAS Tabular Model - I'm trying to design a Tabular Model but when I point the Table Import Wizard at my primary fact table,...

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