In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Hosted 5GB free hosted storage with SQL Backup Pro
Download SQL Backup Pro and securely copy your backups to secure hosted storage. You’ll get your first 5GB of storage free. Try it now.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
SQL Skills Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

Stairway to Transaction Log Management in SQL Server Level, 8: Optimizing Log Throughput

This level takes a deeper look at how log fragmentation can affect the performance of operations that need to read the log, such as log backups, or the crash recovery process. More »


Configure SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing using PowerShell

With SQL Server 2012 AlwaysOn Availability Groups, you can configure read-only routing and to automatically redirect the read-only workloads to a secondary replica after a failover. Can this be done using Windows PowerShell? More »


From the SQLServerCentral Blogs - SQL Server – Add Date/Time to output file of BCP / SQLCMD

You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic... More »


Editorial - The Patch Wild, Wild West

I've had various iOS devices over the last few years and one thing that's annoyed me is the patching process. I have very little control over which patches I install, and I can't roll back patches if I get a new version of an application that doesn't seem to work well. As a result, I'm glad I have control over when I patch particular apps, and patch them rarely. This isn't the best security process, but it provides stability, which is usually more important to me on my devices.

It seems to have worked well for Apple, which has sold billions of applications across their devices. It works so well that Microsoft seems to have adopted a new process for the "Metro" style applications for devices and Windows 8. According to this article, the "Black Tuesday" for patches might be going away. All of the things I don't like about the iOS patch system seem to be coming to Metro apps. I wouldn't care, but since Windows Server 2012 has some of the Windows 8 characteristics, I'm a little concerned. Are we doomed to get more "Metro" style interfaces for Windows features and potentially third party applications that will send patches mixed in with enhancements? Will we get stuck "upgrading" systems with new features in order to get security patches?

Consumers want things to work, and I can understand Microsoft's desire to simplify the patch process, but removing the ability to roll back and not warning users of patches could backfire with companies that end up using these same devices. This most assuredly makes the process of publishing and distributing patches easier for Microsoft, but it's a big step backward for companies, and it's one that I'm sure will upset, anger, and frustrate both users and IT departments.

The world continues to abstract away from particular platforms for any purpose. Those of us using SQL Server are stuck with Windows, but many of the applications we use are increasingly being released on multiple platforms. If Microsoft is having issues with Windows 8, Windows phone, and Windows RT adoption, this is a sure way to continue the problems. Other ecosystems might not be any better, but if that's the case, then wouldn't you consider iOS, Android or something else?

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

SELECT [Values].[Prefix] + [Values1].[Suffix] AS [Name]
     , [Values].[Number]
 FROM (VALUES ('a', 1),                        
              ('de', 2))   AS [Values] ( [Prefix], [Number] ),
      (VALUES ('pple'),
              ('ttach'),
              ('manda')) AS [Values1] ( [Suffix] )

Will this query run successfully?

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

This question is worth 1 point in this category: Row Constructor. 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.

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.


Yesterday's Question of the Day


create table #temp (SomeText varchar(20), OtherText varchar(10))

insert into #temp 
select 'SomeText1', NULL
union all
select NULL, 'OText1'
union all
select 'SomeText2', 'OText2'
union all
select 'SomeText3', 'OText3'
union all
select 'SomeText3', 'OText3'

select COUNT(*) from #temp
select COUNT(1) from #temp
select COUNT(SomeText) from #temp  
select COUNT(All SomeText) from #temp 
select COUNT(distinct 1) from #temp 
select COUNT(distinct SomeText) from #temp 
select COUNT('T') from #temp
select COUNT(convert(int,NULL)) from #temp

drop table #temp 
In above query COUNT() function is being used in different ways. What will the count of rows each statement returns from #temp? Please give output of each 8 count statements in sequence as your best answer from below. 

Answer: 5,5,4,4,1,3,5,0

Explanation: COUNT(*) – Number of records in the table regardless of NULL values and duplicates.

COUNT(1) – Number of records in the table regardless of NULL values and duplicates

**IMPORTANT NOTE: The 1 does NOT refer to an ordinal location of a column. This will not count the records in the first column of the table as COUNT(ColumnName) does.**

COUNT(SomeText) or COUNT(ALL SomeText) – Number of non-NULL values

COUNT(DISTINCT SomeText) – Number of distinct non-NULL values

