Memory Corruptions, or Why You Need DBCC CHECKDB

Paul Randal from SQLServerCentral.com

 More »

Partitioned Tables, Indexes and Execution Plans: a Cautionary Tale

Additional Articles from SimpleTalk

Table partitioning is a blessing in that it makes large tables that have varying access patterns more scalable and manageable, but it is a mixed blessing. It is important to understand the down-side before using table partitioning. More »

Day 1 of The OLAP Sprint

Simon D from SQLServerCentral.com

Day 1 of Simon's journey to build an OLAP solution in a week.  More »

From the SQLServerCentral Blogs - Cloning SQL Servers to a Test Environment

Chad Miller from SQLServerCentral.com

I’ve been involved in a project to set up a full test environment of dozens of SQL Servers. The requirements... More »

This editorial was originally published on Feb 13, 2008. It is being re-run as Steve is returning from the PASS Summit.

I have come competition here with my daily editorial. If any of you get the SQL Server Performance newsletter, you might have noticed that Peter Ward is now writing daily over there. He's a nice bloke from down under, a fellow MVP, and he has some interesting things to say. He's a bit more focused than I am and if you have the chance to hear him speak, it's worth it. He gives some nice presentations.

I noticed the other day that he was talking about how he uses ALT-X to execute SQL queries, while most other people click "Execute" or click CTRL-E. I can't help but think even more highly of Peter since I've used ALT-X for over a decade and know it's the best way to do this. :)

His discusion wasn't on executing queries, but on the fact that there are many ways to accomplish tasks in SQL Server. For example, you could audit by placing triggers on every table, capturing the inserted/deleted information, and storing it in a table. Or you could run Profiler forever and ever, making sure you have a system in place to manage those files. Or you could rewrite your code to use the OUTPUT clause. Or wait for Change Data Capture in SQL Server 2008.

All of these are valid, and there might be good reasons why you'd pick one over the others in your environment, but which one should you look to? What about many of the other possible ways of doing things in SQL Server? Should you look to rebuild or reorganize indexes by default? There are any number of tasks you need to accomplish and, often for people less experienced in one area, it seems either of a few ways is acceptable.

I certainly wouldn't want to remove options and limit a DBA to developer to one way to do things in most cases. I think the richness of SQL Server, which is always growing, allows it to scale and handle a wider variety of tasks than ever before.

However I think that often the overwhelming number of choices can lead to a paralysis of choice for some people. Or confusion about what to do or what makes sense. Present my daughter with 2 candy machines and she can pick the one she wants in an instance. Take her into a 7-11 with an aisle of candy and I'm still standing there 15 minutes later waiting on a decision if I haven't forced the issue.

I think that one area of improvement that could really help with many of the possible choices for completing a task is to have some strong guidance from Microsoft (based on customer pilot testing and the reasons for implementing a feature) that would explain what the defaults should be. And would have those defaults set up as the default choices or actions for the product.

Those of us with reasons to make changes can, and will do so. But those that don't know what to do would have more guidance right out of the box.

Steve Jones

BTW - No offense intended to the cat-people out there.

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to 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!

• Windows Media Podcast - MB WMV
• iPod Video Podcast - MB MP4
• MP3 Audio Podcast - MB

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.

Steve Jones 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:

The following code shows three methods of selecting the number (to the nearest 100) of nanoseconds since midnight. Which, if any, will work reliably?

--Method 1:
-- use datediff with NS as the datepart parameter
select DATEDIFF(NS,
  dateadd(D,datediff(D,0,sysdatetime()),0),SYSDATETIME())

--Method 2:
--use the internal structure of datetime(2)
cast(cast(reverse(substring(cast(@t as binary(9)),2,5))
   as binary(5)) as bigint)*100

--Method 3:
-- go via a character string conversion
-- to ISO 8601 with nanoseconds format
select cast (substring(convert(
   nchar(29),sysdatetime(),129),21,9) as bigint)

