SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Will your db performance match your db growth?
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return different results based on different supplied parameters.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives basic information about SQL injections
Load multiple files in SSIS
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even we can use SSIS to download files from FTP server.
By Mark Wills

Formatting dates and times has aways presented a few challenges in T-SQL. Seems most other languages support a FORMAT command.

Well, time to rejoice because (finally) MS SQL 2012 (and more recent) now supports the FORMAT function. One little "gotcha" is you need .Net Framework because it leverages CLR. Which also means that it cannot be remoted (which is nothing new for CLR runtime).

So, with that out of the way, and for a large majority of T-SQL requirements, it is like any other system function. Probably easiest to show you :)

First, some dummy data. Create an order table with date and price columns, then populate :-
CREATE TABLE EE_tbl_Orders (CUSTOMER varchar(20),ORDER_DATE datetime, ORDER_PRICE decimal(10,4));

INSERT INTO EE_tbl_Orders (CUSTOMER, ORDER_DATE, ORDER_PRICE) VALUES ('Fred','2015-08-20 20:30:40',1234.5678);

Open in new window

The structre of the command is FORMAT ( value, format_str [], culture ] ) and can be found at MSDN Now, [culture] is optional and I tend to leave it out so it can default to the way my server / systems are set up.

So, with our Order Table, let us experiment with our new command...

SELECT Customer, FORMAT(Order_Date,'yyyy MMM dd'), Order_Price
FROM EE_tbl_Orders

-- Results

-- Fred      2015 Aug 20      1234.5678

Open in new window

Now, we can also do something with that price as well (and note the rounding)...

SELECT Customer, FORMAT(Order_Date,'yyyy MMM dd'), FORMAT(Order_Price,'C')
FROM EE_tbl_Orders
-- Results
-- Fred      2015 Aug 20      $1,234.57

Open in new window

LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Darn handy, especially for those that are stuck supporting 2008R2 databases.  Voted Yes.
By Mark Wills

Not so long ago, there was a fairly tricky question on Experts Exchange to do with unstringing a field, and some very good answers. Not wanting to be left out of the "fray", I submitted one of my old tricks. Despite the code being around for a while in various guises, my posting was met with intrigue and various superlatives. Naturally, I felt rather EElated at the response, though, cannot take all the credit.

So, I thought I would share some of those tricks that center around the use of a rather special table in MS SQL Server known as spt_values in the master database.

First, what is spt_values ?

It has been around since the beginning, it is not a system table, but is very much a part of SQL. It is rather simple, it is Microsoft's very own "look up" table for all those system type things (determined by the TYPE column) where you need a name. More importantly, it is probably always resident in memory.

Let's have a quick peek, and please scroll through the list after you have run the first select command, and you will see quite a variety of "names" which are actually held as numbers within the MS systems...

select * from master..spt_values

-- Now, the ones we are interested in are :

select number from master..spt_values where type = 'p' 

-- which gives us all the numbers from 0 to 2047 inclusive. 

Open in new window

So, what can we do with a list of numbers ?

Well, in this article, we can unstring a delimited field, and we can …
LVL 41

Expert Comment

Comment Utility
Mark, nice article.

Why don't you provide examples on splitting the string, displaying vertically etc. here?

As you have already answered such questions, you can add links to those questions for anyone's reference whoever want to check the magic of spt_values.

LVL 60

Expert Comment

by:Kevin Cross
Comment Utility
Here is a nice follow-up tip from chapmandew on how spt_values trick from Mark can be utilized even when a higher than 2048 is needed.


Again this isn't always the optimal method over say a persisted numbers table, but boy have I had fun with this so just wanted to give kudos to Mark again despite the fact I already relinquished my vote above! :)

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005.

The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.


Recently, I was involved in a discussion as to what some of these views really do, and thought I might share some of that discussion in a relatively light hearted way. Considerably more light hearted than the aforementioned discussion...

Now, despite being able to find a lot of discussions about these views, these two in particular can cause confusion. Mainly due to the terminology used in naming these views. Notably, the words "index" and "usage" are a little ambiguous. You would think that those names imply what one would reasonably expect to get if using them, and, when you might use them. Think again.

At first glance they give information which doesn't quite make sense when compared side by side. One view says "this" many reads and the other says "that". One view has an index entry for a table and the other doesn't. Both have entries for tables that don't have indexes. So, what is going on ? Why are they so different ? Which happens to be the wrong question, the fact that the information doesn't match is a very important differentiator. Question should be "How can I take advantage of those difference".

The differences in the information from those views actually tell us important pieces of the index puzzle. Collectively, that information is very telling about your index designs (which will be a different post).

So, lets have a look at those views :

SYS.DM_DB_INDEX_USAGE_STATS only reports on indexes that have been used at least once since the server was last restarted. Here the word "used" really is ambiguous, because it is updated once each time the query plan is executed, not every time an indexed is actually used.

SYS.DM_DB_INDEX_OPERATIONAL_STATS reports on all indexes regardless of whether they have been used and is updated every time the storage engine executes a specific operation on the index during the execution of query plans. One could reasonably argue that this view is really the usage, because it does reflect every time an index is actually used.

Now there is a third very interesting DMV sys.dm_db_index_physical_stats, and can help identify index fragmentation. But that will be a new article.

Because operational stats more accurately reflects actual usage, it is easy to get these two confused. The way I remember how to use them is by their name (duh), but, with a small twist, as in "is my index useful" then usage, "is my index operating efficiently" then operational.

To demonstrate the differences, let's create a couple of test tables :

But first, to use the queries below it has to be in sequence, so open a new query window and paste the code. Highlight the code until you get to the next "go" and then run the queries (keyboard short cut F5).

When looking at the results, you will need to scroll right to see the columns being referred to (namely singleton_lookup_count and range_scan_count in the "operational" view). So please browse and scroll when we look at the results from the DMV's.

by Mark Wills

PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively easy to do, other times it can be a challenge (and that is why we are here).

Let's have a quick look at the PIVOT function...



          (SELECT <source_columns> as Column_Source
                         ,<column_to_be_aggregated> as Column_Value
                         ,<column_with_new_column_names> as Column_List
           FROM <datasource> ) as DataSource

          (<aggregate_function>(Column_Value)  FOR  Column_List  IN
          ([<new_column_1_heading>],[<new_column_2_heading>],...,[<new_column_N_heading>]) ) PivotTable

ORDER BY <column_number_or_name>;

That looks pretty straight forward, except for one or two small details:

1) First up, we need to know the <display_column_list>
    easy enough, just do a Select * instead and problem solved (except that it does control display sequence)

2) Secondly, we need to know and hard code the new column headings as in :

And that last point is often the big challenge. Not so bad if we are doing something static like "months in a year", just list out those months in sequence, and make sure you can cast the…
LVL 51

Author Comment

by:Mark Wills
Comment Utility
Yeah, should be...

Would add in another definition for the totals (similar to @columns).

But then we can no longer simply cheat with "select * from" we would have to add in that new computed column, and probably a parameter to decide when / if to use.

If you are feeling adventurous, there is a slightly more involved Article that should be able to do that for you : http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html


Expert Comment

Comment Utility
Thanks voor the quick reply Mark, i will look in to the article!


SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.