COUNT(distinct 1) - It will give count as 1 always. As said 1 does NOT refer to an ordinal location of column; it considers this as constant value, so distinct of 1 in all rows gives 1 so count is 1.

COUNT('T') - Number of records in the table regardless of NULL values and duplicates. Same as COUNT(1) because it is also a constant value (non-NULL value)

COUNT(convert(int,NULL)) - It will give count as 0 always. Don't know why it is giving 0 count however COUNT() function counts NULL values also.

Ref: http://msdn.microsoft.com/en-us/library/ms175997.aspx
http://www.bidn.com/blogs/BradSchacht/ssis/2195/select-count-vs-count-1-vs-count-columnname

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

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


Featured Script

Generate N numbers

Reursive CTE Script to generate n numbers 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

Can you move a table to new Filegroup/File without removing PK? - These are the steps I was going to take: --step 1 ALTER TABLE Orders.dbo.tbl_OrdersArchive DROP CONSTRAINT [PK_tbl_OrdersArchive] WITH (MOVE TO SECONDARY) --step 2 ALTER TABLE...

SQL Server service account locked - Hello, We had a case where in the domain account which is running SQL server got locked. Database connectivity through management studio...

SQL Server 2005 : Backups

Database Design - What are included columns with respect to SQL Server indexing? - Database Design - What are included columns with respect to SQL Server indexing?

SQL Server 2005 : Business Intelligence