--Method 4:
-- do it piecemeal with several datediffs
with now(t) as (
  select SYSDATETIME() as t
  ),dy (d) as (
  select dateadd(D,datediff(D,0,now.t),0) as d from now
 ),
secs (ss) as (select datediff(S,dy.d,now.t) from now cross join dy),
secstart(start) as (select DATEADD(S,secs.ss,dy.d)from secs cross join dy)
select 1000000000 * CAST(secs.ss as bigint)+CAST(datediff(NS,secstart.start,t) as bigint)
  from secs cross join dy cross join secstart cross join now

By Tom Thomson

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

This question is worth 2 points 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.

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

What stored procedure is used to enable Database Mail in SQL Server 2012?

By Mike Hays

Answer: sp_configure

Explanation: The extended stored procedure "sp_configure" enables the Database Mail in the in the msdb database.

Ref: http://msdn.microsoft.com/en-us/library/ms191189.aspx

» Discuss this question and answer on the forums

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate. 

Smart Index Rebuild - All Databases

Tim Hollobon from SQLServerCentral.com

Rebuilds all indexes from all databases over a specified fragmentation threshold. More »

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

SQL Server 2005 : Administering

SQL Server is unable to connect to server - We recently tried upgrading our SQL servers(clustered) to new physical servers, but during the process we came across some issues...

unable to execute a store procedure - USE [TEMP] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[psp_TEMP] -- Add the parameters for the stored procedure here @MimeType varchar(50), @image image, @Hid int, @ImageName varchar(100), @ImageDesc...

Please help e!! not sure what to do..SQL Database user moved under System Databases - Hello Experts, I have a software that is running with SQL Server 2005. The software has an utility to configure replication...

Move a clustered index from one filegroup to another on a production database - I would like to move a clustered index from one filegroup to another on a production database without causing any...

Primary Key datatype Bigint vs uniqueidentifier: - Hi Friends! Can any one suggest me for each tables primary key should be Bigint as PrimaryKey or [uniqueidentifier] as...

trigger - Hi guys, I have created this trigger: [quote]create trigger TRG_CONTRIBUINTES_VERIFCA_RF_E_NIF on contribuintes for update as if (contribuintes.NUMEROPOSTO) ='CENTRAL' BEGIN if contribuintes.cod_rep_fiscal not in...

Index Rebuilds Still Leave Fragmentation - Hello All, I am running SQL Server 2005 SP2 Standard Edition on a few servers. I run SQL Agent Jobs every...

SQL Server 2005 : Business Intelligence

Why does a NULL aggregate warning in an SQL query causes the SSIS package to fail? - An SSIS Package was failing with an error message as below: Code: 0xC0202009 Source: DFT Populate ImageSummary OLE_SRC ProductImage [1]...

SQL Server 2005 : Development

Date Issue - I have a problem with dates Problem: There is a master table which defines the date based on range as follows [sup] [b][From][/b] [b][To] [/b] [b][ResultDate][/b] '1st...

How to copy production DB into New Developmant Database? - Hi, We have Sql server 2008 R2 and i would like to set up my new test server and copy production...

SQL Server 2005 : SQL Server 2005 General Discussion

Is it "better" to specify the definition of a temporary table, or just define it on the fly? - The subject line says it all, but just to make it clearer... I could use a [b]CREATE TABLE #TempTable[/b] to define...

SQL Server 2005 : SS2K5 Replication

Merge Replication showing connection error in Event Viewer every 60 seconds - I have a merge replication with one publisher and one subscriber. I checked the event viewer this morning and saw...

SQL Server 2005 : SQL Server 2005 Integration Services

data exists - hi ia m getting data from 2 excel file ,that i need to merge and then load into destination table. but before...

Dynamic:How to insert tablename (SSIS) - Hi Guys, I want to perform a task. I want to insert the table name of the destination tables to the another...

