SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Test Coverage

I've never had to work full time in a QA group, but I have had to perform software testing of an application and it wasn't fun. Even as I worked through the various features, looking for edge cases, common mistakes, etc., I realized that running tests was something that really required more discipline and process than I was giving it at the time. Too often I 'd realize that my regressions weren't 100% duplicates of previous executions because I'd allowed too much "human effort" into my process.

These days most testing of software is automated. I know QA groups still exist, and they need to handle some of the manual checks that are very hard to automate. However more and more testing is being pushed back onto developers to handle, with frameworks like NUnit and JUnit. In the database world, we haven't done a great job of including testing into the code we write, but there's a great testing framework we can use.

TSQLT is a framework written by Sebastian Meine and Dennis Lloyd and it's free. It's been developed to help you write tests that can exercise your T-SQL code to determine if it's doing what you expect. I've used it a little, and while I see the potential, I also realize that this will take some practice to learn how to more easily write tests that can cover the various potential places where bugs can be introduced.

However, if you use version control, and you should, then you can easily spread the load of writing tests to all of your developers. Since the tests are stored procedures, they can be included in your VCS project and shared by all your developers. With a little effort from each member of your team, you might be surprised at the code coverage you can achieve with this framework.

Testing is important, as we see over and over again as software is released and doesn't work as expected. I think much of that is our fault, as software developers, for not improving our testing skills and discipline.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

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.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 20.9MB) feed

MP4 iPod Video ( 24.8MB) feed

MP3 Audio ( 4.9MB) feed

Feeds are available at iTunes and Mevio

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


ADVERTISEMENT

New! SQL Monitor Hosted

Start monitoring your SQL Servers in under 5 minutes. Get clear insights into server performance, whilst we manage the monitoring software. Find out more.


ADVERTISEMENT

If you want more automated ways to easily share tests between developers and execute them, take a look at SQL Source Control and SQL Test from Red Gate Software.

ADVERTISEMENT
SQL DBA Bundle

Save 45% on our top SQL Server database administration tools.

Together they make up the SQL DBA Bundle, which supports your core tasks and helps your day run smoothly. Download a free trial now.

SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

Featured Contents

 

Outer Join – What am I missing (SQL Spackle)

Patrick Cahill from SQLServerCentral.com

This article shows how to find missing rows in a table using a outer join. More »


 

SQL in the City Seminar Portland 2013 –Deployment Stairway

Press Release from SQL in the City

Join Red Gate for a free seminar on November 15 (the day before SQL Saturday Oregon). Steve Jones and Grant Fritchey, SQL Server MVPs, will present best practices for SQL Server version control, continuous integration and deployment, in addition to showing Red Gate tools in action. More »


 

Using the SQL Server Distributed Replay feature

Additional Articles from MSSQLTips.com

This tip will guide you through a simple example to illustrate how you can use the SQL Server Distributed Replay feature to replay a simple SQL Server trace file that contained two sessions executing statements concurrently. More »


 

From the SQLServerCentral Blogs - Lock Pages in Memory in SQL Server on VMware – Why or Why Not

kleegeek from SQLServerCentral Blogs

Two weeks ago I presented my session entitled “Squeezing Top Performance from Your Virtualized SQL Server” at the SQL PASS... More »


 

From the SQLServerCentral Blogs - More Azure Goodies

Grant Fritchey from SQLServerCentral Blogs

Microsoft keeps sneaking little things under the door for Windows Azure SQL Database. This time it’s a couple of new... More »

Question of the Day

Today's Question (by Mike Hays):

In SQL Server 2008 and beyond, automatic page repair is supported by database mirroring and by AlwaysOn Availability Groups.  Which three page types can the page repair process not automatically repair?

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: High Availability (HA).

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

ADVERTISEMENT

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.

Yesterday's Question of the Day

Yesterday's Question (by Amit Raut):

What are the analytical functions available in SQL Server 2005? (Select 4)

Answer:

  • ROW_NUMBER()
  • NTILE()
  • DENSE_RANK()
  • RANK()

Explanation:

