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

Core T-SQL

Often I find myself wondering about the minimum bar we expect people to clear to be competent in some field. Almost all fields are evolving these days as technology and new ideas are put into practice in medicine, art, construction, law, etc. It seems as though a Renaissance is taking place with the speed and variety at which new information is spreading, usually due to advances in technology.

In our field, working with SQL Server, we certainly see new ideas and enhancements taking place all the time. However there are also a number of core skills that evolve, but at a slower pace. For example, T-SQL grows with each new version of SQL Server, but the core language remains, and many people continue to use the knowledge they have had for years when writing code.

This week I'm curious if we can debate about, and compile, a list of core skills with T-SQL that we think someone ought to understand to be considered competent as a database developer. I'm asking:

What core skills should someone have with T-SQL?

I'll start the list, but feel free to add to it or give me your thoughts. I think someone ought to be able to understand these items and write code to solve problems that involve:

  • finding duplicate rows (grouping, joins)
  • returning aggregates of single or multiple columns (MAX, MIN, SUM, COUNT)
  • return aggregates in groups, or islands. (grouping and aggregates, windowing)
  • join multiple tables together on matching, multiple columns (joins)
  • find data in one table that doesn't have matches in another (outer joins)
  • filtering data (WHERE)
  • subqueries and complex CTE joins of data
  • create row numbers and join back to a table without numbers (APPLY)
  • pivoting data from rows to columns

That's a starting list from a number of questions I've seen, but feel free to add your own skills you think people need.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

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

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

Steve Jones

Windows Media Video ( 20.3MB) feed

MP4 iPod Video ( 23.4MB) feed

