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.
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
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.
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.
Stored procedure - how to return a single table
- Hi :-P
I have this SP
ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)
DECLARE @steps bigint
SET @steps = DATEDIFF(hour, @start, @stop)/ @step
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...
- 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 ] Error:...
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
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)...
Update field based on condition
I have the following table
CREATE TABLE [dbo].[Orders](
[orderNo] [varchar](10) NOT NULL,
[orderLineNo] [int] NOT NULL,
[product] [varchar](10) NULL,
[orderQty] [int] NULL,
[receivedQty] [int] NULL,
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
I need to design SalesRep hierarchy. What is the best way to design the tables?
SalesRep might be under a...
- long story, so i'll keep it short -
Was using Windows Authentication to connect to the server. The Windows Account was...
- Hello team,
[u]Transactional backup is the topic. [/u]
I have over dozens of databases that get backed up automatically (jobs)
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...
sp_makewebtask is not available in sql 2008
- Any Alternate code for below query, because sp_makewebtask is not available in sql 2008
EXECUTE sp_makewebtask @outputfile = 'C:\WEB\MULTIPLE.HTM',
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.