In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Easy evaluation of database source control
The evaluation repository makes it easy to try SQL Source Control. Get started with the 28-day free trial.
 
SQL Monitor What can SQL Monitor 3.2 monitor?
Whatever you think is most important. Use custom metrics to monitor and alert on data that's most important for your environment. Find out more.
 
Red Gate Cloud Services “Thanks for building such a useful and simple-to-use service”
- Steve Harshbarger, CTO, 10th Magnitude. Get started with Red Gate Cloud Services and back up your SQL Azure databases to Azure Blob storage or Amazon S3 – download a free trial today.

In This Issue

Hierarchies in SQL, Part II, the Sequel

In a followup to his first article on Hierarchies, Gus Gwynn takes a look at the performance of a few different methods of querying a hierarchy. Learn how the HierarchyID stacks up. More »


Red Gate Software announces speaker line up for US SQL in the City tour

SQL in the City is a free, full day training and networking event for database professionals. After the success of last year’s event, Red Gate has expanded the event to cover six cities from sea to shining sea, including: New York, Austin, San Francisco, Chicago, Boston, and Seattle. More »


SQL Saturday #169 - Denver

Come join Steve Jones, Glenn Berry, and other Denver area MVPs and speakers for a free day of training in Denver on Sept 22, 2012. More »


How To Support Multiple SQL Server Package Configurations in SSIS

I have several applications that use SSIS packages and I want to be able to store all the configurations together in a single table when I deploy. When a package executes I need a way of specifying the "application" and having SSIS automatically handle the package configuration based on the application. More »


Editorial - Big Data Jobs

If I asked you to give me five industries where Big Data jobs are likely to be growing, what would you say? Everyone's data is growing, but a few places may grow faster than others. One survey lists banking, retail, pharmaceuticals, government, and insurance as the top growth areas, which makes sense. Despite the slowdowns in the worldwide economies, retailers and banks have improved their systems to gather more and more data. There's no shortage of government growth with regards to the data being collected, and the drug companies see more data analysis as the way to reduce the costs of introducing new drugs.

What if I asked you to pick the places in North America where there will be lots of new Big Data jobs? According to the same report these cities lead the way with job openings: San Francisco, CA, Maclean, VA, Boston, MA, St Louis, MO, and Toronto in Canada. These are all areas with large concentrations of organizations from the industries with the most Big Data jobs. 

I'm not surprised at the list, though there are plenty of other places that are likely just below these five in terms of jobs. I wouldn't look to move to any of these places just for the Big Data job, but if you are looking to work in this field, you might think about checking out various recruiters and contracting companies' sites to see where there are lots of jobs.

As slow as the worldwide economies seem to be moving these days, our fields of technology and data seem to be growing, with plenty of companies looking to improve their systems and tackle new initiatives that might improve their ability to take advantage of the all the data they store. There are lots of jobs out there, and with some work you might find one is a great fit for you.

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

What will be the query result?

select '130'+'120'+1

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

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

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

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

Table A has 10 rows including 3 Null rows. Table B has 7 rows including 3 Null rows.

Select Count(*)
 from [Table A]
Select Count(Column Name)
 from [Table B]

What will the result?

Answer: Table A will return 10 rows and Table B will return 4 rows

Explanation: Because when we use count(*),it will return total number of rows in a table including null rows. But count(column Name) will only return NOT NULL rows.

Ref: http://blog.namwarrizvi.com/?p=137
COUNT - http://msdn.microsoft.com/en-us/library/ms175997.aspx

» Discuss this question and answer on the forums

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.


Featured Script

Identify Unique Columns

Tool to automatically query combinations of columns in your table to determine candidate for unique key. 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

OS 64 Bit, SQL 32 Bit - Hi All, i have a scenario where in the Operating System is Windows 2003 R2 Datacenter X64 bit edition service pack...

Have to enter the port to connect via management studio - A strange problem has cropped up in our enviornment that I'm kind of scratching my head about. Why all of...

