MySQL – Different Methods to Know MySQL Version

In SQL Server, to know the current version of the server, you can use system variable @@VERSION

How many of you know that the same can be used in mysql to know the version?

Also there are many ways to know the version of MySQL

Method 1:

Select @@version as version

It will display the current version of the MySQL Server

There is another function named version()

Method 2:

Select version() as version

The above produces the same result as @@version

You can also use another method to know this

Method 3:

Show variables like '%version%'

The above returns a resultset with two columns variable_name and value. You can find the version where variable_name says version.

Reference: Pinal Dave (

SQL SERVER – What is the OVER Clause? – Notes from the Field #101

Kathi SQL SERVER   What is the OVER Clause?   Notes from the Field #101[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about T-SQL Over clause. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.

The OVER clause was added to SQL Server in 2005. Yes, it’s been around for a decade, but most SQL Server professionals know very little about all the ways it can be used. Whenever you add a T-SQL window (also called windowed or windowing) function such as ROW_NUMBER to your query, you must use the OVER clause. Getting the OVER clause right is critical to getting the correct results. Window functions require the OVER clause, but there is one other situation where the OVER clause is used: with the sequence object. In this article, I’m going to stick just with the ways the OVER clause is used with window functions.

The OVER clause has three components: partitioning, ordering, and framing. Partitioning is always supported, but support for ordering and framing depends on which type of window function you are using.

The partitioning component, expressed as PARTITION BY, is optional and is supported for all types of window functions. Partitioning divides up the rows. It’s kind of like a physical window that is divided up into panes. The entire window is a window. The panes are also windows. Say that I partition by CustomerID. The partition for CustomerID 1000 is restricted to rows for CustomerID 1000.  For the calculation on any given row, the window consists of rows from that partition only. This means that the ROW_NUMBER function restarts the row numbers for each partition, for example. You can specify more than one column for PARTITION BY as long as you separate the columns with commas. Partitioning is not the same as grouping with the GROUP BY clause. When you use the GROUP BY clause, only one row is returned for each group. Partitioning is just a way to divide the data in order to perform the calculations; the detail columns are returned. Figure 1 shows an example.

101 1 SQL SERVER   What is the OVER Clause?   Notes from the Field #101

Figure 1: The effects of PARTITION BY

Ordering within the OVER clause is supported when the data must be sorted to perform the calculation. For example, ROW_NUMBER requires an ORDER BY expression within the OVER clause because the rows must be lined up. Actually, most of the window functions support ORDER BY.  ORDER BY in the OVER clause is not supported for calculating subtotals, for example. You don’t need the data sorted to calculate a sum. Note that the ORDER BY within the OVER clause has nothing to do with an ORDER BY clause found in the query itself. Just like partitioning, you can specify a comma delimited list of columns. Figure 2 shows the results of ORDER BY in the OVER clause.

101 2 SQL SERVER   What is the OVER Clause?   Notes from the Field #101

Figure 2: Using ORDER BY

The third component of the OVER CLAUSE is called framing. Framing was introduced with SQL Server 2012 and is used in only a couple of situations: accumulating window aggregates (running totals, moving averages, etc.) and the functions FIRST_VALUE and LAST_VALUE. While partitioning is similar to window panes, framing is like a stained glass window. When performing the calculations, every row might see a different set of rows through the window. For example, when calculating a running total, row 1 sees row 1. Row 2 sees rows 1 and 2. Row 3 see rows 1, 2, and 3. The syntax for framing is a bit complex, so I am going to save it for my next post. By default, the frame consists of the first row of the sorted partition and all subsequent rows up to the current row.  Figure 3 is an example. The small arrows represent the current row where the calculation is being performed.

101 3 SQL SERVER   What is the OVER Clause?   Notes from the Field #101

Figure 3: The results of framing

To see the OVER clause in action, run this query against the AdventureWorks database:

SELECT CustomerID, SalesOrderID, TotalDue,
SUM(TotalDue) OVER() AS GrandTotal,
SUM(TotalDue) OVER(PARTITION BY CustomerID) AS SubTotal,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS RunningTotal,
LAG(SalesOrderID) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS PrevOrder,
FROM Sales.SalesOrderHeader;

notes 82 3 SQL SERVER   What is the OVER Clause?   Notes from the Field #101Next time, I’ll provide a deep dive into framing. If you would like to learn more about T-SQL window functions, be sure to check out my latest book Expert T-SQL Window Functions in SQL Server or my Pluralsight course T-SQL Window Functions.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (

SQL SERVER – How to take InMemory – OLTP FileGroup Offline?

InMemory topics are always interesting and a great learning experience. When I wrote the other post, it was more about how InMemory tables are loaded into memory even if they are not queried. The basics, here is to bring the data for In Memory Tables ahead to memory irrespective of their usage.

After this blog post was published, someone wrote me a mail asking if it is possible to take the InMemory Filegroups offline? And will the table’s data taken offline too? This question was obvious but still needed validation and I was curious to understand why this wouldn’t work in normal circumstances. The script to start working on this post has to start from previous post. Please create the database and then before the cleanup step – start to run the below scripts.

At this stage, your query must return some value for the below query:

FROM sys.dm_db_xtp_table_memory_stats

For all practical purposes, InMemory tables and filegroups are just like normal files. We should be able to take them offline using the same way we can do other filegroups.

-- Take InMemory FG offline

This should complete successfully and when we run the above DMV query to find the memory consumers, this must return no rows.

FROM sys.dm_db_xtp_table_memory_stats

Now that was pretty simple and easy to figure out. I would like to know if you have ever done anything like this in your systems?

Quick Quiz Question:

Now if taking the filegroup offline was easy, what about bringing the filegroup online next?


Simple isn’t it? No. You will encounter an error:

Msg 155, Level 15, State 1, Line 1
‘ONLINE’ is not a recognized CREATE/ALTER DATABASE option.

So the quiz question is, how can we bring the filegroup online in SQL Server 2014 version? What are the steps. Let me know via comments.

Reference: Pinal Dave (

SQL SERVER – Basics ColumnStore FAQ – Part II

columnstore SQL SERVER   Basics ColumnStore FAQ   Part III wrote the first part of FAQ in here. This is not a typical blog post but more of a process oriented topic when working with ColumnStore Indexes. With the advancements done in SQL Server 2016, I know that this is going to be even more critical to understand some of the nuances of working with ColumnStore indexes inside SQL Server. So what will be target in this post? Here are few questions I asked and I got back in return which form the flow for this post.

ColumnStore index resulted in a performance regression

On first thought when I heard this from one of the tester, I was baffled to see this was the case. How can this happen. I went the route of asking some fundamental questions to get to the root cause. Here are those for a quick reference and the reason for it:

  • Did the optimizer use the ColumnStore index in the first place?

A user can determine whether a ColumnStore index was used in the query plan by looking at the Storage property in SHOWPLAN. Read about batch mode and row mode here.

  • Did the query start to execute using the columnstore index with batch processing and then fall back to row-based processing?

A user can determine that the query started to execute in batch mode then fell back to row mode by looking at EstimatedExecutionMode and ActualExecutionMode in Showplan. In most cases, even in row mode the columnstore index will provide better performance than the rowstore. If performance regression due to the columnstore is suspected, a query hint to force use of a different index can be used to prevent the query optimizer from selecting the columnstore. We discussed about some of the query hints that can be used in the previous post.

  • Did you insert a FORCE ORDER hint on a join?

If a FORCE ORDER hint is used, optimizer will obey the FORCE ORDER hint. If the optimizer would have used the starjoin optimization and created bitmaps, the loss of the starjoin optimization bitmaps could cause performance to be worse than without the FORCE ORDER hint.

Question: In queries processed in row mode, some filters are evaluated in the storage engine. Can the columnstore do that too for optimization reasons?

Yes, very much. Some filters that are pushed down into the storage engine are:

  • comparisons <column, comparison, constant or parameter>
  • IN lists <constant1 or parameter1, constant2 or parameter2 … constantN or parameterN>
  • filters on strings are NOT pushed down

Question: In queries processed in row mode, partitions can (sometimes) be eliminated from the scan. Can the columnstore do that too?

Yes, and no. Partition elimination per se does not occur, however segments (actually row groups – i.e. the corresponding segments for each column) can be eliminated from the scan. Each nonempty partition has one or more segments for each column. Eliminating each segment in a partition is equivalent to partition elimination. Eliminating a subset of segments in a partition for which some values in the partition qualify can be better than partition elimination (because that partition would not be eliminated). Each segment has metadata that includes the min and max values in the segment. Similar min/max metadata associated with bitmaps and filters can be used to determine that none of the values in a segment will qualify without scanning the segment. If a segment is eliminated from the scan, the other segments in the same rowgroup are also eliminated. Column_store_segment_eliminate extended event can be used to see if segments are being eliminated.

Question: Does partitioning work the same way for the columnstore index as for row store indexes?

Mostly, yes. A nonclustered columnstore index can be built on a partitioned table. An empty partition can be split. Two empty partitions can be merged. An empty partition can be merged with a nonempty partition. A partition can be switched between partitioned tables if both tables have a columnstore index and all the other requirements for switching partitions are met. There are some restrictions associated with columnstore indexes:

  • If a table has a columnstore indexes, two non-empty partitions cannot be merged
  • A nonclustered columnstore index must always be partition-aligned with the base table. As a corollary, the partition function used to partition the nonclustered columnstore index and the base table must be identical, not just equivalent. The reason for this requirement is that otherwise the partition function on the base table could be changed, resulting in non-alignment.

As I conclude for this post, I will try to create supporting posts with scripts to show how the above restrictions apply and how one can figure these when they are implementing ColumnStore Indexes in future posts. Out of curiosity, I would like to know how many of you literally use the ColumnStore Indexes in your environments? What has been your experience using the same?

Reference: Pinal Dave (

SQLAuthority News – 9th Year Blog Anniversary

9th SQLAuthority News   9th Year Blog AnniversaryToday is an amazing day!

9 years ago on this day, I started to write this blog.

Since then I am producing one blog post a day everyday!

During this year there are so many changes have happened in the blog and my professional career as well as on my personal life. I have always shared my true feeling on this blog all the time.

Today, I feel like writing a lot of things, but I am pretty sure that readers are more interested in learning SQL rather than reading my monologue.

Here is my promise, I am going to continue my journey to SQL for the next year!

One more thing – THANK YOU for your love and support!

Reference: Pinal Dave (

Interview Question of the Week #043 – What is the Difference Between EXCEPT operator vs. NOT IN

Question: What is the Difference Between EXCEPT operator vs. NOT IN

Answer: The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there are no matching rows in the right query. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that do not also return from the right query.

Example of EXCEPT operator is displayed along Example of NOT IN. If you run both of the Query and compare the Execution plan it is exactly the same. EXCEPT and NOT IN does same functions and have the same execution plan, but EXCEPT has much simpler syntax. The row-by-row comparison provided by EXCEPT, combined with the number of rows being returned remaining consistent, provides compelling evidence that re-factored query is correct. EXCEPT works with * as well as aliases.

Example: (Both of the scripts returns the same number of rows)
-- SQL SERVER 2005 Method
USE AdventureWorks;
FROM Production.Product
FROM Production.WorkOrder ;

-- SQL SERVER 2000 Method which works IN SQL SERVER 2005
USE AdventureWorks;
FROM Production.Product
FROM Production.WorkOrder);

Following the execution plan which indicates EXCEPT and NOT IN uses same execution plan. Click on below image to see larger image.
SQLSERVER EXCEPT ExecPlan small SQL SERVER 2005 Comparison EXCEPT operator vs. NOT IN

Reference: Pinal Dave (

SQL SERVER – Adding Datetime and Time Values Using Variables

Datetime datatype stores date and time part. If you have only date value, You can add time part to a datetime variable using direct literal value and + operator.

It is shown below
SET @date=@date+'15:00:00'

The result is 2010-10-01 15:00:00.000
But it is not possible to add it using a time variable

DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+@time
SELECT @date

The error is

Msg 402, Level 16, State 1, Line 4
The data types datetime and time are incompatible in the add operator.

So the solution is to convert time datatype into datetime and add
DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+CAST(@time AS DATETIME)

The result is 2010-10-01 15:00:00.000

So when adding data of datetime and time datatype values, you need to be aware of this.

Reference: Pinal Dave (

SQL SERVER – Who is consuming CPU on my SQL Server?

The topics around performance tuning is always interesting and I get quizzed by a number of you during conferences on these topic areas post my presentation. A relatively simple yet a powerful question was asked by one of the attendee in my session at MODS (Mobile Developer Conference) this year. I was walking about databases in the web world and how one needs to preserve, protect and optimize for web based workloads. I was fortunate to have a packed crowd but couldn’t take all the questions on stage during the session. As it always happens, there is a mob that follows me outside and the session’s second innings happens there for close to an hour. These are conversations to remember and many bring back questions that I need to work on.

This blog got inspired by one such question one of the gentleman asked at this corridor conversation couple of weeks back. He said, they had a server which was consolidated from 10’s of servers into a monolithic big machine. The consolidation activity went well and everything was running absolutely fine till one fine day the server was not responsive. This resulted in a number of applications suffering to connect to SQL Server.

They were wondering if the consolidation exercise was worth all the pain on first place? When they were on separate servers they exactly knew which server / application was creating the problem and the fix was easy. Now they have about 60+ database on this one server and they are not able to find the cause for CPU spike for an elongated period of time.

So the question was – how can I find out what are the databases that consumed maximum time on my CPU relative to other databases? Do I know which are the consumers of my CPU on a given server? The initial instinct was to search SQLAuthority and I was surprised I haven’t written about this simple need. This is my first shot for this requirement using DMVs:

WITH DB_CPU_Statistics
(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS pa
GROUP BY DatabaseID)
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Statistics
WHERE DatabaseID <> 32767 -- ResourceDB

 SQL SERVER   Who is consuming CPU on my SQL Server?

The result for me on a freshly restarted SQL Server looks like this. Do let me know does this query make any sense on your actual environments? Did you ever need such a requirement in your troubleshooting of SQL Server? What have you been doing to get to this information in the past? Please let me know via your comments as it would be really helpful for all to know the other methods.

Reference: Pinal Dave (

SQL SERVER – What is Semantics Search Inside SQL Server?

Every now and then I bump into stuffs that I didn’t even hear about when it comes to working with SQL Server. This time I heard the word Semantics Search and was wondering where and when did it come. In this blog, I will put the foundation for this and later we will look at how this can be used in our environments.

Wikipedia defines Semantics as the study of meaning. Microsoft defines “Semantics” as providing higher-level insight into large quantities of data even across diverse unstructured data.

In SQL Server 2012, this meaning is around key phrases in documents and documents that share similar concepts. However, if we define extraction as “from unstructured data, create structured data that describes it”, then SQL Server already has three instances of extraction: full-text indexing, XML indexing, and spatial indexing (this inputs unstructured spatial values and then creates a structured index supporting spatial queries).

Full-text search uses an inverted index for storing keywords from documents. This can answer questions like “What documents contain this word” or “What documents have this word NEAR that word” or “What documents have SQL Server in the title?”

While full-text search lets you query the words in a document, semantic search lets you query the meaning of the document. Semantic Search adds two additional index types as part of SQL Server’s vision for Rich Unstructured Data Services (RUDS).

  • A tag index – Given a corpus of unstructured text documents, extract the key concepts in these documents or more specifically, the key phrases.
  • A similarity index – utilizing the extracted key concepts to determine which concepts are related, using related concepts to infer related documents, etc. An example of using similarity is to find resumes based on a job description.

SQL Server Semantics falls into “Patterns/Outliers” segmentation. It is optimally designed to perform the extraction one time, but populate both the full-text search and the two sematic indexes, as configured. Therefore, it is possible to create a full-text index without semantic indexes, but it is not possible to create semantic indexes without also creating the full-text index.

Note: You cannot create one semantic index and not the other. You may only use one, but both are created during population when specifying to include.

If you are wondering if this is enabled on your SQL Server 2012 or 2014 instance, you can run the following command to determine if full-text and semantic search are installed. A return value of 1 indicates that these components are installed; a return value of 0 indicates that they are not installed.


Before I write the subsequent blogs around what these are and how to enable, I am interested in knowing has anyone worked or used Sematic Search with SQL Server in your applications?

Reference: Pinal Dave (

SQL SERVER – Script level upgrade for database ‘master’ failed – CREATE SCHEMA failed due to previous errors

SQL Server troubleshooting topics seem to be the most searched for in this blog. In continuation to this, blog for today is also inspired with one such error message. Some time back I have written below blog which talks about upgrade script mode of SQL Server:

SQL SERVER – Login Failed For User – Reason Server is in Script Upgrade Mode

One of the reader reached out to me with below error in ERRORLOG

Setting object permissions…
Error: 2714, Severity: 16, State: 6.
There is already an object named ‘TargetServersRole’ in the database.
Error: 2759, Severity: 16, State: 0.
CREATE SCHEMA failed due to previous errors.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 2714, state 6, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Above error is truly scary. “Restore master from a full backup, repair it, or rebuild it”. So I asked my friend to share the script ‘sqlagent100_msdb_upgrade.sql’ to check which query is failing. It was not very difficult to find (I searched for “Setting object permissions…” which was printed in ERRORLOG before error) and I found that below is the piece present in script, which was failing.

FROM msdb.dbo.sysusers
WHERE (name = N'TargetServersRole')
issqlrole = 1)))
-- If there are no members in the role, then drop and re-create it
FROM msdb.dbo.sysusers   su,
msdb.dbo.sysmembers sm
WHERE (su.uid = sm.groupuid)
AND ( = N'TargetServersRole')
su.issqlrole = 1)) = 0)
msdb.dbo.sp_droprole @rolename = N'TargetServersRole'
EXECUTE msdb.dbo.sp_addrole @rolename = N'TargetServersRole'
msdb.dbo.sp_addrole @rolename = N'TargetServersRole'

To be very precise, adding the role was failing on MSDB database on his SQL Instance. I asked him to run below and got an error.

TS Role 01 SQL SERVER   Script level upgrade for database master failed   CREATE SCHEMA failed due to previous errors

Now, Since SQL we were not able to connect to SQL, we need to use trace flag 902 to bypass the script. Here are the steps followed.

1. Applied the trace flag -T902 on SQL Server configuration manager
2. Start SQL Services and it should allow us to connect because the trace flag would bypass upgrade script mode.
3. Find the schema/role TargetServersRole in MSDB and make a note of members, if any.
4. In his case, the schema was having someone else as owner. Asked him to modify using below T-SQL
USE [msdb]
ALTER AUTHORIZATION ON ROLE::[TargetServersRole] TO [TargetServersRole]

5. After fixing the cause, we stopped SQL, removed trace flag and started again.
6. This time script got executed and SQL was out of upgrade mode.

Have you also encountered similar issue?

Reference: Pinal Dave (