In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Need to share database changes?
Keep database dev teams in sync using your version control system and the SSMS plug-in SQL Source Control. Learn more.
 
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.
 
SQL Training Learn Agile Database Development Best Practices
Agile database development experts Sebastian Meine and Dennis Lloyd are running day-long classes designed to complement Red Gate’s SQL in the City US tour. Classes will be held in San Francisco, Chicago, Boston and Seattle. Register Now.

In This Issue

From Hairdresser to DBA – How to start a career in IT

In writing about his career change from hairdresser to DBA, Greg Lucas shares his experiences and offers advice for those looking to move into IT. More »


Objects, Relationships, Systems, And Processes

What is the difference between an expert DBA and a Master DBA? This piece from William Talada talks about Objects, Relationships, Systems, and Processes and how they may relate to your job as a DBA. 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 »


SQL in the City - Boston 2012

A free day of training in Boston on Oct 8, 2012. Come join Grant Fritchey, Steve Jones and more to talk about SQL Server and how you can work more efficiently. More »


Editorial - Mining and Algorithms

When we publish articles that look at the performance of T-SQL code, the pieces are very popular. It seems that people are always willing to learn how to write better code, and there is no shortage of people willing to discuss the methods, the results, and the implications of various techniques. It also seems there is no shortage of argument and debate that take place in the discussions as to the best way to solve problems.

However we don't really understand what will resonate with people, or what things they'd like to learn. What we would like is a system like Amazon's recommendation engine that might help us improve traffic, and also bring you information that is more relevant and interesting to your career. If you've ever purchased something from Amazon and then browsed their site, you might notice the recommendations appearing all over the site. If you've received an email from them, you might see some interesting items suggested to you.

Amazon knows their engine is valuable, and it's constantly being refined. We don't know exactly what they use to build the recommendations, but they are constantly analyzing the results and tweaking their system. That makes sense for their commerce oriented business, since they can easily measure the results by looking at sales, but it might also make sense for other businesses. Many of us have reports, and other queries used to perform analysis on our data. Do we regularly analyze the performance of those algorithms and look to tweak them?

I've found many employers in the past were content to develop a system and leave it alone until some new requirement arose. One company didn't like that approach, and regularly looked to tweak our systems to try and get more performance out of an inventory analysis application. It was frustrating to regularly rewrite a series of reports, but also challenging to try and find ways to solve a complex problem.

As more and more data professionals begin to work in the analysis and business intelligence areas, learning to build algorithms, and tweak them over time, will be more and more important. It will be challenging, but it should be fun if you are excited by the challenge of improving your algorithms over time.

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

A database is in the bulk-logged recovery model. I am inserting data into a table using bulk import. At the time the bulk-import operation started, this table has one clustered index and the table already has data. In this bulk import operation, data and index pages are minimally logged in the transaction log.

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

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

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

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Yesterday's Question of the Day

What is the output of both the queries on a database using case insensitive collation?

CREATE TABLE dbo.Zip_code
    (
    id    VARCHAR (10) NULL,
    Descr VARCHAR (max) NULL
    )
GO

INSERT INTO dbo.Zip_code (id, Descr)
VALUES ('1111', 'AAAA')
GO
INSERT INTO dbo.Zip_code (id, Descr)
VALUES ('2222 ', 'bbbb')
GO
INSERT INTO dbo.Zip_code (id, Descr)
VALUES ('aaaa', 'bbbb')
GO
SELECT count(*) FROM zip_code WHERE id = '1111'
SELECT count(*) FROM zip_code WHERE id = 1111

Answer: 1,error

Explanation: For the code:

SELECT count(*) FROM zip_code WHERE id = 1111

This will only work if your id column has all numeric values. If a single value contains a character then the code will return an error:

"Conversion failed when converting the varchar value 'aaaa' to data type int. Severity 16"

» Discuss this question and answer on the forums

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Ace your preparation for Microsoft® Certification Exam 70-461 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with practice tests on CD—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

  • Create database objects
  • Work with data
  • Modify data
  • Troubleshoot and optimize queries

You also get an exam discount voucher—making this book an exceptional value and a great career investment.


Featured Script

Check SQL Job timings

This script is useful to check how long jobs have been running. 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

Fix orphaned users created WITHOUT LOGIN. - So I have restored a database to a new server and a check reveals a DB-user being orphaned. However, this...

What is the meaning of view is not schema bound? - Dear All, I have created following view named FDIP_ITEMS :- CREATE VIEW FDIP_ITEM AS SELECT DISTINCT A.ROWID,A.A_CODE,A.I_CODE,'P02' PHP,'NOS' UNIT FROM PHP_STOCK A...