how to generate records automatically from csv to sqlserver table using ssis etl - Hi All, i have one requirement in ssis. I have one csv file (source) having one column called UOM (Unit of...

simple web based reporting - I’m looking for a simple web based tool that can be used to generate tabular reports. Our application has a...

SSIS 2008 Package design - Hi All, I have a requirement where i need to copy around 70 tables from oracle database to Sql server 2008...

Impact of more datasets on a report in SSRS - What will be the impact of loading time and performance of a report we use more number of datasets in...

SSIS 2005 Creating filenames from a SQL Task - Good afternoon I'm in the process of building an SSIS package which will be used to export telephony data on a...

Business Intelligence - Integration Services - FTP Task - Can't save password - How do you make an FTP connection save the password?

SQL Server 2005 : Development

Automatic Update from Excel to SQL - I need to load the data from excel to a table in SQL Server 2008 and automate the updates.ie., If...

SQL Server 2005 : SQL Server 2005 General Discussion

Service Broker Issues - SQL 2005 Enterprise Edition 64 SP3 - Hello, Before to start I just resume actions I made to reduce msdb size (earn space on c: volume). I checked...

Service Broker Issues - SQL 2005 Enterprise Edition 64 SP3 - Hello, Before to start I just resume actions I made to reduce msdb size (earn space on c: volume). I checked...

JOIN on MANY to one and ensuring the JOIN is correct - Hi All I have a particularly problem with a join which is driving me insane, it involves basically joining two tables...

SQL Server 2005 : SQL Server 2005 Performance Tuning

so many indexes indexes - what if i create so many indexes in a database, on each and every table small or large...? any bad...

Memory usage not matching up with that shown in DMV's - I have a sql 2005 64bit instance on a 64bit windows server. The Server has 32Gb RAM Total with 26GB set...

SQL Server 2005 : SQL Server 2005 Integration Services

Sequence container not to fail if a task fails inside it - hi All, Just need a steer...in that i have a sequence container with 3 tasks i need to go down the failure...

ActiveX Script to Script Task - I am just a beginner in SSIS and having a problems to convert this ActiveX script task code to Script...

SQL Server 2005 : T-SQL (SS2K5)

Fatal error 824 - Im using sql2005 and in a simple query in the mngt studio return the error: Msg 21, Level 24, State...

SQL Server 7,2000 : Administration

require db monitor script for sql 2000 - Hi, Need help in writing a script for sql server 2000 to check the status of all db's in the instance...

SQL Server 7,2000 : Working with Oracle

"ORA-04091" Mutating Error - ora

SQL Server 7,2000 : SQL Server Agent

Monitor High CPU - Hi All, I created an alert under sql server agent alerts. I selected type as sql server performance condition alert, Object:Sql server...

SQL Server 2008 : SQL Server 2008 - General

Considering Moving to SQL Server - My company currently uses Access 2007 and wants me to look at moving to SQL Server 2008/2012. My question is...

How to select first non Null value from multiple fields - Hi, what is the best way to select fist non Null value from mutliple fiels K1,.... K3, I heard about case,...

Count number of visits by week, ending on Saturday - I am trying to get a count of visits by week with the week ending on Saturday. My problem is...

Checkpoint Background Process? - Hi All, I'm new to SQL DBA tasks. I noticed in sp_who2 output a database is showing checkpoint as shown...

What does "SET XACT_ABORT ON" mean? - I created a transaction stored procedure adding a line as Begin Tran But someone told me that need to modify as SET...

Searching Database tables for a specific value within a database field - Hi, I'm new to SQL Server and I need to find out if the value 'Criminal' is stored in any of...

two jobs runnig at a time - hi in my environment one job is running at 9 pmc, i want to run the another job at same...

Data Import // MS Query // Header Missing - Hi Guys, i wonder if anybody can assist me this is doing my head-in. I am using Query and linking it...

Capturing Blocking info - I am trying to capture blocking info with the teqnique detailed below. The issue i am having is that if...

Changing Execution Plans in Cache - Hello, I've exerpeinced something I've not noticed before, We have a heavily stored procedure in on of our databases and we've...

Give access to SQL Server Agent ONLY? - Just wondering if it is possible to give someone SSMS access, however only allow them to run jobs within the...

No of rows in Temporary Table - What is the maximum number of rows can be stored in a emporary table?

Fixing Divide by Zero Error - Hello, I have in select statement select t1.A/t1.B but get divide by zero error when I used with isnull isnull(t1.A/t1.B,'-') I get error...

Comparing Two Different Tables, Showing Values In Table 1 Not in Table 2 - Hello, I'd like to compare two tables, and show values found in table 1 which are [b]NOT[/b] in table 2, then...

Ranking Query - Hi All, i have a requirement to find out which employee pass three exams continuously if you observe below query dataset...

is there a way to skip or ignore unwanted files? - I want to skip or ignore those files that have a "unmatching" pattern in them (i.e, a_unmatched_b.xlsx, b_c_unmatched.xlsx, and so...

Exceeding 25 replication agents on an instance - Using SQL Standard Edition (2005, 2008) the server is not able to startup more than 25 replication agents. We've run into...

Reinitialize log shipping in sql server 2008 R2 after activate Secondary server DB - Hi All, I have set up logshipping between server A (Primary) and server B (Secondary), and tested log shipping by...

Is there a better way to insert data than Access? - Is there a better tool to insert data into SQLserver than Access ? For managers. Small number of people. Very limited...

Backup failure History.... - Hi Team, I need to find out the backup failure history..... Till date how many backup are failed in sql server...

Partition alignment w/ GPT partitions - I'm new here about a month and have been looking through the environment to correct some performance issues. I've come...

SQL Server 2008 : T-SQL (SS2K8)

Need to call a table as a variable - I need to pull records from a table and that would be pretty simple, however, the select statement needs to...

Wildcard DB name in VIEW of Procedure - Possible? - Hi there, I've been trying to figure out whether it's possible to have a Wildcard DB name in a SELECT query....

Storing Spatial Data - Hi All, I work with geospatial specialists who provides me with polygons which i save it to my geography field. And...

comparing list to master list - How would you write a query that would return only the items in the where clause, which were not found...

Can someone help with a TSQL date sorting issue? - I would like to retrieve data for all the fields listed in the query below. The problem is that I...

Group of Groups - Hello All, Thanks if you would like to help. I am hoping there is a 'best practice' way to solve this problem...

Arithmetic overflow error - double join? - Guys, I've created a temp table which has a bunch of IDs and dates, something dead simple like this: [quote]SELECT Pg.ID, pg.Name, COUNT(pa.Log_ID) as A, FROM...

Alternative UPDATE Syntax - Hi all, I have a question about the UPDATE syntax. There are the following - fictive - tables: Table ParamValues: [code="other"] PV_ChangeDate PV_ChangeUser PV_ID PV_Item PV_PK_ID PV_IntValue -----------------------...

Urgent Query help needed- calculate one field based on other field in SELECT statment - Hi Friends, I have reporting TSQL query- in this query within the select statement I have 2 fields 1) Age (working...

Need advice on this string manipulate scalar UDF - Dear all, [b]Can you please comment on this UDF, is there any better way to do it? For example CLF scalar UDF...

UniqueIdentifiers - We have the "update" table with the clients' "client_fk" (format 'ntext') number that relates to the "client_pk" (format 'uniqueidentifier") number...

SQL Server 2008 : SQL Server Newbies

Export data to flat file based on field once. - Hi all, I have a table that is selecting all the fields from the table and exporting to flat file in...

Find client accounts missing a specific service - I am looking for the right way to determine a missing service from all similar client accounts. We have six...

not able to access databases - hello masters, My servers are on virtual environment (SQL SEVER 2005), some one rebooted storage server and now I am able...

Backup failed - Hello Masters, My daily differential backup job failed for user databases. all the user database are in simple recovery model. Below...

SQL Server 2008 : SQL Server 2008 High Availability

Windows or Database Clustering? - Hi All, if my windows in clustering mode, is my database in SQL server also in Clustering? or I will have...

Changing Owner or Mirrored DBs - How would I go about changing owners of a database that are within a mirror? Jonathan

SQL Server 2008 : SQL Server 2008 Administration

Error while I connecting oracle database in SSIS connections issues - TITLE: Connection Manager ------------------------------ Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified ------------------------------ BUTTONS: OK ------------------------------

Transactional Replication - What are the permission required to configure Transactional Replication? :-P

New features in sql server - What are the new features available in sql server 2008 and 2012

How to check performance issue - query execution is taking long time here how to solve this for this type of performance issues what is the necessary steps...

Small doubt - Here i have some doubts 1) what is the difference between current execution plan and actual execution plan 2) on which...

