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

Data Decisions or Instinct?

Most of us that are data professionals think the best way to make decisions is to use data to justify some course of action. We look for patterns in data, some guidance that the information we have will lead us to make the best choice for our organization. Google has talked about making data driven decisions as a part of their success and they think more organizations should do this. Any number of other companies also use data to power their BI systems and dashboards that help their employees make better choices.

That seems in contrast to this piece from the Harvard Business Review that says that great decisions don't start with data. It talks about using stories and emotions, with a few key facts sprinkled in, to help sell ideas and get decisions made. On one hand I agree that stories help to sell decisions, but I often have found that successful salespeople use this technique to deceive and convince by plucking emotional heartstrings, and using relatively little data.

In my mind, the best way to make decisions is to go with your instincts, but while examining and understanding the data. You can't discard data, especially when it presents strong patterns. However data can be deceiving when we don't carefully examine the ways in which it's put together. An average doesn't always reflect the actual value of a set of numbers, especially when we don't also understand the range, standard deviation, and count of values. 

We also have to realize where we do and don't have experience and expertise in some subject. We should certainly look to data to guide us and perhaps even justify our decisions, but we can't forget that the human brain is still an important part of any computational exercise. We need employees that you use their judgement, in collaboration with data, to make the best decision for our organizations.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA 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.

Everyday Jones

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

ADVERTISEMENT
SQL Data Generator

Generate realistic test data, fast

“In less than the time it took me to get my coffee, I had a database with 2 million rows of data for each of 10 tables.” Stephanie Beach, QA Manager. Try SQL Data Generator now.

DBA Team

Can 41,000 DBAs really be wrong?

Join 41,000 other DBAs who are following the new series from the DBA Team: the 5 Worst Days in a DBA’s Life. Part 3, As Corrupt As It Gets, is out now – read it here.

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

 

Columnstore Index Changes in SQL Server 2014

Edward Pollack from SQLServerCentral.com

SQL Server 2012 introduced columnstore indexes, which can immensely improve the performance of OLAP queries. How were they updated and improved in SQL Server 2014? More »


 

Restoring the SQL Server Master Database Even Without a Backup

Additional Articles from MSSQLTips.com

Many DBAs back up their user databases, but not their system databases. Whatever the reason for the lack of a backup, John Grover explains how to save yourself (mostly) should you ever find yourself with no viable master database and no good backup. More »


 

SQL in the City Workshop - Continuous Integration Stairway

Additional Articles from SQL in the City

Red Gate’s running a half-day training workshop at their UK head office in Cambridge on Aug 8. It’ll show you how to link your database source control repository to your build system as the starting point for continuous integration. More »


 

From the SQLServerCentral Blogs - T-SQL Window Functions – Part 4: Analytic Functions

DataOnWheels from SQLServerCentral Blogs

This is a reprint with some revisions of a series I originally published on LessThanDot. You can find the links... More »


 

From the SQLServerCentral Blogs - Quick Tips–SQL Prompt Aliases for Every Table

Steve Jones from SQLServerCentral Blogs

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love... More »

Question of the Day

Today's Question (by Dave):

Evaluate the following statement and choose the most correct answer:

The staging process for importing data with Master Data Services (MDS) in SQL Server 2014 has changed since 2008 R2. You must rewrite 2008 R2 processes or they will fail.

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: Data Warehousing.

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

ADVERTISEMENT

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Sean Pearce):

What will the following query return? 

SELECT
    COUNT(*) AS Result
FROM
    sys.databases d1
INNER JOIN
    sys.databases d2
        ON UNICODE(CAST(d1.name AS VARCHAR(255)) COLLATE Latin1_General_CS_AS) = UNICODE(CAST(d2.name AS NVARCHAR(255)) COLLATE SQL_Latin1_General_CP850_BIN)
WHERE
    d1.database_id <= 4
    AND d2.database_id <= 4;

Answer: 10

Explanation:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_BIN" and "Latin1_General_CS_AS" in the equal to operation.

This is incorrect because we are specifying a collation for a string before passing the string to the UNICODE function and then comparing the output of the function which is an integer. We would receive this error if we were comparing the strings.

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

