In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro SQL Backup Pro wins Gold Community Choice Award
Find out why the SQL Server Community voted SQL Backup Pro 'Best Backup and Recovery Product 2012'. Get faster, smaller, fully verified backups. Download a free trial now.
 
Red Gate Deployment Manager NEW! The easiest way to deploy .NET code
Deploy ASP.NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL DBA Bundle Top 5 hard-earned lessons of a DBA
In part one, read about ‘The Case of the Missing Index’ and learn from the experience of The DBA Team. Read now.

In This Issue

Single Quotation Marks in SQL

One of the common problems is dealing with apostrophes in T-SQL. This article examines the challenges of single quotation marks and ends with a short quiz. More »


Partitioning in SQL Server

Partitioning has improved with each new version of SQL Server. From partitioned views in SQL Server 7.0 through partition table parallelism in SQL Server 2008. With SQL Server 2012, we are now allowed to even create up to a 15K partition on a single table. More »


Free eBook: Inside the SQL Server Query Optimizer

This free ebook from Red Gate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. More »


From the SQLServerCentral Blogs - Vote for my SQL Bits sessions

“Rob? Tsk tsk tsk. That’s a naughty word. We never rob. We just sort of borrow a bit from those... More »


From the SQLServerCentral Blogs - SQLBits XI: Need Your Vote

SQLBits XI: Need Your Vote SQL Bits XI SQLBits XI is in Nottingham, U.K., May 2 – 4, 2013, and I am attending.... More »


Editorial - All Flash

I was reading a piece recently that was titled: Switch Your Databases to Flash Storage Now or You're Doing It Wrong. It's a provocative piece, one that has lots of strong comments about the author's style and claims. It's from the CTO of Aerospike, a NoSQL database, and it tries to compare the bottleneck of disks to memory to the network, with the conclusion that your money is better spent on faster SSD drives than tons of RAM.

I don't know if that's true for NoSQL databases, many of which want to run in memory, but I do think there's something here for SQL Server installations. Many of us do have smaller databases, which are much smaller than 1TB. At those sizes, it does make sense these days to look at an SSD solution for your database. At least for tempdb, now that the MTBF of many SSD vendors is large enough to handle the load. Even if you had to replace a tempdb SSD every year, I'd think the performance improvement would make the investment worthwhile.

Technology is changing for data professionals, especially for SQL Server administrators and developers. We are seeing the platform grown and expand, and the changes in what SQL Server allows us to do, and the changes in cost, mean that we should re-evaluate how we approach our systems. Hardware constantly improves, and some of these changes can be a better short term investment than spending a tremendous amount of development and testing time to improve things.

In the long run learning to write better code, and choosing better indexes are great solutions, but they'll work well with faster storage, which might buy you the time to improve your skills.

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


The Voice of the DBA Podcasts

No podcast today due to the holiday schedule, but they will resume tomorrow.

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


Question of the Day

Today's Question:

If you run the following code:


declare	@NewYearsEve datetime;
declare	@NewYearsDay datetime;

set @NewYearsEve = {ts '2012-12-31 23:59:59.997'};
set @NewYearsDay = {d '2013-01-01'};

select @NewYearsDay as NewYearsDay, 
       @NewYearsEve as NewYearsEve,
       dateadd(year, datediff(year,@NewYearsEve, @NewYearsDay),
        dateadd(month,datediff(month,@NewYearsEve, @NewYearsDay),
         dateadd(day,datediff(day,@NewYearsEve, @NewYearsDay),
          dateadd(hour,datediff(hour,@NewYearsEve, @NewYearsDay),
           dateadd(minute,datediff(minute,@NewYearsEve, @NewYearsDay),
             dateadd(second,datediff(second,@NewYearsEve, @NewYearsDay), 
	     @NewYearsEve
	    )
	   )
	  )
	 )
	)
       )as ToTheNewYear;