Total backup size of backup of all databases in every month from backupset table in msdb - hi, Can you please provide a quick query to get the below requirement : Total backup size of backup of all databases...

directory lookup for the file failed with the operating system error 2 - Dear All, I am getting a problem.I have SQL server 2005 installed on C:\..drive.and I have S:\ drive as a LUN. Now...

Sysdatabases Status - HI, I have 2 databases with the following status in sysdatabases (4194320, 4194328). Where I can find the definition of these...

SQL Job failing because of Job owner - I have several jobs that have my ad account as job owner that fails because Could not obtain information about...

SQL Server 2005 : Business Intelligence

Cube Processing - Hi, I am new to SSAS.i have 1 lakhs record in cube but next day i am getting another 10k...

ssis how to send table records to each execel sheets share equal in on execel sheet - hi friends i have small doubt in ssis plz tell me how to solve this task. suppose i have a table...

Versioning in SSIS - I know DTS packages do we have versioning, just wondering whether versioning feature is still available in SSIS??

Argument “SMTP” for option “connection” is not valid. The command line parameters are invalid. The step failed - My SSIS package is run and sent email successfully if I run it from Stored package from Integration Service. So,...

SQL Server 2005 : Working with Oracle

Simple pass thru query to Oracle - Hello Oracle Forum: I've got my first linked server established from SQL Server 2008 SSMS to Oracle. What is the proper syntax...

SQL Server 2005 : SQL Server 2005 General Discussion

Error 2 querying Full Text Search Fields - Hello everyone, I'm having an issue querying some fields under Full Text Search. I have a stored procedure that receives 4...

Uninstall Clustered instance after the disks have been removed - Hi, First time posting on this forum so any help would be great :-) As the title suggests I need to uninstall...

High Space Used in Database - I am getting alerts for [b]high space used in database 90%[/b] in my one of the production server. Can any...

SQL Server 2005 Agent will not start - The agent service is configured to use a service account, as is SQL server itself. SQL starts just fine so...

XML File Load via OpenRowSet - I am using the following code to import an XML file via Sql Server. The XML file is verified to...

SQL Server Tools - Recomendations On Stress Testing and I/O Testing On New SQL Server - Need to test/stress a new SQL Server box to see what she can do. I went to get a copy...

SQL Server 2005 : SS2K5 Replication

Best one-way replication option going forward - I'm looking for some insight into implementing replication with a SQL 2005 Standard database based on our needs as well...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Is UPDATE STATISTICS operation on columns of NTEXT Data Type Dead Slow. - Team, Is UPDATE STATISTICS operation on columns of NTEXT Data Type Dead Slow. I have few statistics in the database which...

Execution context and Procedure Cache - Not sure whether i m thinking the right way...I know Sql server stores execution context information in Execution plan but...

SQL Server 2005 : SQL Server 2005 Integration Services

How to execute jobs in parallel from while loop of sql server - Hi, How to execute a job and loop through it until you have 5 jobs running in parallel(same job name) Is...

Looking for info on handling error with script. - I have a package that uses a for-each loop to connect to a list of servers to transfer various pieces...

Recordset Destination to SQL table? - Hello - I currently have an SSIS package being built where I want to upload rows from an excel document into...

Argument "Server1" for option "connection" is not valid. The command line parameters are invalid. The step failed. - I have a SSIS package run fine in BID, but when run as a sql agent job, it failed at...

Processing was cancelled due to an interrupt. SQLSTATE=57014 - My SSIS package throws below error while fetching data from DB2 to SQL Server. SSIS package fetch 50 tables from...

"Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT' could not be opened. Operating system error code 5(Access is denied.). - I've created a set of SSIS packages which run fine in our test environment. The packages access a SQL server...

SQL Server 2005 : T-SQL (SS2K5)

Function to split name field - Good afternoon, With a bit of effort this morning, I've managed to get my function to split my name field. There...

String concatenation - Hi I have the following table [code="sql"] CREATE TABLE #Test (ID int NOT NULL IDENTITY(1,1) Primary key, TranID int NOT NULL, OriginCode varchar(5) NOT...

