[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x

SQL

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
6
Fill in the form and get your FREE NFR key NOW!
LVL 1
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
2
 

Expert Comment

by:Hari Shankar
Comment Utility
Great Article. Thank u Vitor
1
Windocks web UI
Windocks is an independent port of Docker's open source to Windows. This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
0
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this solution, while refreshing my recursive CTE skills.
0
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.
0
 

Expert Comment

by:Gregorio Méndez
Comment Utility
Mr. Strauss, what a good tip, thanks a lot for your contribution
1
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.
2
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
0
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.
0
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 ?
2
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
4
Efficient way to get backups off site to Azure
LVL 1
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

8
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Darn handy, especially for those that are stuck supporting 2008R2 databases.  Voted Yes.
0
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 …
15
 
LVL 41

Expert Comment

by:Sharath
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.

-Sharath
0
 
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.

http://sqlservernation.com/blogs/tipweek/archive/2010/06/27/quickly-generate-a-number-sequence-in-sql-server.aspx

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! :)
0

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.


9
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
          <display_column_list>

FROM

          (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

PIVOT
          (<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 :
     ([<new_column_1_heading>],[<new_column_2_heading>],...,[<new_column_N_heading>])

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…
32
 
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

Cheers,
Mark
0
 

Expert Comment

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

SQL

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.