What is returned as ToTheNewYear?

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

This question is worth 1 point in this category: Datetime Functions. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

Microsoft SQL Server 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.


Yesterday's Question of the Day

What will be returned by the following statements?

select ((CONVERT(NUMERIC(5,0),123) / CONVERT(NUMERIC(5,0),100)) * 100) AS [Percentage]
select ROUND(((CONVERT(Float,123) / CONVERT(Float,100)) * 100),1) AS [Percentag]

Answer: 123.000000, 123

Explanation: The conversions take place based on the parameters passed into the CONVERT function.

Ref: Cast and convert - http://msdn.microsoft.com/en-us/library/ms187928.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

Find failed SQL Jobs

Simple T-SQL to find failed SQL Agent jobs 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

Enabling Remote DAC - Hi Seen lots of stuff about how to enable remote DAC, but I can't find anything regarding the implications of enabling. Are...

Stange memory and page file behaviour - Hi, I wonder if anyone can shed some light on a problem I experienced the other day. My company operates a...

Issue installing SQL Server 2005 - Hi. I am having a problem installing SQL SERVER 2005 on my windows 7 enterprise 32 bit operating system. The installation...

SQL Server 2005 : Backups

Backup Log Incorrect Function Message (nonrecoverable I/O error). - We started receiving this message mid-last week: [b]Msg 3271, Level 16, State 1, Line 4 A nonrecoverable I/O error occurred on file...

SQL Server 2005 : Business Intelligence

How to Evaluate Performance Point 2010 - I would like to do some Proof of Concept Dashboards/Reports for my company using Performance point 2010. Do i need...

How to set the condition based on Data flow task? - Hi Friends, I have a scenario that i am loading data into a table. After that i am getting the...

SSRS Custom Code Functions - SSRS2008R2 Upgrade - Hi All,

SSRS - Calculate $ cost on monthly basis - I am new to SSRS and stuck at a point and can not move further and so need your help I...

Database Diagrams tool in SQLSERVER - Is there any Database Diagrams tool in SQLSERVER ? Hello : We are planning to design new Datawarehousing product. I am taking...

SQL Server 2005 : Development

Trying to convert the result set into HTML format but have problem with XML column - Hello everyone, Being new to XML i have stuck in the middle of no where while converting the result set of...

SQL Server 2005 : SQL Server 2005 General Discussion

Job Activity Monitor view - but where are the In Progress jobs? - Hello all, I wrote a view that joins the sysjob, sysjobactivity, sysjobhistory and syscategories tables together to give me a resultset...

SQL Server 2005 : SQL Server 2005 Security

Msg 7416, Level 16, State 2, Line 1 - I have created a linked server using Mgmt Studio (so no scripting to insert). I have included a "Script Linked Server...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Best way to get execution plans? - Hello, I have a database used for reports that has a bunch of scalar functions on it that generate assorted record...

SQL Server 2005 : SQL Server 2005 Integration Services

Issue with script component when used as destination - Hi all, Need help in solving an issue. i have a SSIS pkg deployed in INT & Dev env respectively. The data flow...

Possible to determine the datetime range of loaded data to staging table? - Hi all, I have created a SSIS package. In the control flow i have a Execute SQL task which will truncate...

SQL Server 2005 : T-SQL (SS2K5)

shrinking database - hi all, i want to know that ,can we shrink the database at the time of working on that database. if...

sp_send_dbmail is not giving proper formate in txt attachement of SQL query output - Hi , I have following code to execute. i want to have functionality to send email for the following query output...

SQL Server 2005 : SQL Server Newbies

Initialisation file - Hi, I'm totally new to SQL Server *.sql command files. I'd like to create a SQL file that runs the initialisation...

SQL Server 7,2000 : Data Corruption

Logical consistency error - Hi, Often am getting logical consistency error in one my production server. Recently i did DBCC with allow dataloss option, to...

SQL Server 7,2000 : General

