What is SQL and Why should I care?

SQL stands for Structured Query Language. Some people prefer to spell it out, S-Q-L, rather than pronounce it sequel [ˈsēkwəl]. SQL is a standard defined and maintained by the American National Standards Institute (ANSI) as well as International Organization for Standards – International Electrotechnical Commission (ISO/EIC). It comes in numerous variants. These are specific to database systems that implement the standard. There are also numerous SQL like languages.

I can tell you are excited to learn more. You should care about SQL if you care about data. SQL primarily functions to describe tables of data. It instructs the database system to create, modify, or retrieve some form of table. If you want to impress someone with your insight into computer science, you could research statefulness and understand how SQL manifests this concept by way of transactions.

If you really wanted to you could write some SQL that it in no way describes a table. Why find those cases just to prove a point that something doesn’t fit neatly into “tables”? If that were your aims why  bother with SQL in the first place? There are plenty of other languages that focus entirely on other data structures.

Understanding tables will help you understand how you can use SQL. SQL has the keywords CREATE, ALTER, and DROP which can be used to make, change, and destroy tables. These keywords can make other objects like functions, stored procedures, and views (users, logins, triggers, audits, connections, … etc.). While technically not tables, they do exist as rows in tables. These keywords can also be used to make indexes which help table operations be performed more efficiently.

Often, a person will be working with tables that already exist. In these cases, the SQL keywords SELECT, INSERT, UPDATE and DELETE will be used to perform CRUD operations. CRUD stands for Create, Read, Update, and Delete. These operations are often being performed by multiple users simultaneously. This is an important consideration.

SQL is declarative. A user need not know how to create locks or latches, nor which algorithms most efficiently sort, sample, or join. SQL allows the user to declare the state they wish data to be in. The database platform determines based on the structures in play, the statistics available, and other factors how best to fill the request.

When operations are performed the database uses a more specific state transformation known as a transaction. In short, a transaction is an all or nothing operation. It allows multiple tables to be acted on such that the affected tables are in either a start or finish state. Transactions are said to be ACID compliant. They possess Atomicity, Consistency, Isolation, and Durability. They are self-contained. The state is either as it was or as it was intended. Transactions operate independent of other transactions. They are permanent once completed.

In short SQL is a language for working with data in database systems. SQL allows you to describe what you want and get it without worrying about a whole host of technical issues best left to software engineers, database architects and administrators.


Where is my SQL Server Configuration Manager?

If you are asking yourself that, you are administering SQL Server 2016 network or services for the first time, or found some dusty old instance that should be decommissioned. In the latter case you are looking for Enterprise Manager, and perhaps someone to accept responsibility for the change control request.

In the case of 2016 you may not have noticed but its where it has always been.

“What? No it’s not. I looked in the SQL Server folder of the start menu.” you say.

Ok. It’s not there but it is still a snap-in for Microsoft Management Console (MMC).

If you never knew that there was a such thing as MMC, you only need a few quick steps to get up and running.

First, you press the Windows key on you keyboard. If you are unfortunate enough to not have one of those you click the start menu.

Next you type MMC. If you notice that a familiar little icon of a toolbox on a window didn’t appear but instead it you highlighted something starting with the letter M, then something else, then perhaps something starting with a C, you might want to think about Windows end of support dates. For now, click run, and then type MMC.

In either case you should be able to launch the MMC.exe console.

Unless, someone configured it for you, the console is going to be this rather uninteresting window:

2017-06-20_20-34-18 Plain Old MMC

If you open the file menu and select add/remove snap-in…. (CTRL+M for keyboardists), you will be presented with the Add or Remove Snap-ins dialog.

2017-06-16_19-45-23 SQL Configuration Manager

From there you can scroll down to SQL Server Configuration Manager. Click add. Click Ok. And now you have SQL Server Configuration Manager.

2017-06-20_20-44-10 SQL Server Configuration Manager.

As you may have guessed you can add other useful snap-ins  like (Performance Monitor, Disk Management, Event Viewer, Services, WMI Control, ADUC, RSAT, or Computer/Server Management).

2017-06-20_20-46-19 But wait there's more

In case you missed it, while we were adding all those cool snap-ins most of which are  part of Computer/Server Management, you can select the local computer or a remote server.

2017-06-20_20-55-17 Remote management

If you like that you can save the console and give it a name. You can also configure folders to hold different groups of servers. You can also create favorites and organize the relationships between the snap-ins to allow you to drill down through common troubleshooting steps. For example you might like to see a performance monitor snap-in configured to view IO related counters as a child of disk management.

MMC is great tool. Now you have to use it. Try and enjoy.


Temporal Tables. Yes, Please.

SQL Server 2016 and Azure SQL Database implement a new feature called Temporal Tables, not to be confused with Temporary Tables. This feature implements in a common sense fashion the needs of database applications to maintain a history of changes not just for auditing but for the purpose of analytics.

Temporal Tables allow a user to leverage the FOR SYSTEM_TIME clause when querying a table which was created with the SYSTEM_VERSIONING = ON. Users can also query the temporal history table directly, which is necessary for history values with a duration of zero (e.g. rows updated multiple times in a single transaction).

Are You Ready for Some Football?

In honor of Super Bowl 50 I put together a quick visualization with Power Map in Excel 2016. We can see the scores of each team by the size of the dot on their cities. We can also see the number of people in attendance as the height of the bar on the hosting city.