Tlog space getting full whcle executing the Update statement - Hi All, Could you please assist on tlog space issues. I am trying to execute the query have update statement like...

SQL Server 2005 : SQL Server Newbies

need to fetch the value between two delimiter - declare @text varchar(max) = 'MSH|^~\&|||||21110428134755-0500||ADT^A04^ADT_A01|21110428134755|P|2.5.1|||AL|NE EVN|A08|21110428133821-0500||||21110428133821-0500 PID|zdly100001|zdly111000^556|PATID1234^5^M11|6g7g7|JONES^WILLIAM^Aryan^III^Dr.^MBA|Singh|19621225000000|M||2106-3|1200 N ELM STREET^^GREENSBORO^NC^27401-1020^TX|GL|(919)379-1212^^^test1@gmail.com^TX^324^5656568^121|(919)271-3434~(

SQL server table on a PC - Hello, can we create a SQL server table on a PC based on a query on the sql server ? Thank you

SQL Server 7,2000 : In The Enterprise

2005 Cluster unable to add new database - I have a 2 node windows 2003 cluster with SQL Server 2005 running. The first node is the owner of...

SQL Server 7,2000 : T-SQL

Help on splitting the name field in a query - I am trying to extract first name, last name and middle initial from the custname field, any help is greatly...

SQL Server 2008 : SQL Server 2008 - General

CONVERT DATA COLUMN TO ROW WTIH COMMA - HI ALL, HOW TO COVERT COLUMN DATA IN TO ROW WITH COMMA SEPARATOR DATA LIKE THAT :- TABLE NAME : - TEST COLUMN NAME : - COL1 ORIGINAL...

Multi Lingual SSRS Reports - Hi All Looking for a bit of guidance on a issue with SSRS. I know SSRS will not natively support translating tablix,...

CREATE VIEW permissions - I've got a schema (Reporting) that I want to grant CREATE, ALTER & DELETE permissions to a group; the T-SQL statements...

SQL SERVER 2008 R2 SP2 Upgrade - We are on SQL SERVER 2008 R2 CU5 and we are planning to upgrade it to SQL SERVER 2008 R2...

Consolidate rows based on criteria - Hi, here is some sample data [code] CREATE TABLE #TESTING (acc_no varchar(20), number INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) ) INSERT INTO #TESTING...

OPEN query problem - Hi I have a fresh install of SS 2008R2 on Windows Server 2008 and have been walking through a DMX query...

Determine if a database is read-only when the log is not marked as read-only so DBCC SHRINKFILE may be skipped? - I know the database in question should not be setup this way - one of the Finance guys copied a DB...

Position count excluding Vacant positions in hierarchical data - Hi, I have following table structure, [quote] PositionId ReportToId EmpId IsVacant LevelNo 1 0 123 0 0 2 1 124 0 1 3 1 125 0 1 4 2 126 0 2 5 2 Null 1 2 6 2 128 0 2 7 5 129 0 3 8 5 130 0 3 [/quote] From this I am able to calculate position count meaning number of positions under one position. I...

Clustered Key. (Making it unique). - Dear reader, I remember (from 2000 version): If the clustered key is not unique. A artificial field is added to make it...

Return datediff as X Years Y Months internationally - Hi, What I'm looking to do is have an ininternationalized function that returns datediff between 2 dates formatted as x Years...

compare case sensitive and case in-sensitive values in Lookup transformation - I have Source and Dest tables table :source Table : Dest ---------------------------------------- Col Col ---------------------------------------- AA aa Bab BAb by default SSIS is Case Sensitive and my...

Query timeout expired - State:S1T00,Native:0,Origin:[Microsoft][SQL Server Native Client 10.0] on production server - Hello, I am getting the below error on customer production error, Query timeout expired - State:S1T00,Native:0,Origin:[Microsoft][SQL Server Native Client 10.0] the queries are...