Automate DTS Column Mapping during import - I created a DTS package that imports a flat file with 10 columns.... on occasion some of the other files...

SQL Server 7,2000 : SQL Server Newbies

Analyst seeking wisdoms while learning the platform - Im a business/financial/data analyst who wishes to make a transition to a BI solutions development/BI information delivery role specializing in...

SQL Server 7,2000 : Service Packs

Installing Hotfix - Hi. Im with a SQL Server 8.00.2066 (SP4) 32 bits. it run on Windows Server 2003 32 bits (SP2). I have...

SQL Server 7,2000 : SQL Server Agent

SQLServerJob failing - I have a job that executes fine most of the time but sometimes it fails with the error below. The...

SQL Server 2008 : SQL Server 2008 - General

How to automatically script-out all objects in database ? - Hi all, I need to automatically script out all objects from a databases similarly like in SSMS context menu in <Database>/Tasks/Generate...

convert -ve int to +ve bigint - Hi, How do you convert the -ve int value to +ve bigint value. I have used cast and convert which didn't help. Example. declare...

CmdExec problem - I have a job that has 4 steps. The first step runs the SSIS pkg. The second step calls a...

Export to CSV? - I have a script that writes data to a temp table. I need to automatically export this to a csv...

Setting Snapshot Isolation Level - Do i need to explicitly say SET TRANSACTION ISOLATION LEVEL SNAPSHOT; in each batch that i want to operate under...