The Case of the Missing Templates

Let me start off by thanking Manuel Quintana for his providing quick resolution to this mystery. I also want to thank the rest of the team at Pragmatic Works Consulting for welcoming me to the team as well as providing their input on this mystery.

“Manuel is like the Seal Team 6 of support people :)” -Mitchell Pearson

In addition to welcoming me to the team Pragmatic Works provides me with the opportunity to get to know Task Factory and Pragmatic Workbench. I was excited to get better acquainted with the latest edition of Task Factory.

The first thing I did was install Visual Studio 2015 so that I could install SSDT. According to MSDN this is a prerequisite. Also according to MSDN it is necessary to install SSDT as update through the Extension and Updates dialog of the Tools menu.

I found it odd that after installing SSDT and launching Visual Studio I wasn’t able to find Integration or Analysis services projects in the new project dialog. I did see SQL Server database projects but that wasn’t going to satisfy my urge to jump into to Task Factory.

I allowed myself to test a hypothesis about the order of installation (I installed Task Factory before SSDT). After reinstalling Visual Studio and SSDT I still didn’t find my templates.

I did some more reading and skimming through the annals of TechNet, MSDN, and Microsoft’s support pages. I found a footnote to a table that stated:

1 There are no BI templates in the product release version of SSDT for Visual Studio 2015. You can either choose the Preview or use a previously released version of SSDT. See Previous releases of SQL Server Data Tools (SSDT and SSDT-BI) for installation links.

Aha! I need to install a previous version! I proceeded to install Visual Studio 2012 and SSDT for Visual Studio 2012. But still no templates… another link, another link… another link… How many SSDTSetup.exe file downloads are there.

2016-02-03_15-29-54 SSDT Downloads

In the end Manuel pointed me to SSDT_VS2012_x86_ENU. As you may have guessed. SSDT is now two separate things SSDT and SSDT-BI. I installed SSDT-BI an now I can create BI Projects in VS 2012. An interesting post script to this mystery is that if I want to create BI projects in 2015 I can install the December 2015 Preview of SQL Server Data Tools.

SQL Server Snippets

Yesterday, working with a colleague the topic got steered towards the use of snippets in SQL Server 2012+. My reflex was to let my colleague know that my blog is wonderful source for all things SQL Server. Much to my chagrin, for all of the snippets I have, I have written a blog post about it.

Visual Studio has had code snippets for a decade now, but SQL Server only got them a version and a half ago. I might imagine that having templates was the excuse given for not pursuing them but I am glad we have them now. The tools menu will lead you to the Code Snippets Manager… from there you can find the path to built-in snippets. These provide good fodder for creating your own.

There are two types of snippets. Expansion snippets insert the code snippet where the cursor is. Surround snippets wrap code around highlighted SQL. Both have arguments that allow you to tab through variable portions and update multiple references in a single place. Let’s take a look at a simple expansion snippet.

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
    <_locDefault _loc="locNone" />
    <_locTag _loc="locData">Title</_locTag>
    <_locTag _loc="locData">Description</_locTag>
    <_locTag _loc="locData">Author</_locTag>
    <_locTag _loc="locData">ToolTip</_locTag>
	<CodeSnippet Format="1.0.0">
			<Title>Create Temporary Table</Title>
			<Description>Creates a temporary table.</Description>
			<Author>Thomas W Marshall</Author>
                                	<ToolTip>Name of the Table</ToolTip>
                                	<ToolTip>Name of the Columneter</ToolTip>
                                	<ToolTip>Data type of the Columneter</ToolTip>
                                	<ToolTip>Name of the Column</ToolTip>
                                	<ToolTip>Data type of the Column </ToolTip>
			<Code Language="SQL"><![CDATA[

IF EXISTS(SELECT name FROM tempdb.sys.objects WHERE object_id=OBJECT_ID('tempdb..#$TableName$') AND type = N'U') DROP TABLE #$TableName$;
    $Column1$ $Datatype_Column1$,
    $Column2$ $Datatype_Column2$


You can copy the above XML into a file with the extension .snippet and save it in the Code Snippets\SQL\My Code Snippets directory. You should then see it in the Code Snippets manager. Alternatively, you can import a .snippet file using the Code Snippets manager. Once the snippet has been installed whenever you strike the expansion chord CTRL+K,X in SQL Server Management Studio you will see it in the My Code Snippets folder of the fly-out menu.

In the above XML document we can see a field in the declarations for each of the variable fields. Once you have chosen to insert the snippet those declare fields are linked by there name appearing between $’s. This allows you to change the value in one and have all instances of that name update. You can also tab to the next name.

You’ll never hear successful people say these 15 phrases


This post is in partnership with Entrepreneur. The article below was originally published at Entrepreneur.com.

By Sujan Patel, Entrepreneur.com

If you want to become more successful as an entrepreneur or in your career, you can start by making a habit of talking and thinking more like the people you know or read about who are already successful.

[fortune-brightcove videoid=3867034492001]

Here are some phrases you’ll never hear a successful person say:

1. “We can’t do that.”

One thing that makes people and companies successful is the ability to make solving their customers’ problems and demands their main priority. If a need arises repeatedly, the most successful people learn how to solve it as quickly as they can.

2. “I don’t know how.”

Instead of automatically shutting down solution-finding, successful people learn what they can in order to succeed in a project or in their career. For example, you would never…

View original post 946 more words