String or binary data would be truncated.

This is a red herring. Although the function only returns the unicode value for 1 character, the function will return the value for the first character of the input expression. The casting of the two values differently doesn't matter because the input expression is a ncharacter expression and the input values will be converted implicitly.

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

0, 10, or 4

The query is valid and is joining a list of system databases with itself on the first character of the name.

We have two distinct first characters. t for tempdb and m for master, model and msdb.

t will return 1 row (1 * 1) and m will return 9 rows (3 * 3) giving us 10 rows as the return value.


» Discuss this question and answer on the forums

Featured Script

Move End of Month file from source to destination

Prem Kumar from SQLServerCentral.com

Anyone who is using this script can schedule this on 1st of every month for moving full backup file from source to destination location. The name of the source file is considered as "2014_05_31". You can change the source format according to your needs.

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

Redbuild CMS tables from XML file - Hello, I had to reinstall my local copy of SQL a few weeks ago, which naturally overwrote the msdb.dbo.sysmanagement_shared_server_groups_internal and msdb.dbo.sysmanagement_shared_registered_servers_internal...

Unable to create credentials - What is the needed permission to create credential except for sysadmin? Already granted securityadmin but still user does not have permission...

Downgrading from SQL Server 2014 to SQL Server 2012 - Hi, I need to know how I can downgrade to a SQL Server 2014 installation to SQL Server 2012. Thanks


SQL Server 2014 : Development - SQL Server 2014

how to call dynamic query stored procedure in select statement - Hello, i have created a stored procedure with dynamic query and using [b]sp_executesql[/b] . stored procedure is work fine. now i...

sp_send_dbmail truncates when attaching query results. - Installed SQL Server 2014 CU1. While testing sp_send_dbmail I noticed the query results, when attached are cut off or truncated....


SQL Server 2012 : SQL 2012 - General

domain account errors out when use as service accounts - installed sql server 2012 enterprise. Runs with the built in account fine. I tried entering a domain account to run as...

Changing collation - Hi, I need a bit of advice, I am currently going through a migration to a new data centre and SQL...

Schema Changes on SQL 2012 High Availability Group Databases - Hi So we have our HA group servers and databases, now we want to deploy schema changes to the HA group...

SQL Server Distribution Synchronisation Error - I have been attempted to setup a transactional replication across two servers. I am using two separate Virtual Machines. One is...

Creating a Test DB from a full backup of Prod - I am attempting to create a Test db from a full backup of the production db. With 2012, I cannot...

SQL Alias between client and server - Hi, I am trying to set up a client alias to connect to a named instance on another server but for...

Allow a low privilege user to execute a stored procedure to create db users - I have a requirement to allow a user to restore a database and then create database users and add them...

Need help on Configure linked server with Failover partner - Hi, Firstly, I don't to it is possible to create Linked server with Failover partner option. I can query when primary...

SQL 2012 Multi Instance Cluster - DTC - Hi all I am required to set up a multi-instance SQL/Windows Cluster. Windows 2012 Ent/SQL 2012 Ent There will be 12 instances across...

Index Maintenance and Update Stats - Hi there I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to...

Insert sequence number - I need help to code to add sequence number. One claim number may have various max sequence number. For example, 1) Claim...

SSAS Pivot refresh - Hi All, Request your help in the below scenario which i came across today. I had a pivot to pull data from...

Trigger after UPDATE INSERT based on column value - Hello All, I would like to change a value after the table updates \ inserts a new row based on the value...


SQL Server 2012 : SQL Server 2012 - T-SQL

How to get most recent and oldest from the the joins to a child table - Hello, I have the following tables in my DB [b][u]Employee[/u][/b] table - This table has EmployeeID, Name, DOB. [b][u]EmployeeDesignation[/u][/b] table - 1 Employee can have...

IN / JOIN / EXISTS giving same performance in a query. - Hi all, I have written a query in three different ways using IN, JOIN and EXISTS. As per my knowledge, Exists...