MP3 Audio ( 4.8MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
SQL DBA Bundle

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

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

SQL Search

How do you search your database schema?

"I use SQL Search regularly and think it's great." Gregor Suttie, Senior Software Engineer, Pulsion Technology. Download Red Gate SQL Search while it's free.

SQL Monitor Hosted

Try new SQL Monitor Hosted

Get real-time server performance updates and instant access to the data you need to fix the problem, whilst we take care of the monitoring software. Get started now.

Featured Contents

 

Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Jonathan Kehayias from SQLServerCentral.com

Learn how to use SQLCLR to get file system information instead of using xp_cmdshell on your SQL Servers. More »


 

Script to Set the SQL Server Database Default Schema For All Users

Additional Articles from MSSQLTips.com

You may come across or even inherit a system where many of the users have different default schemas. Usually, this is easy to remedy - you go into Management Studio, right-click the user, and change the default schema through the UI. Or you write an ALTER USER command manually. But what if you have hundreds of users, across all of your user databases, that should all have the same default schema? More »

Question of the Day

Today's Question (by Steve Jones):

From which DMV in SQL Server 2012 would I query to get a list of the server roles?

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 2 points in this category: Security.

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

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

Which of these wildcard characters is used to match a single character? (Select 2)

Answer:

  • []
  • _

Explanation:

Both the [] and the _ can be used to match a single character. 

For example, run this code:


WITH    myCTE ( mychar )
          AS ( SELECT 'Steve'
               UNION ALL
               SELECT 'Andy'
               UNION ALL
               SELECT 'Adam'
               UNION ALL
               SELECT 'Bdam'
               UNION ALL
               SELECT 'Pteve'
               UNION ALL
               SELECT 'Billy'
             )
    SELECT mychar
        FROM myCTE
        WHERE
-- mychar like '_dam'
            mychar LIKE '[a-z]dam'
Comment and uncomment each line of the WHERE clause and you'll see both filters can match a single character.
Ref: 

» Discuss this question and answer on the forums

Featured Script

Automate Data Purity Investigation

Richard Fryar from SQLServerCentral.com

A couple of weeks ago I wrote an article at www.sqlcopilot.com/dbcc-checkdb-with-data_purity.html about the DATA_PURITY option of DBCC CHECKDB, and how to identify the affected columns.

One of the ways to find affected columns is to run a SELECT with a WHERE clause to return out-of-range data. But this doesn't always work. It is possible for data to be within a valid range but still fail the data purity check. It is also possible for the column to be totally corrupt, resulting in an arithmetic overflow error when you attempt to SELECT it.

The method I showed in my article for when a SELECT doesn't help was to use DBCC PAGE. But if you have more than a few columns with invalid data, it can be quite time consuming to do. This was the case for me recently when I migrated a database from SQL Server 2000 to 2008 R2 and the CHECKDB found 540,000 data purity errors!

A SELECT for values outside the range for the datatype (decimal(23, 8) in this case) didn't return any data and so DBCC PAGE was the only option - but obviously there was no way I could run it manually 540,000 times!

The script attached to this article was my solution to the problem.

1. It runs DBCC CHECKDB(dbname) WITH DATA_PURITY, NO_INFOMSGS, TABLERESULTS and captures the results in a temporary table

2. It extracts the page, slot, object id, column name and data type for each row returned

3. It loops through the results perfoming a DBCC PAGE for each one to get the primary key values of the rows containing the invalid data.

Within this loop, there is also code to derive the condition for the primary key (allowing for multi-column keys). The key value is then used to query the table to get the current value of the affected column. The results are stored in a table, tmp_final_results. This can be dropped once all investigation is complete.

There is further code, commented out, that I will explain shortly.

How to use the script

This is a 3 stage process.

1. First run the script within the context of the database to check.

It may take several hours to run, especially if you have a few hundred thousand data purity errors.

When it has finished, the table tmp_final_results will contain a row for each out-of-range column.

2. Use the first commented out section of the script to view the results.

Note: the conversion of [Value] to varchar is necessary, as attempting to retrieve some out-of-range data may result in arithmetic overflow errors. By converting them to varchar, these are displayed as -1.#IND instead.

3. Fix the data

Now you have to decide what to set each one to.

If you are lucky you may find that all the values are within a valid range for their datatypes, so a straightforward UPDATE to their existing value will fix the problem. The third section of my script (also commented out) generates an UPDATE statement for each column.

However, you may find out-of-range values, and some may be displayed as -1.#IND. For these you have to decide what they should be set to, and this means speaking with someone who knows the application well and getting them to look at your data.

4. Tidy up

The final commented out section needs to be run to drop the tables generated by the script.

Please remember that this script is a tool to aid in the identification of columns that have failed a data purity check. You should not blindly update the columns - ensure you are 100% confident of the correct values. However, the code in section 3 of the script is available if you decide the suggested values can be used. And of course, backup the database before making any data changes.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2014 : Development - SQL Server 2014

Data type validations - Hi, I have two columns in the same table One is data_value (varchar) and the other is sql_data_type (varchar). Data_value holds info...


SQL Server 2012 : SQL 2012 - General

SSMS Upgraded a sql script project/solution from 2008 to 2012, can I downgrade it back to 2008 now?? - So we were testing out some stuff and I ended up having to install 2012 on my dev machine so...

Connecting to an AOAG / HADR database - Does anyone have a good link, or even a simple list of items, indicating best practices for apps connecting to...

Management Studio question - holding files on next SSMS open - Hopefully this is a simple question. I updated something/an option in my 2012 SSMS. When I close SSMS I am...

Log shipping failing all of a sudden - I have several databases on a 2012 instance that are mirrored to a second server, and log ship to a...

Best Tools for Monitoring a Large Estate - What is the best 3rd party tool for monmitoring a large SQL estate.

SQL 2012 Dev Edition - Windows 8 SQL Server 2012 Developer Edition Clean install on laptop, no previous versions of SQL Server Absolute pain in the nether regions...

Copy logins through Database Copy Wizard - I copied logins from 2005 to 2012 through Database copy wizard. All the logins copied over correctly. But none of...

Sql 2012 Always on vs Neverfail - Hi, I am currently running a SQL 2005 Standard failover cluster with some 250 databases. I also use mirroring for...

SSAS tabular mode Vs SSRS - Hi, If we use SSAS Tabular mode, then don't we need to use SSRS? So end users directly user SQL Data tools...

Is this a Kerberos issue? And if so any suggestions on how to fix it? - We've started testing DNS Aliasing to our SQL Servers, and on one new server we setup a DNS alias SQLSALES...

Collation error when adding distributer - I'm trying to set up Replication, and the first step is adding the distributer. I run the code: [code="sql"]use master exec sp_adddistributor...

Using SSMS 2008 with SQL 2012 - I'm struggling to adjust my practices to the changes in SSMS 2012. There are a lot of things that I...

Not getting the email notification that I set up, part 2 - I've set up an email profile in Database Mail, and tested it. It seems to work when I run it...

Warning: Could not open global shared memory - Hi all, Apparently this error was fixed in CU12 for SQL 2008, but it seems to have raised it's head again...


SQL Server 2012 : SQL Server 2012 - T-SQL

Read all values from an xml node - Hi All, I have the following code and trouble reading values of Bank Accounts. If i remove the line it says...

query to return top 10 tables on each db on server - i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable...

Stored procedure - how to return a single table - Hi :-P I have this SP [code] ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int) AS DECLARE @steps bigint SET @steps = DATEDIFF(hour, @start, @stop)/ @step DECLARE @i...