Shrinking Database or File not Reclaiming unused space - Hello, First time poster, long time lurker. I thought this was an interesting enough question to ask. Pretty often we bring our...

CPU on SQL server - Our .net application runs slow from a couple of weeks ago, it is using the SQL database, I see the...

No performance gain on queries on different servers with considerable hardware change - I have a dedicated Server for SQL Server. SQL Server runs on SQL Server 2008 Standard edition with SP2 applied....

Simple Replication Question - I am doing some testing with Replication, I have not used much replication before. I have set up a Subscription that...

Create DML trigger for single table in SQL 2008 and send email alert to group - Hi, i have a requirement to Create DML trigger for single table ( check for any updates ) in SQL 2008 and send...

BackupExec SQL Agent or SQL Server Native backups???? - I'm looking after a whole bunch of SQL Servers, both 2005 and 2008, some full and some simple recovery databases...

Commvault Simpana 9 SQL Backups - Hi, We have an ongoing battle with our Infrastructure Architect, where they would like to incorporate all SQL Backups in the...

Career : Certification

Differences between 70-432 and 70-450 - Hello: I'm now preparing the 70-432 exam and looked at the 70-450 and it seems quite similars, is this true...

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 : XML

Filtering results from SELECT on XML column - Hi I have the following table: [code="sql"]CREATE TABLE XmlTable ([ImportData] [xml] NOT NULL) ON [Primary][/code] I have the following XML format: [code="xml"]<Parent> <ID>1234</ID> ...

Programming : Powershell

French characters into SQL Server - Hi all, I use Powershell to deploy DB scripts. In one script file, I have French accented characters. When I call Invoke-sqlcmd...

SQLServerCentral.com : Anything that is NOT about SQL!

Features of NO SQL (may be mongo db) vs SQL Server - I want to hear some comments on "Featurs of NO SQL" from the mouth of actual sql developers / sql world...

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

how to add radio buttons in ssrs reports - Hi friends i have small doubt in ssrs plese tell me how to solve how to add radio buttions parameters in...

update parameter changes as soon as another parameter - I have a parameter in vai get today's date and have another parameter that will get the date one year...

SSRS Report will not render numeric values - I have a SSRS 2005 report that will not render certain numeric values. Nothing special about them, they are definied...

SSRS expressions to get part of a string - Hi, I need to write expression in my report to get the characters between "_" and "-". I have two columns in my...

SSRS 2008R2 GROUPING - I have add multiple goups in ssrs 2008 r2.My problem is when i try to add a group 1 extra...

Data Warehousing : Integration Services

SSIS Data Flow Task Crashing - I have a simple data flow task that moves logging events from one database to another. It pulls log events...

Rowcount incorrect in Execute SQL Task - I think I'm running into some sort of table lock issue within my SSIS package. I have a Data Flow...

Load dim Table using Lookup - Hi, I am a newbie currently trying handson on desinging SSIS and Star schema. I have 2 tables Location (LocID, LocName) and...