This editorial was originally published on Nov 22, 2013. It is being re-published as Steve is out of town.
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.
The industry standard for comparing and deploying SQL Server database schemas
Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial
How to track every change to your SQL Server database
See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more
SQL Server 2017 is now on general release. The latest version of the heavyweight platform is more than the sum of its parts, however, because it doesn’t just deliver new functionality. Alongside the list of extra features, it also changes two important ways we think about the platform itself. More »
Azure SQL Database provides a number of benefits that leverage resiliency and redundancy built into the underlying cloud infrastructure. In addition to Point-in-Time Restore as well as Geo-Restore and Active Geo-Replication, you have the option of implementing long-term backup retention of Azure SQL Database. More »
SQL Server T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server. This edition has been lightly updated for SQL Server 2014 and provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, create in-memory tables and stored procedures, insert and update data, generate reports, secure your data, and more. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Steve Jones):
My system has the MAXDOP set at 8 to prevent a query from using all 16 cores. For my index rebuild, I want this to complete this as quickly as possible overnight and use all 16 cores for only the index rebuild. What is the easiest way to do this?
Answer: Use ALTER INDEX ... REBUILD with (MAXDOP=16)
The MAXDOP can be overridden for queries and index operations with query hints. In this case, the WITH (MAXDOP=16) will override the instance setting.
Ref: Configure Parallel Index Operations - click here
Ideally, when we run the database consistancy check, we should get notified if consistency check error. To fulfil this requirement, i have created a script which will create a stored procedure which will execute database consistency check on database and email the status of DB consistency check.
This script will perform below task
Script will create a parameterized store procedure named "spDB_Consistancy_Check"
Stored procedure will accept parameter “@DatabaseType”. Parameter will be either 'S' Or 'U'. 'S' = be System Databases U = User Database.
Based on the parameter, stored procedure will perform consistency check on databases.
When procedure completes consistency check, it will send an email to notify the status. If consistency check returns any error than it will display error in email body or just say "No database consistancy error found".
How to Configure:
we need to replace 2 parameters in script before creating stored procedure
Replace @Email_Profile parameter with valid database email profile
Replace @Recipients parameter with valid email recipients.
set @EMail_Profile='DEMO_Mail_Profile' --Replace this parameter with organization's email profile
set @recipients ='email@example.com' --Replace this with valid recipients
How to use:
As mentioned in above code replace the parameter and execute the script on any database. This script will create a stored procedure and it can be executed by SQL Server agent job or we can run it manually.
Exec spDB_Consistancy_Check 'S' --S = System database
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.