Deattaching a replicated subscription database - hi, what is the best way of get rid of the log file from subscription database. it is growing big in...

Help!... Memory Use on Windows 2008 R2, SQL Server 2005 x64. The physical is 98% - Hello to everyone, first... sorry for my english... I'm from Colombia. I have a situation that i can't understand, a SQL...

SQL Server 2005 : Backups

Backup is taking much time - Hi Experts, Today one of my database took more than expected time for backup i have no idea why it took...

SQL Server 2005 : Business Intelligence

OLE DB Source blocks its own destination - Here's an odd one. It just started showing up two months ago. We have a daily-running package that loads an...

SQL Server 2005 : Working with Oracle

Excel Pivot Table Show Zeros For Numeric Oracle Data Via SQL Linked Server - I have a view in Oracle 11G. I want to show the contents in an Excel 2010 Pivot Table without...

SQL Server 2005 : SQL Server 2005 Compact Edition

Minmize Down Time During Data Type Conversion - EDIT: Sorry, posted in wrong section. Updating.

SQL Server 2005 : SQL Server 2005 General Discussion

Needed database level permissions script for whole instance - Hello, I cannot seem to find a script like this which would produce the users and their DATABASE LEVEL permissions like...

OLE Automation in T-SQL - Hello, I have been trying to find any resources about OLE Automation in T-SQL. The problem right now is: I have...

NEED SSIS,SSAS Coding Standard - Hi Frnds I am looking for Some Coding Standards, that we need to keep in mind for implementing MSBI Project. Any Standards...

Reg: Migration of Crystal reports to Sql 2005 Reporting Services - Hi, i am working with web based ERP which was developed in .NET, and the reports we generated for our clients...

SQL Server 2005 : SQL Server 2005 Strategies

Multiple parameters for a query - I receive a flat file which I am importing with SSIS. The problem is the file does not contain a...

SQL Server 2005 : SQL Server 2005 Performance Tuning

INDEX REBUILD - Here is what I did 1. Ran the following command ALTER INDEX ALL ON APPOINTMENTS Rebuild; 2. Ran the following query select index_id, avg_page_space_used_in_percent,...

SQL Server 2005 : SQL Server 2005 Integration Services

How to execute a set of T-SQL statements from a given folder using SSIS? - Can someone help with an example for executing a set of T-SQL stmts from a given folder using SSIS ? Please

SQL Server 2005 : T-SQL (SS2K5)

Unable to login users - It is an 8 years project.I am working on this project since 1 year.ther is a stored procedure to verifiy...

Tricky REPLACE problem - I've been poring over how to handle this for a few hours without using some kind of horrifying loop, and...

Switch rows and columns - Hey all, getting a bit of friday afternoon brain freeze ... Say I have this data: [code] CREATE TABLE #Test ( ID INT IDENTITY PRIMARY...

Performance of Joins over Updates - I was wondering if anyone knows which is faster, to build data for my reporting table.. To write new data using...

xp_cmdshell - Procedure expects parameter 'command_string' of type 'varchar' - Here are the contents of @Cmd (varchar(max)): bcp "SELECT [Data] FROM myserver..dbo.PEC_Citrus_RptData where [Data] is not null order by [Data]" queryout...

SQL Server 2005 : SQL Server Newbies

column to calculate the balance stock dynamically - I have a table as shown below and would like to have the balance quantity which should be dynamically calucalted...

SQL Server 7,2000 : Administration

BKP job against the database : BizTalkMgmtDb - Hi, Can anyone please update me, once we run a backup job against the biztalk and other user databasesd using the...

SQL Server 7,2000 : SQL Server Agent

How can i make the data transformation in DTS to do an update instead of insert? - Hi guys, this is the dilemma im in now. i have to create a DTS package that instead of doing the...

Ideas welcome on SSIS jobs, Agent and kicking off another SSIS job - Hi. I'm looking for some ideas. Currently I have 3 SSIS packages which are scheduled in SQL Server Agent. The run...

SQL Server 2008 : SQL Server 2008 - General

Store Procedure Requirement extraction - Hi I have create a store procude with Application Id as Input parameter, and make few select statement on my store...

Replication between different databases - Hi all, I was wondering if anyone has had any experience using replication in the description text below: Detailed below is a...

split column - Hi, I'm trying to split a single column into a number of columns, with the split being the delimiter of...

queries running faster after cached in the query plan - Hi Probly a quick on to answer Is it the more times you run a query the faster it gets? or is...

UNION Help - I have a database that contains a number of individual tables for each sales agents. I need to find a...

sql server 2008 10.0.1600.22 patch 64bit - need patches for sql server 2008 10.0.1600.22 patch 64bit...

