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

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.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
SQL Compare

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

SQL Source Control

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

Featured Contents

 

Additional Data Files and Proportional Fill

David Fundakowski from SQLServerCentral.com

Additional files can be added to to a database to increase the storage. When more data is added to the files, Extended Events shows how SQL Server spreads data across multiple files. More »


 

Why it’s time to think seriously about SQL Server 2017

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 Long-Term Backup Retention

Additional Articles from Database Journal

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 »


 

From the SQLServerCentral Blogs - SQL Clone Server Service Permissions

Steve Jones from SQLServerCentral Blogs

SQL Clone is amazing, and it can really save time and disk space for many organizations. I’ve got a series... More »


 

From the SQLServerCentral Blogs - A virtualized CPU forced me to eat my lunch early, every day, for weeks

Ed Elliott from SQLServerCentral Blogs

I worked one particular contract where I was forced to take my lunch at 11:35 every day, and it was... More »

Question of the Day

Today's Question (by Steve Jones):

In working with a dictionary in Python, what syntax creates a dictionary, and what syntax accesses an index?

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 1 point in this category: Python.

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

SQL Server T-SQL Recipes

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

Yesterday's Question (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)

Explanation:

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


» Discuss this question and answer on the forums

Featured Script

Script to send database consistancy errors

Nisarg Upadhyay from SQLServerCentral.com

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.

Description:

This script will perform below task

  1. Script will create a parameterized store procedure named "spDB_Consistancy_Check"
  2. Stored procedure will accept parameter “@DatabaseType”. Parameter will be either 'S' Or 'U'. 'S' = be System Databases U = User Database.
  3. Based on the parameter, stored procedure will perform consistency check on databases.
  4. 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

  1. Replace @Email_Profile parameter with valid database email profile
  2. Replace @Recipients parameter with valid email recipients.

E.g.

set @EMail_Profile='DEMO_Mail_Profile' --Replace this parameter with organization's email profile
set @recipients ='nisargupadhyay87@gmail.com' --Replace this with valid recipients

How to use:

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

Demo Code:

Use LABDB
go
Exec spDB_Consistancy_Check 'S' --S = System database

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 2017 : SQL Server 2017 - Administration

transactions per second multiple databases - Hi, We have a server with 300 databases and need to know correctly the tranactions /second of each database  for the...

latest timestamp multiple databases - Hi  dbas can you tell me  a quick way to check timestamp on multiple user databases in a SQL Server Instance. For...


SQL Server 2017 : SQL Server 2017 - Development

StartDate and EndDate in same column - I have a date column below RunDate , I want to create two column StartDate and EndDate like this StartDate = '2004-08-01' ...


SQL Server 2016 : SQL Server 2016 - Administration

Send Job Alerts to User Base - Morning All, So this place is very job-centric, alot of the processes happen overnight and as jobs they run just fine. What...

HA and DR Solution - Based on the discussions that we had internally we initially decided to move with a SQL FCI for our non...

Migrate SQL Server to new server - Hello All, I would like to upgrade my SQL Server 2012 to SQL Server 2016. I created new machine and i would...

REporting Services: (Bing-) Map shows HTTP Status 403: Forbidden - Hi! we created a Report with the integrated Bing maps. On our development-Server, inside Visual Studio, the Report works, the map...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Error while Storing Results of a stored procedure into a temp table -- Using SQL server 2016 - I have a stored procedure which uses CTE and a temp table. I want to store the results of this...


SQL Server 2014 : Administration - SQL Server 2014

Perfmon does not start for named instance, but does for default instance. - Version in use is SQL Server 2014/SP2 CU5. I have encounted an error that others have also encountered about perfmon counters...


SQL Server 2014 : Development - SQL Server 2014

Huge number of inserts vs number of reads - Hello to all I have a system that is getting 24 hours a days xml files to import. Each of these...


SQL Server 2012 : SQL 2012 - General

Collect data en show it in a graph - Hi all, I'm not sure this is the right place for my question, but I'll give it a try: Ik looking for...

CASE Statement - I need to generate a adhoc report. I need to query a time range(calltime) that is stored as char(5). Querying...

Where should I save stored procedures and user defined functions for use in all databases? - I have some generic utility stored procedures and user defined functions that I would like to define/save once, and have...


SQL Server 2012 : SQL Server 2012 - T-SQL

How to find the first recurring character of a string. - I have a query that finds the first recurring character in a string. For eg : if @str = 'ABCCDA' then i...

PURGING DATA - HI All, I have an assignment, to Purging data, with conditions: 1. Keep the full data 6 months (keep all the records...


SQL Server 2008 : SQL Server 2008 - General

Changing NVARCHAR to VARCHAR... - Dear Experts, As part of tuning on a database , I want to reduce the high physical reads by reducing the number...


SQL Server 2008 : T-SQL (SS2K8)

Join two tables with complex derived keys that don't match - Guys, I need to join two tables together - Master and Detail Master Table: ID =7000776601*1*6994 Code=P*W*S Instances=6994ý12510ý12874ý13245ý13609ý13973ý14337ý14701ý15009ý15072ý15436ý15800ý16164ý16528ý16899 Details Table


Data Warehousing : Analysis Services

Design considerations for a tabular model - I have a star schema already in existence (SQL Server fact table and numerous dimension tables). For a given tabular...


SQL Server 2005 : Data Corruption

DBCC CHECKDB with Msg 8921, Msg 824 - my database has 20 tables. out of which 8 tables is opening but the remaining 12 shows error no backup...


SQL Server 7,2000 : T-SQL

using DOS START command inside of xp_cmdshell - What I want to do is to issue a START command inside the xp_cmdshell.  This should start a new cmd process with whatever...

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