divided by zero error - Hello, I am reviving this error when trying to run a query (48 row(s) affected) Dec 25 2011 3:30AM Jan 1 2012 3:30AM (5 row(s)...

CONVERSION DATATYPE - ERROR:The data types varchar and bit are incompatible in the add operator. CAN ANY ONE KNOW WHAT TYPE OF ERROR...

calling stored procedures of mysql from sql server - when mapping the columns from sql server to mysql iam getting the following error in oledb command iam unable to...

Import Text file into SQL....? - Hi Team, HAPPY NEW YEAR. -- I have a table with below structure. [b]Create table temp_ttt ( col1 varchar(100), col2 Decimal(9,2), col3 Decimal(9,2) )[/b] i want to insert data from...

Split based on the input - hi how to split the string based on the input say starting is 1 and next is also 1 the difference...

Error Conversion failed when converting the varchar value 'SELECT in sql server 2008 - Hi, I have the following stored proc written using dynamic query. i am appending an integer variable to the dynaic...

SQL Newbie with some (hopefully) simple SSIS questions. - Greetings. I hope someone can point me in the right direction as I'm new to SQL in general and SSIS...

Export wizard failed? - Hi, pl. suggestion me, what could be on issues during export to another new database by export wizard? how to resolve...

Upgrading to 2012? - Is it worth upgrading from SQL Server 2008 R2 Developer to 2012 Developer? I believe most companies are still using SQL...

SSIS loading only 5 of 42 fields to SQL - Greetings all. I am learning SSIS and having great fun with Sequence Containers, Control flows and Data flows. I believe I...

Not null Foreign key self-referencing identity column - Hi all, I'm wondering if there's a way to do the following: IF OBJECT_ID('test') IS NOT NULL DROP TABLE test GO CREATE TABLE test (nId INTEGER IDENTITY...

Keeping all three environment(Dev,Stag,Prod) in sync - In our office we run jobs to keep these environment in sync. We get data from outside source and run...

Dynamically adding the schema name or populating the schema name - We have a script that we run on various different databases of customers. so for example it may have a line...

Len did not display right size - I use len function to find out varchar column data size but did not display right size. For example, in some...

2008 Central Management Server - Schedule Multi Server Query? - Hi Everyone - I have a 2008 Central Management Server that we're able to run queries from against all our SQL...

SQL Server 2008 : T-SQL (SS2K8)

Using xp_readmail to read mail from outer environment - HI Is there any way to read the (Inbox) mails from exchange servers(outside the sql server)? my first requirement is...

How to calculate percentage from two queries? - HI, I have two queries, Query 1 - is the total result equal to 80 the Query -2 is a subtotal with...

Turning raw data into a grid layout - Hi all, not sure how best to explain what I'm trying to do so will try to break it down as...

Remove duplicate records? - HI, How to find duplicate records as below query [code="sql"] Select A.SS_ID,A.SD_ID, A.SD_Info, A.SD_User, A.SD_Date from dbo.BC_ShiftSummaryInfo A Join dbo.BC_ShiftSummary B On A.SS_ID = B.SS_ID Where B.ShiftStartDate...

Query for delete records between date range? - Hi, Need Query for delete records between date range, I want delete --(3377 row(s) as below two tables [code="sql"]Select A.SS_ID,A.SD_ID, A.SD_Info, A.SD_User,...

Sentiment Analysis - I want to do sentiment analysis, I need to know how I can achieve this using SQL Server?

CTE with multiple tables - I've seen lots of examples of CTE's on line using a single table.......my situation I need to recurse thru 2...

how to fetch records from multiple tables - i have Demo,elections and electionshist tables where i should neglect the pepole who have status='t' from demo table and for...

i want to add column in exiting view - Hi i am trying to use design mode. but it is throwing error . is there other wayt to add column in...

sp_OAGetProperty @obj, 'responseText', @response OUT cannot handle large output - Hi All, This is quite weird, so thought you can have some ideas, please: I use HTTP POST to send and XML...

Time - Adding minutes and seconds - I have the 2 columns with time datatypes. select top 5 [Connect Time2],[totalTime2] from dbo.verizonDetails Connect Time2 totalTime2 08:05:44.0000000 00:13:00.0000000 08:05:57.0000000 00:01:00.0000000 09:07:42.0000000 00:03:00.0000000 09:07:46.0000000 00:09:00.0000000 09:08:08.0000000 00:01:00.0000000 I want to add the...

SQL Server 2008 : Working with Oracle

Oracle connectivity issue with SSIS - Hi All, I have SQL Server 2008 R2 installed on Windows server 2008 R2. I have a requirement to fetch the...

bcp queryout blob data from oracle - hi @all, i've got a problem and feel a little out of ideas: i'va a website where i upload attachments via html...

SQL Server 2008 : SQL Server Newbies

Transaction Log file Deletion or movement? - Hello All, I have a Query Please help me out about this: Scenerio is, I have a database server which have a...

Dev Licensing questions!? - hi all, trying to get my head around licensing. specifically in a dev environment. Could i get some feedback on...

Database Monitoring tool - Hi, Do anyone knows what is the best tool for database monitoring. Basically I need to monitor more than 50 database...

Taking a vertical list and transposing it to horizontal and then grouping - Hello, Is this possible? I have several columns, such as: Coulmn A red green yellow black Column B shirt jumper blouse coat Column C for women for men for girls extra large I have permutated the columns in...

Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34...

DBCC Commands - Hi All, This is Rangaraja.im fresher. Can anyone please explain mostly used in real time DBCC Commands? Regards, Rangaraja rangarajasqldba@gmail.com

convert month name to month number - Hi All How should i convert the month name to month number like 'April' it should converted to 4 'January' it should converted...

SQL Server 2008 : Security (SS2K8)

How can i apply Deny Permission regarding sys.database and sys.tables etc to users ? - I need urgent help regarding permissions: [b]Restrict the the user for viewing,updating,inserting,selection and creation of tables ,stored procedures,views ? Restrict user to...

SQL Server 2008 : SQL Server 2008 High Availability

How can i resolve undistributed commands in Transaction replication - Hi, We are reconfigured publication and subscription but undistributed commands to subscriber is showing very huge No command to be distributed...

Log Shipping exe location - Hi All Environment: 2 Node Cluster, Win2008 Ent, SQL 2008 Sp3 Ent Log Shipping was always working fine, even after failover between...

Snapshot Backups - The company which i work for have just introduced Veeam backup and replication as the method of backing up the...

SQL Server 2008 : SQL Server 2008 Administration

Slow Transaction Log Deletes? - I have a sever that has around 230 databases ranging from 1 gig up to 300+ gigs. Running SQL 2008...

Snapshots Fail - Every now and then snapshots fail to be created. The following query: CREATE DATABASE Admin_SnapShot ON (Name = [Admin], FILENAME ='E:\SQLData\Admin_SnapShot.ss') AS...

Index optimization failed for User databases - Hi , Index optimization job is failing with the below error :: Message Executed as user: XXXXXXXXX. Incorrect syntax near '-'. [SQLSTATE 42000]...

Reuilding the index. - I am a newbie.I am learning SQL Server through the articles present on internet. I am a bit confused regarding...

Internal tables, inserted and deleted tables, change tracking - I understand that change tracking writes changes to "internal tables." Are these in the database that the change tracking is...

Growth in tempdb and more RAM - In general, would adding more memory reduce the growth of the tempdb during general operations of SQL Server?

Could nested loops cause the growth in tempdb? - I believe hash joins can cause growth in the tempdb if there is insufficient memory. I don't think nested loops...

Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch - Hi, Just run SQLIO on my new Cluster and got very interesting results that I would like to share and/or get...

Faster way to release the Unused Space back to Disk - Hi, I have few databases that have 8 data files allocated with 300 GB each and used only 110 - 120...

Career : Certification

Querying Microsoft SQL Server 2012 Training Kit (Exam 70-461) publishing delay - I've been waiting patiently for this training kit coming out for months now, expectantly waiting for it's arrival end of...

Career : Employers and Employees

Money vs Job Satisfaction, where is the balance? - Im currently in a job i really enjoy. As a consultant for a small specialized database consultancy firm I get...

Programming : Powershell

Query local SSMS server group with Powershell? - This may be a totally off the wall question, but it seems like it should be possible to do. Just...

SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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

DASHBOARDS with Reporting Services - Hi to all. My question is HOW TO CREATE a DASHBOARD with REPORTING SERVICES 2008 r2 ??? Have anyone any suggest about...

Reporting Services : Reporting Services 2005 Development

Unable to connect to datasource pointing to Dev environment - Hi All, I have a strange issue with SSRS 2005 , The issue is the report works fine from BIDS , but not...

MultiValue parameter, select all no data - I have an SSRS 2005 report (DB = SQL 2005), running on SSRS 2008 report server. There are total 6 datasets...

Database Design : Design Ideas and Questions

Optimal Database Filesize in Filegroup - Hi there, I was wondering if there is experience one where about the optimal filesize for databases (2012) lies. I am creating...

Database Design : Hardware

Storage Subsystems and RAMDisks - We're setting up a new database server for our OLTP and smaller website databases, and disk performance is a big...

Data Warehousing : Integration Services

Run 2012 SSIS package on SQL Server 2005 - Hello all, We work alot with mysql and will only be doing more, although sql server will remain prevalent. We...

SSIS2005: DataFlow Task from OLE DB Source to Excel File Destination - Hi guys, Once again it looks like Excel is the poor relation in SSIS. I am trying to create a DataFlow task...

SQL 2008 SSIS package Conversion error - Hi I have a package loading data from a flat file to a table. At about 90,000 rows one of the...

Data Warehousing : Analysis Services

Hierarchies in Tabular SSAS 2012 - I created a tabular model of Adventure Works DW 2012. I created a Date Hierarchy in dimDate for Calendar Year...

MDX Query - Anybody knows how to put in a table of sql server the result of a MDX Query? So, I write a...

Hierarchy from two different dimensions - Is it possible to have a hierarchy that contains attributes from two different dimensions?