A "special" stored procedure problem - Hi guys! I'm not saying that actually have a problem... :-P I have this table: ID(int) | START(datetime) | STOP(datetime) lets call it...

2 records at a time? - Hi everyone, I'm not even sure how to word this succinctly enough to search for this topic, so I apologize in...


SQL Server 2008 : SQL Server 2008 - General

calendar from table - I have table : ID Date Location 1 2013-11-01 H1 2 2013-11-02 H1 3 2013-11-01 H2 4 2013-11-02 H2 and i need to create view calendar like the attached but the...

CONVERSION Issue - For following date im using 10/15/2013 10:17:26 am Data going in Oracle: (DT_STR,75,1252)((SUBSTRING(Date,1,4) + "-" + SUBSTRING(Date,5,2) + "-" + SUBSTRING(Date,7,2) + " " + SUBSTRING(Date,9,11) + ":" + SUBSTRING(Date,11,13) + ":" + SUBSTRING(Date,13,15))) Getting the error: [Oracle Destination [1867]] Error:...

How to change our license from cal to core ? - We have a Standard SQL 2008 R2 server which currently has a client access license (CAL) and we need to...

Which way is better in SQL Paging (With or TempTableVariable) & Why ?? - Which way is better in SQL Paging and Why ?? [b]With[/b] [code="sql"] WITH MyCte AS ( SELECT EmployeeID, EmployeeName, Age, Position, Address, PhoneNumber ,ROW_NUMBER() OVER...

Create Login Only - How can you allow a user(login/server principal) to only CREATE other logins? I want to setup a user(server principal) to add...

Error In Derived Column - Hi, Im moving csv file to oracle database and i am converting the date column as datetimestamp LEN(Date) > 0 ? (DT_DBTIMESTAMP)(SUBSTRING(Date,1,4) + "-" + SUBSTRING(Date,5,2)...

Import Export Wizard mapping files - Hello experts, I am running Microsoft SQL Server 2008 R2 (SP2) 64-bit on Windows 2008 R2 SP1 and having troubles...

SSRS URL Login Issue - Hi Guys.. I have just install SSRS 2008R2 Ent on app server and configured Database on different server. But when...

DETECT ROWS CHANGED IN A TABLE to transfer in a datawarehouse - Hi all. Every day, I need to transfer data from SQLServer in csv format. I need to identify wich are the...

Looping through multiple dbs in a view - CREATE VIEW [dbo].[VW_PERSON_DETAIL] AS SELECT p.PTNO, p.[NAME], p.SSN FROM table1 p INNER JOIN table2 c ON p.idcode = c.idcode WHERE c.RV = 1 I...

Update field based on condition - Hi, I have the following table [code="other"] CREATE TABLE [dbo].[Orders]( [orderNo] [varchar](10) NOT NULL, [orderLineNo] [int] NOT NULL, [product] [varchar](10) NULL, [orderQty] [int] NULL, [receivedQty] [int] NULL, [orderStatus] [varchar](10)...

SQL Query Performance - Can any one spot anything in the given below query that can be done to improve the performance of query........

Design SalesRep hierarchy - Hi, I need to design SalesRep hierarchy. What is the best way to design the tables? SalesRep might be under a...

Restoring .BAK - long story, so i'll keep it short - Was using Windows Authentication to connect to the server. The Windows Account was...

Creating index for all Foreign key columns!!! Please help - Hello All, I have a requirement to create primary key for tables with no primary keys and to create index...

Is security issue? - I have 2 develop PC, A and B in which SQL 2008 R2 were installed. I want to move ORDER database...

Where can i find my JDBC Connect String and JDBC driver class - Hi , I am creating a profile for Dbsync for my Database.For validating the database it is asking for JDBC Connection...

SQL: BACKUPS:: - Hello team, [u]Transactional backup is the topic. [/u] I have over dozens of databases that get backed up automatically (jobs) once...

Copy-only backups and differentials - Please consider the following scenario: Day1: FULL DB Backup Day2: Differential Backup Day3: Copy-Only Full Backup Day4: Differential Backup am I right in thinking that...

Before deleting/detaching database, Why is it necessary to close all other tasks/documents? - I faced a problem while detaching and deleting a new database.. I had to close all other processes and tasks(e.g-...

Linked server/ very restricted access - We have a user who has access to just one view on one database, and he needs to create a...

Pushing inserts into SQL server with C# ? - I want to insert about 5000 (typically, can be 10K+ at times) rows into SQL server at a time. My...

SA login question. - Good day, I found an issue where I am unable to log on the the local instance with my SA credentials...

Changing the SQL Server license key - SQL Server 2008 R2 with SP2 on WIndows 2008 R2 Recently, i have installed a SQL Instance (2008R2 Standard edition x64)...

RedGate SQL Monitor Value - Quick question: Is SQL Monitor from Redgate worth the money? I've never used it, but I wanted to know other's...

Cursor fetch loops endlessly - I am having a cursor which I use to update a 2nd table containing unique IDs from an identity column. The...

The transaction log for database 'tempdb' is full. Msg 9002, Level 17, State 4, Line 2 - hi All, I found this Error The transaction log for database 'tempdb' is full. Msg 9002, Level 17, State 4, Line...

Converting User SID Binary to String TSQL - Hello All, Basically I'm trying to use TSQL to convert user SIDS from Binary to String and visa versa but I'm...


SQL Server 2008 : T-SQL (SS2K8)

removing lettering in front number in field - Hi Everyone I have a field with a mixture of number and letter and I would like to remove all...

How To Get week range of week number - Hi All, I need help with returning a week range. I need to pass in week number and year from a...

sp_makewebtask is not available in sql 2008 - Any Alternate code for below query, because sp_makewebtask is not available in sql 2008 Pl help USE pubs GO EXECUTE sp_makewebtask @outputfile = 'C:\WEB\MULTIPLE.HTM', @query...

Best Way to Create Multiple Columns from A Single Column - In the world of dynamically resizing HTML tables according to the number of elements, I have to believe there's a...

Count for each account no -parent and childsql - Hi Guys I have a data where there are parent and child product. Each parent has got a unique code(P1) and...

Pivot All Columns - Hello Everyone I am playing around with Pivot. But I am not sure if this particular instance is a Pivot or...

T-SQL join to same table n number of times - Hi, I have a table where each row has an item and the item which superseeds it. There can be many...

Rearrange Result of Data with TSQL Script - Hi , I am replicating my problem in otherway . Say , we are tracking record of bus service i.e. how buses are...

Update column based on other column value - I am looking for a way to update a column for only one row for each ID based on a...

How To Pass Multiple Date Values In a Single Parameter(comma separated) - Hi Guys, I need to pass comma separated date values in a single parameter and do a Year on Year...

ISNULL not working - I have a select statement in my existing SP, which is not working: [code="sql"] select IsNull(St.alternateStudyCode, St.studyCode) AS StudyCode,* from Studies...

how to display totals grand total in t-sql - hi, I have a table class classname section Marks first a 800 first a 200 first b 100 first b 200 second a 400 second b 400 first a...

T-SQL puzzler - given a cell in an N-dimensional space, return the cells that are inline with the given cell along each axis - The cells in an N-dimensional space are modelled with the 2 tables below. A script is needed that takes a...


SQL Server 2008 : SQL Server Newbies

I want to be a DB Admin - I'm a graduating student and I want to be a DB admin in the future. Where should I start? I...


SQL Server 2008 : SQL Server 2008 High Availability

"Set Partner Failover" "User must be in the master database"? - I'm trying to switch roles in a mirroring session from mirror to principal using the following statement on the principal. alter...

log shipping setup advise needed - Hi, I was wondering if experts here can help with an advise :-) Let's say I have a site A with...


SQL Server 2008 : SQL Server 2008 Administration

Upgrade SQL Server first (05 std to 08 R2 std) or upgrade OS first (in place upgrade)? - I'm trying to assist our sysadmin with the best way to go on this in place upgrade (I'm almost strictly...

How to detect and troubleshoot slow statements within stored procs - Hi All, I want to know about how to go about detecting and troubleshooting slow sql statement inside stored procedures. For example...

Database file backup - Currently have a database with two files, PRIMARY and FILESTEAM. We only want to backup the PRIMARY file. What is...

Regarding adding another data file on PRIMARY - Need to clarify something. I have this db about 90 gb data file sitting on a 100gb drive. And I am...

Email Notification for Transaction log full - How can we get an email notification when db transaction log is full rather than production getting affected. it would...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

String or binary data would be truncated. Error when inserting empty table results into TEMP table - Has someone ran into something strange like this where no data is being inserted but this error happens. Msg 8152, Level...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Insert Trigger and @@rowcount problem - Performance Issue - In an Insert Trigger this code is sometimes quite slow: "SELECT @numrows = @@rowcount" This code is used to determine if a...


Cloud Computing : Amazon AWS and other cloud vendors

How to Import data from S3 directly into my sqlserver database on amazon EC2 - How can i take text files, or csv files from s3 and directly upload/insert them into a table in my...


SQL Server 2005 : Administering

Change default installation directory for system databases - Hey all, Got a question regarding the installation of a new SQL Server instance. Up until now, what I've been doing...

Smart way to script sql servers jobs - I am wondering if someone has a free ware tool which can connect to given server and script ONLY set...


SQL Server 2005 : Business Intelligence

Replicate SQL Function in a Cube - Hello, I hope somebody can help me, I made a 2012 SSAS Multidimensional Cube, but I need to do a...


SQL Server 2005 : Data Corruption

cannot attach mdf file - Hi, I have the following problem: Due to a harddisk problem the database went into Suspect mode. I don't want to risk...


SQL Server 2005 : SQL Server 2005 General Discussion

Pushing inserts into SQL server with C# ? - I want to insert about 5000 (typically, can be 10K+ at times) rows into SQL server at a time. My...


SQL Server 2005 : SQL Server 2005 Performance Tuning

Maximum stored procedure nesting - I have a stored procedure that is giving me the error: Maximum stored procedure, function, trigger, or view nesting level exceeded...


SQL Server 2005 : SQL Server 2005 Integration Services

Flat file connection having column names in Header - Hi, I'm using a Flat file destination. I 'Check' the column names in the first data row in the connection and...


SQL Server 2005 : SQL Server Newbies

Use SQL Server 2005 to select a column from Excel - Hello, I have a spread sheet that has 300+ columns. I would like to create a loop that will select...


Reporting Services : Reporting Services

SSRS question/option - Afternoon. I'm currently working on a data retirement initiative within my organization. We are retiring the data from 200ish application to...

Migration of SSRS reports subscriptions and datasources from Integrated mode to Native mode - We have a project to move our SharePoint Integrated mode reports back to Native mode. Both servers are SSRS 2008...

Retrieve a Customized Report to many Users - Goal: Every user, who has a AD account shall retrieve a customized and personal report, that is filtrerad data of their...

Hiding detail column - Hi All, I am struggling with a seemingly simple reporting issue in 2008, and was wondering if someone can help. If I...

2008 vs 2012 - Same Proc, different outcomes - Hey guys, I've been tearing my hair out over an issue with SSRS for weeks now (yes, weeks). I've got a...

Jasper Smith's SSRS Scripter - Anyone happen to have this tool handy? I used in the past to successfully migrate a 2005 SSRS instance to...


Reporting Services : Reporting Services 2008 Development

SSRS Column Chart (Bar Chart) needs to appear more clear - I have an SSRS chart with a lot of values on the x axis. I have a requirement that the...

SSRS 2008 use several iif statements together - In an existing SSRS 2008 r2 report, I have the following "IIF(RowNumber(Nothing) Mod 2 = 0, "Gainsboro", "White")" for the background...

changing the spacing for a bar chart in ssrs - I would like to eliminate the empty space between the axis and the bars . Is there a way I can...


Programming : General

Stored Procedure and Parameters Table - To all: I am creating a paramaters table to store values for mulitple sctipts. For example, I will create a Table called...


Data Warehousing : Integration Services

implement SCD type 1 - Hi, I want to load target table with full data from source as it is and after every 1 hour...

Package is not running when set as a job - When I set my SSIS package up as a job, I am getting : Executed as user: RADAC\SC-DEV. Microsoft (R) SQL...

SSIS: Finding Table Used in Other Package(s) - Hello - I'm trying to determine where a particular table(s) that SSIS is loading during a monthly job is being used...

ssis script task throws error when run from sql agent job - When I run this package from Visual Studio, it works fine, but when I run it from the sql agent...


Data Warehousing : Analysis Services

SSAS 2012 - "unable to establish server mode" - OK, installed SQL Server 2012 (SP1 11.0.2218.0) on Windows 8 (what a pain this was). Now after a while I have...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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


SQLServerCentral.com : SQLServerCentral.com Website Issues

Not receiving daily newsletters - Has anyone else stopped receiving the daily newsletters via email? The last one I got was Friday 15th Nov.


SQL Server 7,2000 : T-SQL

Maximum Number of "When Then" lines in a CASE statement? - Does anyone know if there's a maximum number of "When xxx THEN yyy" lines you can have within a single...

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 ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com