Strange Management Studio 2008 R2 (10.50.1600 and 10.50.1617) behaviour - Using management studio (10.50.1600 or 10.50.1617.0) Connecting to named sql server instance (lets call it [DEV-SQL\SqlKing]) version 10.0.4279 --> Cumulative update package...

How to mask a field? - Hi, I have a table with 10 columns Out of 10, i need to mask 5 columns because i don't want to...

log for database is not availabe - Hi, I am getting the below error error message. The log for database 'Commun' is not available. Check the event log for...

Middle Number Needs GROUP BY - I can't figure out the GROUP BY. This result should be: A 2 B 7 C 8 Thanks! [code="sql"] IF OBJECT_ID('tempdb..#TempMid') > 0 DROP TABLE #TempMid CREATE TABLE...

One skinny table or many wide tables? - This is driving me nuts, and we can't generate enough data to really test for sure - we need about 30...

How do I remove an MSDTC Instance from a clustered application that has already been deleted? - I had an old clustered instance on my server that I needed to get rid of. I went through each...

group data by week - trying to group this data by week instead of day.....SQL and I don't really communicate well, so any help would...

shrinking the log file - Hi, we have a problem at work and the DBA has just gone on holiday (on an airplan right now!) We have...

.csv format from ssms - Hello I want to take data extract into .csv format. I already try from select my result set and save as...

sql logics1 - hi firend i have small doubt in sql plz tell me answer Auto commit has been turned off. 1- William logs into...

Go from SQL 2008 R2 standard edition to Developer? - Is it possible to 'downgrade' SQL 2008 R2 from standard edition to Developer edition? I'm working on developing a long-term...

Installing SP1 on Cluster - Can anyone provide a link or instructions on how to install SQL 2008 SP1 on a 2node active/passive cluster. do...

SQL Server 2008 : T-SQL (SS2K8)

need help replacing cursor logic in stored procedure w/faster sql statement - I've inherited some very slow code in a stored procedure that uses cursor logic to read a table of purchase...

Convert UTC string to DateTime - Hi folks, I have one table and one of its column contains the data in this format. [b]Thu Jun 14 15:07:06 UTC+0530...

How to combine tables in one straight row? - Hello friends, I hope every one is in good condition upon reading this forum. I am finding a hard time combining...

Improving query performance to detect first duplicate - I need to query some tables looking for All Sales that are active at the same time and have at...

evaluating and combining values from two rows - I am running into a bit of trouble with the query below, would be great If you could offer some...

comma separate value show as table - I have One table tbaleOne Column1 ------------------------- AA,BB,CC DD,EE,FF GG,HH,II I need out put col1 col2 col2 --------------------------------------------------------- AA BB CC DD EE FF GG HH II

Convert Text to Date DataType - Hello Everyone, I have an sql table which stores dates from some other system as text. Eg.: DD/MM/YYYY ,31/12/2012. I have made...

Varchar Exceeding 8000 characters - I had a dynamic query in which exceeds 8000 charactes adn will have approraxmately 15000 characters how can the same...

FOR XML EXPLICIT add attribute to element - I am coding procedures which extract XML that is returned to a client application. The following code is a quick example...

SQL Server 2008 : SQL Server Newbies

SSIS 2008r2 - In the advanced editor for the OLE DB Destination I am adding columns to "External Columns". Once they are added...

Insert into one table from multiple tables - Hi All, Just needed a bit of help with an insert query. I have 3 tables that I need data from each...

Index Reorg and Rebuild - My understanding with these tasks, if the fragmentation is greater than 10% and less than 40%, reorg. If it is...

Down grade from sql 2008r2 Enterprise to Standard - Since the only way to go from Enterprise to Standard edition is to uninstall enterprise and then install standard. I...

Error Handling: TRY...CATCH - I'm trying to write an error handling query using the TRY...CATCH construct as a homework problem. I want to compare...

Landing The First SQL Job.... - Hello All! I currently am a plumber by trade, been in that industry courtesy of my father in law since graduating...