Migration from SQL 2005 to SQL 2008 - Hi My friend wants to migrate his database in SQL 2005 to SQL 2008.He has some SSIS packages running on his...

Upgrade to 2008R Collation - Hi All, I am waiting for project in whcih I need to upgrade sql server 2000 to 2008R2. The main...

Parameter error - Hi All, I am week in write the query. Below query i am executing for upload the data to s3...

Create a query that function as Vlook up in Excel - Hi guys, I'm working on this query and the table looks like this Item id status sub_item_id 10001 NNU no_sub 10002 Overstock...

SQL 2008 express DB Mail - I have SQL 2008 Express installed and running under windows 2008, with smtp service running as well. The mail could not...

image insert into SQL DB - Hi, What I have to do is creating a SQL image DB but instead of inserting the images themselves into it...

SSIS runs in debug but not from job - We have this SSIS where one of its components, reading a flat file and writing to a database table, runs...

Update Statistics - Hi every body Updating statistics causes queries to recompile. first time that i run a query with a parameter,the query optimizer make...

Help with SQL Query - Hi, I have a table which looks like : Name Region City Status N_Region N_City N_Status E_Region E_City E_Stauts Peter AMERICA NY A...

SQL Server 2008 Standard Installation failed - SQL server 2008....stand alone. Windows 2008 r2 SP1 I have been trying to install a SQL server default instance basically through out...

Need help on select sceanrio - I have one config table which has my where clause which is needed in my select statement. E.G Select * from MyTable...

ASYNC_NETWORK_IO wait type on stored procedure that has finished giving the results to the web application and that is blocking another stored procedure - Greetings, I am a developer and am not a DBA or a Networking Engineer. I am just a lowly Developer. Please...

Check that a specific Group By condition does not exist - [code="sql"] CREATE TABLE #tblBlocks ( TemplateID int, BlockID int, FieldID int, BaseStage bit ) GO INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage) SELECT 1,...

Default Language Setting of Database - Hi All, Our default language setting for a DB is set to US-english , but our client insists now that it was...

How to group time into a range like 9:00-10:00 AM,10:00-11:AM etc in SSRS charts??? - Hi All, I have a trend chart showing total services per 1 hour time slot across the day. But I wanted...

Passing comma separated values for IN list in DELETE statement from SQLCMD - Hello, Following are the contents of script Delete_employees.sql. [quote]USE CompanyDB BEGIN TRAN DELETE FROM dbo.employee WHERE empid IN ([b]<Comma separated values passed from SQLCMD>[/b]) COMMIT TRAN;[/quote] [b][u]Question:[/u][/b] Will...

Weird Requirement... Multiple Left Joins? Am I missing something? - Ok, so I think I should know the answer to this but I'm getting nothing but what looks like a...

SQL Server 2008 : T-SQL (SS2K8)

Storing images in database - Hi, Just for the sake of information i was learning how to store data into database. I came across 2 main...

days into completed weeks only - Hi how do i easilty convert days into weeks (obviously Days/7 will give me weeks as decimal) Problem i have...

output ve space while printing in sql server 2000 - alter procedure Goals_history ( @stuid nvarchar(22), @startyear nvarchar(22), @endyear nvarchar(22), ) as begin select name,class,noofsubject,rank,test from @tab1 union select name,class,subject,rank,test from @tab2...

problem in customising a result set .plz help.. - I am getting the out put of a query as SN NodeName NodeValue 1 Address address1 1 Address address2 1 Address address3 2 city kol 3...

Deleting records from the same table through different sessions - Hi , Consider the below scenario : I have a huge table where records are present based on organizations [org_id] Each org can...

Looping through table - Hi, I have to write a query and i am not sure how to go about it. I have a table...

error while attaching adventureworks in 2008r2 - hi i am getting following error while attaching mdf file in my SSMS. ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement...

Fetching values under condition - Hi to all I have a Query which is fix like SELECT StudentId,EnrolmentId,SessionId,SchoolId, Name, ClassGroupId, ClassId, ClassName FROM ViewStudentTable WHERE IsBlock...

Odd SQL conversion error "August 2012" - This is a really odd issue!! A parameterised SP runs fine on my test database wth a certain set of input...

Order by a column keeping the families together - I have two tables Table A -------- ItemID Date Table B --------- ItemID ParentID Records in Table A may be items with parents (or) items without parents. Some of...

Importing only rows with column data in specific columns. - Hi all - I have an interesting scenario that i would like some input on. I have a requirement to import...

UPDATE when the values are the same - Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating...

Reverse Of Number without Using reverse() - Hi, How will i reverse the number in SQL Server with out using reverse()

SQL Server 2008 : Working with Oracle