Find Changed Rows (and the row prior to the changes) - Thank you for reading. I have a table (represented by #Events) that holds modifications made to another table. I do...

Please help me debug this SQL - Hi All, I've got this CTE and it's giving me fits. I'm getting the following Error which I can't track down. [b]Conversion...

How To Group By Values Over None Contiguous Data - HI Guys I was wondering if anyone can help me. Im trying to generate a list of data such as this [code="other"] STOCKID MIN_WKNUMBER MAX_WKNUMBER...


SQL Server 2008 : SQL Server 2008 - General

CREATE ASSEMBLY with SAP .NET Connector 3.0 - Hi all, has anyone created an assembly with the SAP-DLLs? If I try to create an assembly CREATE ASSEMBLY <mydll> From <path> WITH...

Transactional replication: how to replicate new objects without re-creating replication? - We have a transactional replication for a database with size 220 GB. It was created with backup/restore method. Sometimes developers make...

Detaching the database to free up space - I am trying to free up some space in the server by detaching the unused database and deleting the ldf...

Insert Data into Access - Hello All, I am trying to insert sql data into access database but having the error. "Test Connection failed of an error...

Question on data compression - I have a table with with several XML columns stored as VARCHAR(MAX) because they some of the XMLs may not...

What could be disabling triggers? - Everyone: In a couple of occasions, on two different databases, on two different servers (two different customers of ours)... we found...

Setting up DR for Reporting Services - I'm in the middle of doing a DR setup for an inhouse vended solution. Both Server A and Server B...

if where on update doesn't match do an insert - hi, I have this [code="sql"] UPDATE EPSBankRef SET OrthoID = '@Request.inpHidden~' WHERE BankID = SUBSTRING('@Request.TransDesc~', 0, 25) [/code] I wish to add, if there isn't a match...

Update from another table using substring.. - Hi, I have a table BankRef with a BankID column that was populated from the first 24 characters of a column...

Display Duplicates - I have a Contact db. It has the usual Columns: Company, FirstName, LastName, Address, City, ST, etc.. When I SELECT DISTINCT on...

DB transaction log file on a shared hosting service - Hi, Thanks to this forum. It's great. I have a SQL DB on a shared hosting service (NewTek/The SBA) with 400Mb disk...

Difference in Time for same query - Query time having different timings [code="plain"][b]Start Time End Time[/b] 2014-07-23 12:10:28.423 2014-07-23 12:10:28.643 Query Select col1,col5,col8,col9,col10,col12,col14,col15,col16,col17,col18,col19,col20 from Table_name [/code] [code="plain"][b]Start Time End Time[/b] 2014-07-23 12:10:28.657 2014-07-23 12:10:30.860 Select col1,c

Good script for index maintenance - Hi All, I wonder is there any good script for index maintenance ? ..especially how to define of fillfactor number on certain...

Count of Membership per month starting beginning of each year - I need some help with a script to count the total amount of members each month of current year. I have...

Non-Clustered, Composite Primary Key - Hi All SQL 2008R2 I have a non-clustered, Composite Primary Key. It is the natural key from the business domain and is...

Tempdb not releasing the space - I did try shrink the tempdb database then file. Also, restart SQL Server to release tempdb space to OS. But...

4 Data files and 3 Log files on same drives!! - I've got a Production server with 4 data files on D:\ and 3 Log files on L:\. which was migrated...

1 Quick question for all the DBA's - I am wondering if someone can help me out. I have become a SQL Server DBA about 7 months ago,...

Stuck stored procedure - I have a stored procedure that creates backups of all the databases on my server. It is called from a...

Need Help parsing through the XML - i have an XML that needs to be written onto a table here is what i have done i created...

Index grows really fast! - Hi all , I have just created an index but it grows so fast... in 3 hours it becomes 95% fragmented...

Need help with basic spatial query - I have a table with events that took place at specific x,y coordinates and another table with a listing of...

How to efficiently import data in .rpt format into SQL 2008 - Good day, I have been told to import 100G data generated in SSMS and in .rpt format (10+ files) back to...

? on merging fields - Hi I have a table(Comments) two columns clientno and comment one clientno can have many comments I want to merge all the comments for...

Audit connections via extended events - Hi, I am looking for a little bit of advice on how to setup an extended event in SQL 2008 R2...

Convert file from ANSI to UNICODE - Currently, we generate file in ANSI. Is there a setting we need to change to generate files in UNICODE? or...

disk space 90% full... - Hi friends, Today i found that one server is having space issue, it has 90% disk space full and in that...


SQL Server 2008 : T-SQL (SS2K8)

Trying to SUM row with Current Date to Row with "Last Month" Date - Hello, I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last...

Multiple updates using a CTE - Hi Everyone, Can anyone help me on writing this code using a CTE or any other method which do not use...

Problem with Round - I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the...

Unintended Escape\Continuation Character, BackSlash, in Executed Variable? - I was trying to see if there is any documentation regarding the backslash symbol "\" as a escape or continuation character...

When Does Minimum not Mean Minimum? - I've got an interesting little problem that's really puzzling me. I have two tables like those below and I'm trying...

Insert multiple values based on parameter - Hi All, I need to write SP where user select SUN to MON check boxes. If user select Class A with...


SQL Server 2008 : SQL Server Newbies

Stupid Question: Return Object Explorer to It's original position - Stupid question but I accidentally moved object explorer from it's original default position on SSMS. By default, object explorer is...


SQL Server 2008 : Security (SS2K8)

Execute permission - Hello, I am trying to execute [b]xp_logininfo[/b]. Part of the KB says: "[i]membership in the public fixed database role in the...


SQL Server 2008 : SQL Server 2008 High Availability

How to upgrade an Active & Active sql cluster? - Hi Experts, How to upgrade an Active & Active sql cluster? Is there any difference in upgrade for sql 2005 Active-Active cluster and...


SQL Server 2008 : SQL Server 2008 Administration

Database backup with 3 log files need to restore to another server. - Hi all, I have a DATABASE on server A, which has 2 data file and 2 log files ( plz don't ask...

Need Script for Automating Find and Fix Orphand users - Hi Friends, I have 10 Databases . Irefresh these databases from PROD to TEST server. after refresh i have to fix the orphand...

Job for masking 6 databases - Hi friends, I have a script for masking. Using this script i want to create a job for masking 6 databases one...

Shirnk Tlog(LDF) size? - Hi, Database High availability - Mirror setup configured, Principal server Tlog (.LDF) file size 25 GB, also warning messages recording at error...

Transactional replication with updatable subscription - Replication champs, I have a Transactional replication with updatable subscription setup having 1 publisher and 3 subscribers. All the servers are...

Backing up a DB with log shipping turned on - I have a Db which has log shipping turned on. If I create a separate maintenance plan to do a...

Copy partition data on OLTP 24x7 instance - Hi, I am looking to change a number of table partitions from a specific range ie currently 30 day to 15...

SOX Compliance - I apologize if this is not the correct place to post my question on SOX compliance....Did not know where else...


SQL Server 2008 : SQL Server 2008 Performance Tuning

High to troubleshoot high cpu and high memory usage happening on the same time? - Hi Experts. What would be the proper troubleshooting approach for the below performance issue. Server is 100% cpu and 90% memory...

TempDB Avg Wait Times - Hi All, I am new to the forum and don't have a ton of SQL experience. In our environment we...

Taking long time for a single table execution - Hi, I am having a table "Test" with 70 columns. Out of them a composite primary key is defined with...


Cloud Computing : General Cloud Computing Questions

SQL Azure - Dear all, I need answers for the below questions on SQL Azure. Please explain. 1. Few of our applications use cross-database queries...


SQL Server 2005 : Administering

Link Server Security Issue,not able to access from SQL ID - Hi, I have created a linked server which reads data from an excel file i am able to run query against this...


SQL Server 2005 : Business Intelligence

Specify Values in SSRS parameter - Hi! I have a SSRS report , parameter @A uses 'specify values' (integer 1 and 2) as available values. What I need now...

SSRS hide column - I want to hide one of my column based on this logic; =IIf(First(Fields!TOLERANCE_CHECK.Value, "NetUnitPricingReport")= NULL, True,False) I get an error on the...

Problems Using Expressions to control the Visibility ToggleItem (SSRS 2005) - Hi, I have a problem I am hoping someone out there knows the answer to. I have a report with up...


SQL Server 2005 : Data Corruption

logon trigger and EXCEPTION_ACCESS_VIOLATION - Hello. My production sql server is running Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005...


SQL Server 2005 : SQL Server 2005 General Discussion

Update XML Column - I have a table "table1" that has an XML column (by the name of VariableData) in it. I need to...

Spid of -3 holding permanent lock on database and tempdb does not have any tables after restart - I restarted my server's SQL Service when I could not kill a connection for nearly an hour. This caused many...


SQL Server 2005 : SQL Server 2005 Security

How to set password newly install Sql server 2005 by query? - Hi, I'm using Vb6.0 Database : SQL Server 2005. In my vb6.0 installation file, add sql server 2005 and install automatically if not already...


SQL Server 2005 : SQL Server 2005 Integration Services

format the output column in destination .csv - Hello, in ssis, I use a oledb source to execute a stored procedure and a flat file source to have the...


Reporting Services : Reporting Services

Horizontal Axis show all categories - Hi All, In Visual Studio 2008 report builder I have a bar graph with a LOT of categories in the horizontal...

?? on adding a day to a dataset filter date - Hi I have a two date parameters (StartDate and Enddate) I want to filter dataset1 by Startdate thru Enddate + 1 Day , I...

Multiple Databases - I have a query where in I Union two separate SQL database queries. It works fine. Now I need to...

ssrs 2012 rdl connect to data source - On a ssrs 2012 reporting server, I want to install load uploaded rdls to a test report server. The rdls...

Frustrating Issue - I have created this report twice but don't seem to figure out the problem. I keep getting this error The SortExpression...

SSRS2008R2 Data Driven Subscription username/password? - I inherited an SSRS server that was not using best practices; each report had it's own custom data source, and...

SSRS Migration tool like SSIS package upgrade wizard - Hello and Happy Friday! I have been searching for a migration tool for SSRS that is similar to the SSIS package...

SSRS Parameter issue (SQL Server 2008 R2) - Having an issue where I have created a report with a shared data source and several shared data sets. The...

Report Manager Layout Different after migrating from SQL Server 2005 SSRS to SQL Server 2012 SSRS - Hi! We've recently migrated from a SQL Server 2005 server with Report Manager running in IE 7 to SQL Server...


Data Warehousing : Integration Services

ssis data conversion - Hi, I have excel source and oledb destination. I am facing few errors ' ID cannot convert between unicode and non unicode...

SSIS Analysis Services Processing Task. - Hi SSIS - 2008R2 SSAS - 2012 I have got a very weird problem. I am trying to build a package on SSIS 2008...


Data Warehousing : Analysis Services

Date Dimension Filtering - Hello, I have a date dimension [Facility].[Close Date] which I need to filter on. The criteria for the filter is to...


Data Warehousing : Data Transformation Services (DTS)

DTS Information retrieve - Hello all, My first post here. It's probably a very easy, or not question, but I'm new to Data warehousing...

DTS package run while in the designer but not when scheduled. - I have a DTS package that connects to the database and generates text files. After each text file is created...


Database Design : Design Ideas and Questions

need help to design wills database - hi there i am new here and especially to design database aim currently in school i need help to design wills...


SQLServerCentral.com : Anything that is NOT about SQL!

What does a masters degree in database administration give you - Is there any justification for pursuing a masters degree in database administration? what would be the positives and the negatives?

Fantasy Football 2014 - SQLServerCentral would like to host a Fantasy Football league again in 2014. As of now, we'll run this league the...

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


Career : Certification

I'm totally new. Need some guide on SQL 2012 - Hi folks! I'll put it briefly.. I want to work with DB, but I know nothing about it because I have...


Career : Employers and Employees

Definition of a Database Architect - Been a loooong time since I last came here, seems like forever and yet remarkably familiar. Anyways, really got my curiosity...

This email has been sent to {user_email}. 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 ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com