ROW_NUMBER(), NTILE(), DENSE_RANK()  and RANK() are the only analytical functions available in SQL 2005.

Ref: http://sqlmag.com/t-sql/ranking-functions

The rest are introduced in SQL 2012 

Ref: http://blog.sqlauthority.com/2011/11/23/sql-server-2012-summary-of-all-the-analytic-functions-msdn-and-sqlauthority/


» Discuss this question and answer on the forums

Featured Script

Script All Logins / Users / and Roles

Greg Ryan from SQLServerCentral.com

Running this Script will create a script which recreates all the Logins and adds them to the Server Roles, and all the users for each Database and adding them to the database roles.

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 2014 : Development - SQL Server 2014

Poor query plan generated for SQL Server 2014 CTP2 clustered columnstore index - I posted this on Microsoft's SQL Server forum but I want to post it here as well to see if...

Subqueries - 1. I need to find the names of the customers who have purchased academic books. (Coding required as Subquery NOT...

Cursor to Delete data - Hey Guys, i have to delete data from a table which is older than 2 weeks, how can i use a...


SQL Server 2012 : SQL 2012 - General

Combining variable values with result set - I have a bunch of variable values that i need to combine with the result set of dynamic sql and...

Index tuning - Hey all, I am doing an evaluation against all indices on my database, and I have a question regarding index maintenance....

SS 2012 Replication - How do you reinitialize in 2012 just one partition from a large SS 2012 table to another SS 2012 table? If...

Efficient management of table and sp access - I have a three db's on one SQL Server 2012 instance and one on another server for which I need...

Trying to locate SQL 2012 SP1 CTP4 - any suggestions? - We have a SQL 2012 Server setup before I was with the company that I need to move it to...

Ideas for stopping customers meddling directly in the tables? - We have some issues with customers meddling directly in the tables for our application. Has anyone any ideas for monitoring or...

how to convert open office DB file into SQL - i want to convert open office DB file into SQL file. is there any tools or script api to do this?

bulk export of xml column - with reference to http://technet.microsoft.com/en-us/library/ms191184.aspx [code] E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_featu res\db_features\bulk_import\examples_of_bulk>bcp practise..xTable out a-wn_x.out -x -T -S user-PC\sqlexpress Enter the file storage type of field xCol [nvarchar(max)]: varybin Invalid...

BULK import of binary in xml column - from http://technet.microsoft.com/en-us/library/ms191184.aspx D. Specifying the field terminator explicitly using a format file Sample Data File [quote] FF FE 3C 00 3F 00 78 00-6D...

ms sql server from fci to standalone - We have FCI with 2 nodes running 2012 Enterprise Edition. I wan't to remove fci nodes from both nodes, and...

REPORTING SERVICE - Hello All... Please I would like help on the following regarding reporting services...First here are the basics.. I work as data entry/database...

Moving from mirroring to AlwaysOn for SQL Server 2012 - I am setting up two test servers loaded with SQL Server2012 Enterprise edition, one in a datacenter on the East...

Minimally Logged trouble: SELECT ... INTO vs. INSERT INTO WITH (TABLOCK) - Hi everybody. I've an unexpected behaviour filling a table with the [i]tablock[/i] hint. I need to use minimally logged insert...


SQL Server 2012 : SQL Server 2012 - T-SQL

Opening an ADODB recordset from SQL Server 2012 Stored Procedure - I have a large SQL Server database with hundreds of complex stored procedures, many of which execute a Select statement...

sql query for ssrs report - I have the following report I need to generate . I am new to SSRS. [code="plain"] Number of Stores with June July...

identifying many to many relationship between columns - Hello All, I am struggling with something that is conceptually simple but I'm at a loss at how to implement it....

Passing multiple values to a parameter in a stored produre - Can anyone help, I have the following stored procedure create procedure TEST_procedure @exampleid as update tablea set text_field = 'Y' where example_id in (@exampleid) I would like...

Proc with input paramaters does not compile - I have a SQL 2008 R2 proc which compiles & executes fine. It receives input variables to execute. This proc no...

Behaviour of CONVERT with GETDATE() !!! - [size="1"]select dateformat from sys.syslanguages where name = @@LANGUAGE --[b]mdy[/b] select getdate() --[b]'2013-11-11 16:00:04.960'[/b] [/size] Dear All, Above are the stats at the time of creating this post. Below...

Split function in sql - Hi , I need the store procedure which i need to get the comma seperator values result in different columns and...

SQL Report Builder - Hi, Not sure where to put this... Im working in SQL Report Builder (sorry dont know which version) and am having trouble...

Return the last version of set of records - Hello all, I have a query I am working on where an orders table has a version column for each...


SQL Server 2008 : SQL Server 2008 - General

Insert Rows from excel file - I have an excel file with the items listed horizontally...I need to insert each item as a row in my...

Trim Function Problem - Hi All, I have two table originalHeader and CurrentHeader of which have one column named Colname and Column_Name. One value in...

query plan with "parallelism"... - hi everybody I have a query that runs in 2 seconds when I add another inner join, the same query lasts 120...

There is a prefered age for developers and administrator the recruiters in companies preferred it? - I'm in [b]30 years[/b] old now and try to find a job in SQL Server Administration and Development but I'm...

Connection problem with SQL 2008R2 and SQL 2005 on same server - Windows 2008R2 server had 2 instances of SQL 2005 installed, BACKUPEXEC and ACRONIS. I installed SQL 2008R2 Express (64 bit), created...

How can I create a user with securityadmin role that can assign read/write rights on tables, execute rights on procedures? - Good day all, How can I create a user with securityadmin role that can assign read/write rights on tables, execute rights...

Disk space gets full and cluster goes offline - Hi, We have 2 node cluster in windows server 2003, sql server 2000. Drives are allocated from net app. When the...

tempdb secondary file not functional - we have a SQL 2008 R2 instance where tempdb data file was full and the drive also didnt have any...

use cte instesd of while and temp table - hello all. I have this quey: IF OBJECT_ID('tempdb..#Out') IS NOT NULL DROP TABLE #Out declare @count int,@i int,@RoleID int,@UserID int create table #Out...

Execution Plan sugested creating nonclustered index - Hi, i am paying more attention to execution plans right now, and after execution a few queries, SSMS suggested that: [quote] /* Missing Index...

trouble shoot replication - i did not worked on replication , how to trouble shoot replication ? any queries to trouble shoot transactional replication ?

One Log Writer per instance? - Hi guys, Looking at sys.sysprocesses I saw one Log Writer process, rather than one per database. Does this mean that worker...

Error To Open Sql Server - Error Occure when Open An Sql Server Microsoft Sql management Studio Package Not Load Iahave Also Attach Th Error Window Kindly Suggest...

Unable to see the available space value in @alertMessage - Hi Guru's, I am new to this forum and having below issue. The below code is working fine but the problem is...

DB_creator server role.. - hi, i checked number of tutorials in that DB-creator server role can do create alter drop their own database . but this...

Most frequent used stored procedures without access to system tables - Is there a way to determine the most frequently used stored procedures in a given database if you do not...

SQL Server : error trapping sp_send_dbmail errors to prevent Job Failed status - I posted the following in Stack Overflow and didn't get any response. I'm just looking for a better way to...

windows os support for sql 2008 and sql 2012 - I would like to know whether sql 2008 and sql 2012 enterprise/standard or developer edition has support on windows 7...

Patch SQL 2008 Express - Maybe a dumb question, but can you patch express edition?

sql jobs issue - Hi All, SQL nightly jobs were hung because one of the users forgot to close the application or log off from...

Access rights - How to provide only rights to execute sp_who2 & killing the blocking SPID if any without giving sysadmin rights, is this...

Running Putty through Agent, package hangs. - I'm running into a odd error using Putty in one of my packages and having it scheduled through the agent....


SQL Server 2008 : T-SQL (SS2K8)

OpenQuery linked server and local variables/syntex - I am trying to query from a linked server where a fields is in a list of values queried from...

XML Export File - Hi Friends, The below i have mentioned the sample data and Table structure [code="sql"]CREATE TABLE [dbo].[Xml_Export]( [City] [nvarchar](200) NULL, [Company] [nvarchar](300) NOT NULL, [Bedrooms] [int]...

ISOLATION LEVELS - Hii Guys what are Isolation Levels ? how to apply various isolation levels and please give me a detailed information in understanding...

Criteria Table - Hi all, I've had a trawl through the forum but can't find anything that will help although I'm sure this isn't...

how to find which date is greater using SQL (urgent Help neeed please) - Dear friends , being new to SQL i needed to know how to find - “Work Order End Date_WO”is greater than “Previous...

How to bring in more than one column from left table in a CURSOR/dynamic sql query - First of all, if this can be done without the use of a cursor, I'm open to switching away from....


SQL Server 2008 : Working with Oracle

Totally lost on creating a linked server from 2008R2 to Oracle - I've reviewed the two threads here, and many articles from doing a Goggle search. They all follow a theme, but...


SQL Server 2008 : SQL Server Newbies

Where to start to learn XML with SQL Server - Hi Floks, I am trying to get my hands dirty with XML in SQL Server. I have tried MSDN and some...


SQL Server 2008 : SQL Server 2008 High Availability

Transnational Replication - Hi, I am using Transnational replication of Microsoft SQL Server 2008. I always face one problem when distributor database fails eventually....


SQL Server 2008 : SQL Server 2008 Administration

Updating ID field - Hi, I have a table: CREATE TABLE [dbo].[FIM_GLOBAL_ID]( [GlobalID] AS ('RH'+right(''+CONVERT([varchar],[ID]),(10))), [ID] [int] IDENTITY(100100,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [nvarchar](50) NULL, [PreferredName] [varchar](50) NULL, [DisplayName] [varchar](50) NULL, [DateofBirth]...

Merge Replication - is it possible to have a Publisher as 32 Bit SQL Server and Subscriber as a 64 Bit SQL Server. - We currently have 1 publisher and 1 subscriber for our Merge replication setup. We currently have SQL Server 2008 standard edition...

Corrupt Backup File ? - I got this message when trying to restore a DIIF backup Msg 3183, Sev 16, State 2, Line 91 : RESTORE detected...

SQL Installations - What role does the installation keys play - Hi guys/gals After scouring the dark corners of the interwebs for the last couple of day and can't seem to get...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Query Runs for Long Inspite of Indexes on table - Hi I have a table which has more than 6 million records . Below is the table CREATE TABLE [Off].[EngineResponseResult]( [EngineResponseResultId] [int]...

Index creation help - I was analysing an execution plan before and after creation of an index. Disk IO decreased considerably after creation of...

Curiosity on performance increase - I'm just curious as to why this would increase performance so drastically. I have a stored procedure that accepts two...

T-SQL Performance tuning on Aggregates - The below query resulted after 30minutes. It uses 2 uses and SUM(). Attached the execution plan too. Index was created as...


SQL Server 2005 : Administering

Statistics Needed - When loading data? - I have a bunch of empty tables, i will be disabling all the nc indices and leave clustered index enabled....

Craeting DR in sql 2012 standarad version for priamry server 2005 enterprise sp4 - Hi I have one query Is it possible to create DR in sql server 2012 standarad edition for a primary...


SQL Server 2005 : Business Intelligence

truncation error - hi have a derived column with this in it BLOOM_COUNTRY != "" ? "BLOOMBERG" + BLOOM_COUNTRY : CO1 before that i have a dervided column that set...

Drop and Create table or Trunc table - Hi All, I have a scenario in which I have to delete rows in a MS SQL Server table and load...

Execute Child packages parallel in loop - hi , I have a master package which calls the child packages from a folder dynamically, when i tried this with...


SQL Server 2005 : SQL Server 2005 General Discussion

Selection!! Please help - Hi I inspection offices (+-200) i need to select the ones that did not perform inspections for the past 2 years please...


SQL Server 2005 : SS2K5 Replication

Error - "Unable To Synchronize The Row Because The Row Was Updated By A Different Process Outside Of Replication" - HI, I have a replication setup where there is a publisher and 4 subscribers. All running SQL 2005 SP3 and a...


SQL Server 2005 : SQL Server Express

List of SQL Server Express gotchas? - Is there a list anywhere of the different behaviour of SQL Express? It's things like the Auto Close setting defaulting to...


SQL Server 2005 : SQL Server 2005 Performance Tuning

Slow performance - I have face slow to retrieve the data from particular table. This table frequently data inserted and same time 50 user...


SQL Server 2005 : SQL Server 2005 Integration Services

SSIS (dtexec) and bulk load permission - Hi, I am an accidental DBA trying to install existing SQL Server 2005 in a new environment with existing contents. I...


SQL Server 2005 : T-SQL (SS2K5)

Concatenating columns returns NULL results - When I concatenate 2 columns, it returns NULL result. Column1 has some NULL value. How do I ensure it displays...


SQL Server 2005 : SQL Server Newbies

sql 2012 certification - Hi Can someone help me with correct certification path for SQL 2012 data platform.I have cleared my MCITP 2008. Also is...


Reporting Services : Reporting Services

Remove NaN and Infinity - Goal: I do not want to display the text NaN or Infinity in the object matrix list in SSRS. Instead, I...


Reporting Services : Reporting Services 2008 Administration

Report Grouping - Grouping Question - Best advice Here's my dataset DocNum ItemNumber ItemType QtyOnHand MO001 Item A LotTracked 5 MO001 Item B NotTracked 100 MO001 Item C LotTracked...


Reporting Services : Reporting Services 2008 Development

Experssion - I am trying to Sum the percentage of increase or decrease in the Dataset. Here is the expression I have to...


Programming : Connecting

Native Client driver choice by SQL Agent - How does the SQL Agent choose what version of the NC driver to use when connecting to linked servers? and/or...


Programming : General

SSDT 2012 REquired Permissions - We are not allowed to have persistent admin privileges on our workstation where I work. After I installed SSDTBI 2012...


Programming : XML

Stuck with an xml nodes query - Hello there, I'm currently working on extracting a load of xml elements out of a dynamic blob of xml. I have...

Extracting XML - I know this is not a new issue but: How can I extract data from XML column: I have DB1 and Table1...


Data Warehousing : Integration Services

Handling a large ADODB record set in data table? - I am loading a large result set into a data table. Its taking too long to even load the result...

Data flow task showing yellow - but all data flow tasks completed?? - Hi can anyone tell me why a data flow task shows as yellow on the control flow, but all internal...

expression for sql command in data flow task - I have a data flow task with two OleDB connections, one for the source and one for the destination. I...

Import data from Excel to DB table - Hi, I'm learning SSIS and as a part of my project here is one scenario: I've a folder location where .xls...

Cannot read first column from Excel File - Hi, I've been using SSIS for quite some tome reading Excel Files, and I just encountered an error I had never...

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D - I get the error below when I execute my SSIS (2008) package in BIDS 2008 - [Execute SQL Task] Error: Failed...

SSIS - cannot connect to database after putting tasks in sequence container - I had a package that worked perfectly until i decided to put some of its tasks inside a sequence container...


Data Warehousing : Analysis Services

Discover Cube Error befor Production Phase - Goal: Apply a fresh deployed cube in the production environment. Problem: How should I enable to discover any problem in the cube when...


SQLServerCentral.com : Anything that is NOT about SQL!

Jobs and Outsourcing - not to make a political statement, but more out of curiosity. Someone pinged me saying they didn't see a lot...

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


SQL Server 7,2000 : In The Enterprise

SQL ADSI Query limitation - Hi, I am trying to query a list of 2500 users out of the AD in SQL Server with the following...


SQL Server 7,2000 : Security

Anonymous access in SQL RS 2008 - Hi In SSRS 2008 their is no IIS required. So anonymous access is not possible by configuring IIS. I find the...


Career : Job Postings

Jr. Business Intelligence Analyst (Mid-Michigan) - Two Men and a Truck International is looking for a Jr. Business Intelligence Analyst. This is a direct hire position....

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com