SQL Server 2008 : Security (SS2K8)

Table Permissioning - Is there a way to create a role having insert/update/delete permissions on each table in a database in such a...

Login failed when creating a linked server - Hi, I'm trying to create a linked server from a remote sql server to another remote sql server on the network....

Database connection encryption - Hi all, I'm planning to migrate some applications to connect to our SQL 2008 R2 instances using encrypted connections. However, one...

Giving users the right to execute a query w/o access to table - our general policy is to not provide direct data access to any user or group in the company for our...

user mapped as dbo - hi, we are trying to drop a login that happens to be mapped/aliased as 'dbo'. it is not the owner of the...

SQL Server 2008 : SQL Server 2008 High Availability

Netapp snap for files, log shipping for database? - One of our top challenges is to create a disaster recovery site so we can recover from an outage in...

Log shipping throw errors - I have log shipping set up on my database running on SQL Server 2012. It is scheduled to run Monday...

SQL Server 2008 : SQL Server 2008 Administration

This differential backup cannot be restored because the database has not been restored to the correct earlier state. - I restore a Database from a Complete Backups: [code="sql"] RESTORE DATABASE BASSISControlCYP FROM DISK = 'H:\Backups\Complete\BASSISControlCYP_backup_201208170809.bak' WITH REPLACE, NORECOVERY, MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCYP.mdf', MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCYP.ldf' [/code] Processed 640...

How to mask a field? - Hi, I have a table with 10 columns Out of 10, i need to mask 5 columns because i don't want to...

Synchronization of 2 different DB's - Hello, there are two similiar databases given. They are widely even identical though have some different columns and tables. I am looking...

Splitting a huge, a huge table in two or three - Ok, I've seen big tables and databases before, but this one is really big. I just recently discovered that the reason...

Rights for - Hi Everyone, I have a question regarding rights. Go to database -> Views -> Design View. I have some application administrators that ar...

Create index with DROP_Existing - Some Questions about Create index ... with Drop_Existing 1) in case of Create Clustered index ... with Drop_existing=on non-clustered indexes are rebuilt only...

Career : Resumes and Job Hunters

How can I attract Senior Database Administrator candidates? - I have a few full-time DBA positions open on my team, one of which is for a Sr. DBA. We...

Programming : Service Broker

Slow performance - Hi, I have a problem in SQL2005 so after several days of work without problems, suddenly takes a very slow (sqlserv.exe...

Programming : Powershell

Powershell for network path - I am totally new to powershell(just 2 days) so please bear my ignorance.Here is the code I am trying to...

SQLServerCentral.com : Anything that is NOT about SQL!

Crazy Interviews - Reading the topic 'Stupid Interviewer Tricks' reminded me of an interview where I couldn't tell if I was crazy or...

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 Handle a column with Length more than 8000 characters - Hi, I have a report which consists of 40+ columns. one of the column's length is more than 8000 characters. Report is working...

Reporting Services : Reporting Services 2005 Development

financial report ( P&L, Balance Sheets) - I am writing some financial report (e.g. P&L, Balance sheets etc). Can any body please advise me on how I can...

Database Design : Design Ideas and Questions

Indexes with datatype char - Hi guys! I have an question, in my environment I have one table that the most of columns is char datatype. But...

Data Warehousing : Integration Services

Execute Process Task Arguments - Hi all, I currently am downloading zipped csv files from our source. As part of the process I am to have...

Data Warehousing : Analysis Services

Dimension processing error - Hi, I'm trying to change the source of one of the dimensions in a small cube. This particular dimension will henceforth be...

Data Warehousing : Data Transformation Services (DTS)

Cannot view/Edit DTS, BLANK DTS appear - When I try to open DTS packages I received this error:'MMC has detected an error in a snap-in. It is...

Notification Services : Administration

Multiple sqlservr.exe on Task Manager - I have only one instance of sql, MSSQLSERVER but on Task Manager, there are 2 sqlservr.exe running. Anyone have any...