Change of source databases(9i to 11g) 0xC02020F6 cannot convert between unicode and non-unicode string data types - Hi, Could someone tell me how to resolve this issue. I changed my oracle source database from 9i to 11g. Everything worked...

SQL Server 2008 : SQL Server Newbies

SQL Cursor Help - I need to amend the below Cursor to output into a table off which I can Query/join the output of...

insert and update problem in stored proc - I want to insert values from a select query into table "Delkk" The select query has the following result: Orderdatum txtKortingBdr Klantnummer...

Join performance gets much worse after combining two selects that are each fairly fast - I have a query that seems to take forever to run. I broke it into parts to try to understand...

Logshipping is not working. - Hi, We have a log-shipping set up on the production server for reporting purpose. Unknowingly, I have set up a backup strategy...

Need help desperately on query optimization - Hi there, I'd a query causing a performance problem on server. The data sets are huge. Any suggestions on optimizing will...

What would the SQL2008 equivilent be to outer union corr? - I'm still trying to get the hang of SQL2008.... I've been using SAS and had no problem joining these tabes...

SQL Server 2008 : Security (SS2K8)

Not View Any DB Other than The One He Has Access - Hi All, My req is that I have to give a user permission to view only the DB he is a...

How to Secure the MDF and LDF File with Password - hi, I've been working on sql server. I want to protect the mdf file with password. I develop a windows application with sql...

SQL Server 2008 : SQL Server 2008 High Availability

Poor Performance - Steps to see which procedure is slowing the performance in sql server 2008? As there are many stored procedure which gets...

setting up Clustering - SQL Server 2008 R2 - I have a couple of questions about installing SQL Server 2008 R2 Standard in a 2 node active\passive cluster First, a...

Using High Availability to move to a new server? - We have an existing windows 2003 server with sql 2008 r2 enterprise. Its part of a Hyper-V configuration. A new windows...

Would a smaller number of VLFs in the log speed up the redo phase of recovery of a mirror snapshot? - From time to time, when an intensive log operation takes place on the principal database, we observe very long snapshot...

SQL Server 2008 : SQL Server 2008 Administration

Memory Leak SQL 2008 R2 64 bit? - OS - Windows 2008 R2 64 bit (no service pack installed on operating system level) SQL - SQL SERVER 2008 R2 64 bit...

collation issue when upgrade to 2008 R2 - Hi All, I am waiting for project in whcih I need to upgrade sql server 2000 to 2008R2. The main...

SQL Server Audit - I have a requirement 1. when a user logon I would need to log the below information in a table....

Single user mode during upgrade to SQL 2008? - IN place method for the database upgrade SQL 2000 to 2008, Can I go for below one of the steps? first database...

Does a tool like "SQL Agent Compare" exist? - Hi everyone, Can anyone tell me about a tool that would make it easy to script out, version, and diff the...

"Client Tools Connectivity" needed? - I'm having problems with getting SQL Server Agent to run after a in-place upgrade. When doing the upgrade, I didn't...

Career : Employers and Employees

Common questions asked in SQL Server DBA Interview - Hi, Can anyone provide me some common questions which are asked in a Ineterview for a DBA post

Programming : XML

Adding New Elements to Existing XML Data - say I've got an XML column containing something like this: [code="xml"]<doc> <heading> <orderid>1</orderid> </heading> <line> <lineid>1</lineid> </line> <line> <lineid>2</lineid> </line> </doc> [/code] Now I...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Grouping Values in a Report?? - Hi, I have an SSRS report to build that looks like this. Name Count Total BIKE COMPANIES 50 3000 HELMET COMPANIES 10 200 The problem...

Data Warehousing : Integration Services

capture oracles's (sequence.nexval) value in a column during data flow... - Hi all SQL server techies.. I have the following scenario I am doing an insert from sql server table to...

Tab Delimited file handling - Hi, I am new to Integration Services technology. In SQL 2008 R2 Integration Services, We will be given Tab delimited Text files...

Integration Services contain Analysis Services Processing Task. Fails but works in Analysis Services - The dimensions have processed Successfully prior to the updating of the SEN Cubes The attribute key cannot be found when processing:...

SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excel - Just google "derived column dt_ntext to dt_str not working" and you will find numerous cases where people have difficuilties importing...

Data Warehousing : Analysis Services

Help in MDX or calc Members - Hi, I have 2 dimensions prod and prod_warr 1) prod-> which has list of prod's prod table schema prod_id,Name ------------ 1,a 2,b 3,c 2)...

Microsoft Access : Microsoft Access

Can Access FrontEnds run T-SQL/XML - Hi guys, I'm not knowledgeable about XML or T-SQL but do you know whether it's possible to use syntax such...