Executing multiple instances of SSIS package in parallel - What exactly I want to implement is like this - I have a file watcher which kicks the SSIS packages when...

SQL Server 2005 : T-SQL (SS2K5)

What's the best way to pass parameter into a dynamic sql in my case? Thanks. - My final dynamic @sql would be: Select DocID, Code, Ref, Title, DocType, FileName, InitialSignOffDate, LastReviewDate, NextReviewDate, Due = Replace(@Due, 'in', 'Within'), DueValue,...

How to filter if data was not there - I have a query (shown below) that I have been asked to filter so that only stock activity that has...

Dealing with Daylight Savings Time - Hi all, I'm having an issue regarding dates that are stored in one format vs another, as far as Daylight Savings...

SQL Server 7,2000 : Administration

Session CPU Wait times - Hi all, We have a lightly-used OLTP system, split across 3 clusters, that is showing 6 sessions with wait times...

Id of most recent login? - Hi all, I have an orphaned .mdf + .ldf fileset on our production box, I know how to attach them. But I also...

SQL Server 7,2000 : T-SQL

Group by with MAX then other columns - how to control what is returned if there are multiple results - Hi all, I know this is a 'normal' issue that I see littered about the forums alot, trying to get unique...

SQL Server 2008 : SQL Server 2008 - General

How to locate sub-packages dynamically? - Hi there, I have a master package calling sub-packages. All my packages are deployed in one folder on the file system. How can...

SP_SEND_DBMAIL @QUERY Inserts Line Breaks Into E-mail - I use the @query perimeter in sp_send_dbmail to send out an e-mail with a list of warnings (the warnings are...

Backing up SSAS Database; Insufficient space - Does anyone know if SQL Server takes into account the size of the backup file it is overwriting in the...

differential backup question - Hi Guys, For example only. I have a daily full db backup via SQL maintenance plan to file system at 10pm. I also...

Question -Find rows where only some fields are duplicate- - Ok I got another fun one I got a table Site, which contains sites (Duh) [code="SQL"] ([Sell-to Customer No_] varchar(20), [Name] varchar(50), [Name 2]...

Rename SQL Server 2008 R2 Cluster Instance Name - I have a Sql 2008 cluster with SQL network name as bbmnet but the cluster name is bbmnetsql , now the...

Recursive algorithm for category - Hi all, I have a table Category(CategoryID int,ParentCategoryID int, Name nvarchar(1000)). [code="other"] CategoryID ParentCategoryID Name 1 NULL A 2 1 B 3 1 C 4 2 D 5...

Emergency state - My database not accepting any alter command to bring it back online.

MS SQL Server 2008 R2: SQL Native Client error - Hi I'm getting this error when trying to the sqlcmd: command against the instance ... MS SQL 2008 R2 (10.50.2806.0) SP1...

How to replace the database hardcoding? - Hi friends I have database 'A' in my Prod and Testing environments. Now I have created a new database 'B'...

Database Log File Full - Emergency Work-Around Script - I'm doing my best to recreate a script I read written by Gila Monster (Gail Shaw) when she was recently...

Merge with duplicate rows - Hello all, I am running merge stored procedures against various staging and final tables in my database. I have one...

Can Execute Procedure But Job That Runs Procedure Errors? - I have a procedure that pulls data from my database and uses MSDB.DBO.sp_send_dbmail to send out e-mails. The procedure itself...

script uses INT variable as if one lower than set value - This should delete all records from these tables where the archiveID is greater than 14, but it is taking out...

delete top 1000 rows from table - Hello I need to delete 1000 rows from table and it should do it batch wise. means i need to loop delete statement...

Having a SQL 2008 R2 Database in Memory - Hi All, I have looked around and not found enough to answer my questions. And under some pressure to review this...

Time out - Hi, I'm doing bulk insert which is taking 36sec approx. can anyone tell me how to execute using asp.net c#. normal...

Updating 566787 Records takes forever - Hi, I am writing a script to update bad data in a column of a table. The Table has 566787 rows...

Best practices to create databases for different applications - Hi, We are in a process of developing 4 different applications and they are all not related. All 4 application developers created...

SQL Server 2008 : T-SQL (SS2K8)

SQL Express - Create maintenance plan to run truncate table - Hi - I am new to SQL express, can someone please guide me to how I can schedule a task to...

Stored Proc with XML input param to create Dynamic SQL: Is there a better way? - Hi Guys, I have a requirement to generate record sets which are built using a number of different filters. The filters...

Is this a bug ??? - why is the difference between both outputs ... --SQL1: Declare @tab table (seq int , k varchar(20)) declare @comma_Delimited varchar(max) set @comma_delimited='' insert into @tab select...

Query Help - I'm testing on some part of the application . My Table structure is like [code="other"]create table test (id char (8)) [/code] I want...

Reg: Interview Questions - I face one interview question.One salary table i have three column like (id,name,salary) Table Name:SalaryDetail id name salary 1 arun 1000 2 jothi 2000 3...

Exclude Records using join - Hi friends How to Exclude Records using join . I usually do not in Example: I have a Table A and...

Pipes at the End - Hello Everyone I am loading some data, not clean and not from another SQL source. So there are all kinds of...

TSQL to check database backup status - Hello, I am trying to generate a monthly report for sql database backup job. Is there a script that I can...

CONVERT BEHAVIOUR - hi, when I ran query- SELECT CONVERT(FLOAT,'-') It gives me an error- Error converting data type varchar to float. While when I ran...

Check and transfer data between two databases in same server using complex conditions - Source DB1.TB1 [ ID, EAN, ASIN, category, NAME, CONDITION] 1, null, 20001, ps2, COD2, new 2, 1002, 20002, xbox, HOLA, new ...

SQL Server 2008 : SQL Server Newbies

Understanding Memory Clerks - Hello All When investigating memory using sys.dm_os_memory_clerks, why is it that CACHESTORE_SQLCP is always among the top consumers? I would imagine that...

Connecting to SQL Express via SQLCMD errors - Hi all, I cant connect to a sql express instance via sqlcmd and was hoping for some ideas! Command line:...

SQL Database moved under System Databases by mistake - I have a sw wich has a tool to configure replication between my 2 dbs I have. When the tool...

Modifying a SQL 2000 maintenance plan -- and shrinking - I've got a SQL 2000 DB with a plan that was created long ago. On the Optimizations tab it's got...

Using SUM(1) to count Rows (vs Count(1)) - I have run into an instance where a developer was using SUM(1) to count rows in a table rather than...

Sum sales for current month - Hi All, I'm trying to sum the sales where the date parameter will automatically reference the current day/ month, which I...

Backup plan help required - Good afternoon, Please consider the following: [img]http://s17.postimage.org/ggq5fn4xr/Backup_Plan.jpg[/img] I have 18 servers at two different sites. The number of DBs on each server varies...

Using functions on remote/Linked server - I have a linked server that I need to run stored procs that contains functions in the query on the...

SQL Server 2008 : SQL Server 2008 High Availability

Automation scripts - i need to configure mirroring for multiple db's can any one send me the automation scripts to configure mirroring for...

ReBuilt Index - I wanna confirm that some user are manually executing below query on each databases one by one This may be the...

SQL Server 2008 : SQL Server 2008 Administration

Wait Types concern - Hi Experts, In one of our DW servers the performance is very slow that a simple select statment is taking hours....

User SP on Master DB - Hi Experts, Is there any problem if developers create user SP on master DB??

Job details - Hi Experts, How can i find the job name from the program_name value= SQLAgent - TSQL JobStep (Job 0xD498185D4A27E947ABE673A5CD93B09B : Step 1) in...

The row was not found at the Subscriber in replication - The row was not found at the Subscriber when applying the replicated command. Please suggest me

How To Spool Into A Script File In SQL Server - How To Spool Into A Script File In SQL Server using sqlcmd Thanks in advance

Create an alias of sql server - In SQL server 2008 R2 configuration manager, I would like to setup alias of the server. This is for use a...

I/O requests taking longer than 15 seconds to complete - This is a Win2008R2 SQL2008R2 SP2 active/passive cluster. I am getting these messages when I run an UPDATE STATS on a...

Scalability of SQL Query - Hi, Can you help me understand how I can check the scalability of an SQL query. - Nidhi

Blocked Queries - Hello All I'm trying to find the cause of blocking on my system I've found the following script to check for...

SQL Server Agent Powershell Subsystem with only 2 worker threads - My Sql Server Agent can only run two powershell jobsteps at one time which apparently is caused by the max_worker_threads...

Efficient Data Deletion Method and Transactional Databases - What is the efficient method/T-SQL that I can use to delete data from my SQL Server 2008 R2 database. Currently I...

Career : Certification

MCM lab exam - how to register? - I'm going through the Prometric site (UK) and it does not appear in the list of available exams. 088-970 and 088-974. Any...

SQL Server reporting services - Hi, I have created some reports and deploy on report server.I want that every user on LAN can access reports. When I...

Career : Employers and Employees

I have a dilemma - Hi everyone, I have a dilemma and need your advice. We have a customer that runs a report that retrieves very...

Programming : Powershell

powershell WMI error: "Could not obtain SQL Server Service information. An attempt to connect to WMI ... failed with the following error: access is denied." - I get the following error when I launch powershell from SSMS, after right-clicking on a remote SQL instance and choosing...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

Not enough work? - Just curious, has anyone ever had to quit a job because they just can't keep you busy?:doze:

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

SUM(ReportItems!Textbox.Value) - Hi geniuses! I'm trying to SUM all the values from a textbox (txt.abc), which is in a group (details.abc), which resides...

Extra Scrollbars - Hi all, We have recently been upgraded to Windows 7 and IE8. Previously we were on XP and IE7. Our reports (use...

Expose Parameters Value selected - Hi geniuses, I want to show which parameters the user as chosen to run the report. For a Non Multivalue parameter it...

SSRS report passes null value please help - Hello, I am totally new to SSRS and getting an error for more than a week and cant figure out...

View report of reporting service give different result between visual studio and IE - Hi Experts, After deploying the reports that developed using reporting service, the result of the view report in the internet explorer...

Show/Hide Report Parameters based on selected values in different parameter - Is it possible to Show/Hide Report Parameters based on selected values in different parameter? plz help me...!

Timing out when exporting to excel - I am trying to export a report that is returning over 65000 records to excel. I have created a grouping...

Reporting Services : Reporting Services 2005 Development

How to Create Dashboard using SSRS 2005 Reports.?? - Hi All, After all attempt I finally Suceeded to deploy SSRS reports outside Local Host...Next thing I am trying to create...

Data Warehousing : Integration Services

Send mail task does not execute when scheduled - Send mail task works when executed from Visual Studio but the SSIS package when scheduled does not send email.

Single vs Multiple Data Flows - I have used SSIS a bit in the past (2005), but I am embarking on a new BI project and...

Data Warehousing : Strategies and Ideas

How to Relate a Snowflake Dimension to the primary Dimension - I have a dimension table with names of individuals and several demographics associated with the names. Each name can have...

Data Warehousing : Analysis Services

applying filter in MDX - Hi, i have one MDX query which i am using for reporting, i have around 30 different fields which have different...

nable to build SQL Server OLAP Cube when there is no rows corresponding to foreign key in a table - I am new to SQL Server OLAP Cubes. I am having the following issue like ex I have purchase order and...

Good MDX tutorial ? - Hi, Could anybody recommend good MDX tutorial ? Thanks.