MySQL – How to Format Date in MySQL with DATE_FORMAT()

MySQL supports formatting the datetime values into a different formats using DATE_FORMAT() function. This function accepts date/datetime values as a first parameter and returns into a specific format defined as a second parameter.

Let us explore this with the following examples which are self-explanatory

Define a DATETIME variable
SET @date:='2014-06-16 14:12:49';

-- Display datetime values in YYYY-mm-dd format
SELECT date_format(@date,'%Y-%m-%d') AS formatted_date;

The result is 2014-06-16

-- Display datetime values as Long Date format
SELECT date_format(@date,'%W, %M %d,%Y') AS formatted_date;

The result is Monday, June 16,2014

-- Display datetime values as Full date format
SELECT date_format(@date,'%W, %M %d,%Y %T') AS formatted_date;

The result is Monday, June 16,2014 14:12:49

-- Display datetime values in HH:MM:SS format
SELECT date_format(@date,'%T') AS formatted_date;

The result is 14:12:49

-- Display datetime values in Month Year format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is June 2014

-- Display datetime values in mm-dd-yyyy format
SELECT date_format(@date,'%m-%d-%Y')  AS formatted_date;

The result is 06-16-2014

-- Display datetime values in dd-mm-yyyy format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is 16-06-2014

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Free intellisense add-in for SSMS

This article shows how to use ApexSQL Complete, a free SQL Server intellisense add-in. You can download ApexSQL Complete, and play along through the article.

ApexSQL Complete is a free SQL Server Management Studio and Visual Studio add-in that speeds up SQL coding. In this article, we will explain ApexSQL Complete through its features. To start using ApexSQL Complete, enable it from the ApexSQL menu under the main menu in SSMS:

Hint list – complete your SQL code

This is a key feature of ApexSQL Complete. It helps you find the wanted object or a SQL keyword, and complete the SQL statement faster instead of typing the entire object name or keyword. After you start typing, for example “SE”, SQL intellisense will appear with all keywords and database objects that contains “SE”, listed by importance:

You can uncheck the box for the appropriate objects, and prevent them to appear in the SQL intellisense from the add-in options, under the Hints tab. This will decrease the number of hints in the SQL intellisense list, and speed up the coding process:

Another useful property of the SQL intellisense in ApexSQL Complete is a multiple sections, which allow you to navigate over the database schema, to a table, and select the specific columns, checking the appropriate boxes. You can also select the columns and hit the Enter key to insert them:

If a SQL script you are using is large, at some point you will need to look at SQL code before you continue. ApexSQL Complete offers you to accomplish this without a break. Press and hold the CTRL key, and the SQL intellisense will become transparent, so you can see through, and review SQL code. Releasing the CTRL key will get you back to a previous state, and you can continue typing:

Tab navigation – monitors all opened SSMS tabs

This feature allows you to track opened and recently closed tabs, or to restore previously saved session after crashing. These operations can be managed from the add-in options, under the Tab navigation tab. Here you can set the period for keeping the tabs saved, and set the interval for auto-save.

This could be useful if, for any reason SSMS crashes. The Tab navigation feature saves your time, and gets you back to a point before crashing.

The Tab navigation pane consists of two parts, Opened tabs and Recently closed tabs. In the Opened tabs section, all opened tabs from one session will be shown, and you can easily switch from one to another. You can search the content of the opened and closed queries, and open the query that contains searched results. Double-clicking the query from a list of the Recently closed tabs tab opens it in a new query window. For the opened and closed tabs, if you select a query from the list, a complete content will be shown in the preview section to the right:

At any point, you can save your workspace or opened tabs, and restore to the saved state later, if SSMS crashes.

Code structure – view and find SQL code blocks

This feature provides a tree-like form of SQL code presented in a separate SSMS window on the left side of the query window. When you enable the Code structure feature from the add-in options, it allows you to see all the important parts of SQL code used in the query. SQL code blocks from the Code structure window can be expanded so you can navigate to a specific part of the block. Selecting any item from the Code structure window highlights a SQL code block in the query window:

This way you can move through SQL code in blocks and find the part you are looking for instead of scrolling down the query.

Executed queries – track executed queries

Using this feature allows you to track all executed queries in a defined period. To enable the feature, select the Log execute queries option under the Executed queries tab in the add-in options. You can set the folder for storing the executed queries. The queries are saved as an .xml files. You can also define the maximum number of lines in SQL code, which will be stored. The Default period option allows you to show the queries executed in a defined period.

When activated, the Executed queries form will show all the queries executed in a defined time range. If you select a query from the list, its content appears in the preview section. You can search through the queries, executed in a defined period. Double-clicking any of the executed queries from the list opens it in a new query window in SSMS, so you can additionally change SQL code.

Snippets library – insert often SQL statements

With this feature you can insert often used SQL statements, even a whole procedure, or blocks of SQL code. You can create a snippet from the ApexSQL Complete options, or from the SSMS query window:

1) To create a snippet from the ApexSQL Complete options, navigate to the add-in options, and click Add new snippet option, under the Snippets tab:

Here you can edit any of the predefined snippets from the library, export/import them to use it on another machine.

2) To create a snippet from the SSMS query window, type SQL code you want to be defined as a snippet, select it, and right click on it. From the context menu, choose the New snippet option:

This will open the Create a new snippet window, with the selected code already inserted in the Code section. You just need to define a name for the new snippet, and optionally a description:

To use already created snippet from the Snippet library, click the Insert snippet option from the context menu in the SSMS query window, and double click on a snippet from the list to use it in the query.

Navigate to object – locate an object in the Object Explorer

This allows you to locate the selected object in the Object Explorer pane. In the query window, select the object you want to locate, and right-click on it. From the context menu, choose the Navigate to object option, and the selected object will be located and highlighted in the Object Explorer pane to the left.

Test mode – execute queries without impact to the database

The Test mode feature allows you to execute a query in a test environment, without impact and consequences to the database. To use the Test mode feature, select the Test mode option from the toolbar, and highlight SQL code in the query window you want to execute.

The Test mode feature will add BEGIN TRANSACTION and the ROLLBACK TRANSACTION statements. After the execution, it rolls back the transactions at the beginning:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Beginning Contained Databases – Notes from the Field #037

[Note from Pinal]: This is a new episode of Notes from the Fields series. Login and Users are very confused concept. Just yesterday I wrote about a difference between Login and User. In Latest version of SQL Server now we can also have a User without Login. This concept is not easy to understand and needs a clear example.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces related to Login and Users. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


One of the downfalls with database mirroring is synchronizing database users with logins. If you were using SQL Server accounts instead of windows domain accounts it could be a tougher challenge to ensure that the unique identifiers (SID) match up. In SQL Server 2012 and up we can leverage contained databases to mitigate this problem with Availability Groups. Contained database users are stored inside the database instead of leveraging the instance logins. This allows the database itself to manage authentication and authorization for the login for the database.  Therefore, you don’t need to fixed orphan users when you failover.

Here is how you can enable and leverage Contained Databases.

Step One: Enable Contained Database on the instances that will host the database. In an Availability Group this would be all the replicas for the Availability Group.

This can be configured in SSMS or via T-SQL as shown below.

EXEC sp_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_CONFIGURE 'contained database authentication',1
GO
RECONFIGURE
GO

Step Two: Enable Contained Database on the primary replica. This is also known as the read/write replica.

This can be done via SSMS or T-SQL

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

Step Three: Create a Contained User
In order to have a contained user were going to have to create a new user inside the database and create it without a login. This will make the user a contained user. You will also need to add the needed security for your account. For the purpose of this weekly tip we will skip this part. Creating the contain login also known as SQL user without login can be done with SSMS or T-SQL as shown below.

USE [AdventureWorks2012]
GO
CREATE USER [MyContainedUser] WITH PASSWORD=N'!LPPeople!', DEFAULT_SCHEMA=[dbo]
GO

Step Four: Test connectivity.

Finally, we can test connectivity. This will be done utilizing SSMS as shown below.  Make sure you change the default database to the database where the login is contained as shown below.


Now, make sure you select the Connection Properties tab and select the database where you created the contained login. In our example, this will be AdventureWorks2012.

 

When connection is successful with a contained database user you should see the login and the database right next to the instance in object explorer as shown below.

 

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Difference Between Login and User – SQL in Sixty Seconds #070

“What is the difference between Login and User in SQL Server?”

This is a very common question I often receive. Yesterday when I was browsing Facebook, I once again noticed this question once again asked in SQLBangalore group. My very good friends – Vinod Kumar and Balmukund Lakhani had already answered the question there. However, I every time, I read this question, I realize that not everyone knows the basics of these two concepts. If I have to explain the difference between them, it may take a long time, but I will try to explain it at a very basic level.

Basic Explanation:

SQL Server Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level. We can have multiple user from different database connected to a single login to a server.

I hope this is clear enough. If not, I strongly suggest you watch following quick video where I explain this concept in extremely simple words.

Action Item

Here are the blog posts I have previously written on the subject of security. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSMS: Memory Consumption Report

The next in line in this series of reports is the “Memory Consumption” Report from SQL Server Management Studio. This is a goldmine of a report in my humble opinion and lesser respected. When I used to be consulted or land into performance tuning exercises for customers in the past, there is one question that gets repeated and echoed every now and then – “My SQL Server is eating away my RAM and it is not releasing it back even in non-peak hours”. I always am smiling when this question comes up. SQL Server or for that matter any database system is highly memory oriented processes. If they had taken for some reason, then they are not going to release it because they assume at a later point in time they will require it again. So instead of depending on the OS to allocate, they go with the assumption of grabbing and never releasing even when it is not required in the interim.

Now that brings to the point the fact what is my SQL Server using this memory for? Well, if you search the internet you will be amazed by the plethora of scripts and it is overwhelming how people have killed this subject to death. But this hidden gem inside SQL Server Management Studio is never talked about. So in this blog post, let me take a tour of what this report contains and how one should read the sections.

This report can be launched by going to Server Node in SQL Server Management Studio (SSMS) right click > Reports > Standard Reports > Memory Consumption.

The report has multiple sections which we would discuss one by one.

Memory Related Counters

These three values can give us a rough indication of memory pressure on SQL Server Instance. These three values are retrieved from SQL Server Memory counters.

SELECT  OBJECT_NAME
,counter_name
,CONVERT(VARCHAR(10),cntr_value) AS cntr_value
FROM sys.dm_os_performance_counters
WHERE ((OBJECT_NAME LIKE '%Manager%')
AND(
counter_name = 'Memory Grants Pending'
OR counter_name='Memory Grants Outstanding'
OR counter_name = 'Page life expectancy'))

As per perfmon counters help, “Memory Grants Outstanding” shows counter shows the current number of processes that have successfully acquired a workspace memory grantgrant, whereas “Memory Grants Pending” counter shows the current number of processes waiting for a workspace memory grant. Page life expectancy is defined as “Number of seconds a page will stay in the buffer pool without references

Top Memory Consuming Components

This section of the report shows various memory consumers (called clerks) in a pie chart based on the amount of memory consumed by each one of them. In most of the situations, SQLBUFFERPOOL would be the biggest consumer of the memory. This output is taken from sys. dm_os_memory_clerks DMV, which is one of the key DMV in monitoring SQL Server memory performance.  We can use sys.dm_os_memory_clerks to identify where exactly SQL’s memory is being consumed.

Buffer Pages Distribution (# Pages)

This particular section of the report shows the state of buffer pages. Behind the scenes it uses DBCC MEMORYSTATUS to get the distribution of buffer in various states. Buffer Distribution can be one of the following as: ‘Stolen’, ‘Free’, ‘Cached’, ‘Dirty’, ‘Kept’, ‘I/O’, ‘Latched’ or ‘Other’. Interestingly, if we run the DBCC MEMORYSTATUS, we may not see all these states. This is because memory status output format has been constantly changing SQL 2000 (KB 271624) and SQL 2005 (KB 907877).

Memory Changes Over Time (Last 7 Days)

This section of the report shows data from default trace. One of the event which is captured by default trace is “Server Memory Change” (Event id 81). Behind the scene, this section reads default trace, looks for event ID 81 and adds a filter (datediff(dd,StartTime,getdate()) < 7) to display last 7 days records. My laptop doesn’t have much load that why we don’t see any memory change. Another reason, as quoted in the text, of no data could be that default trace are disabled.

I am sure in your production or active development boxes these values are not going to be zero for sure.

Memory Usage By Components

At the bottom, there is a table which shows the memory for each component.  This is also taken from the same DMV, which is used in “Top Memory Consuming Components”. The graph earlier shows top 5% consumers by name and the rest would be shown as others. It’s important to note that in SQL 2014, it would always show MEMORYCLERK_XTP which is used by In-Memory OLTP engine (even if it’s not a top consumer).

Here is the little description of various columns:

Allocated Memory Amount of memory allocated to sqlservr.exe
Virtual Memory (Reserved) Memory reserved in Virtual Address Space (VAS)
Virtual Memory (Committed) Memory committed in Virtual Address Space. Once memory is committed in VAS, it would have physical storage (RAM or Pagefile)
AWE Memory Allocated Amount of memory locked in the physical memory and not paged out by the operating system
Shared Memory (Reserved) Amount of shared memory that is reserved
Shared Memory (Committed) Amount of shared memory that is committed

To understand reserve and committed, I always quote this. Imagine that you need to fly to Mumbai on a certain date and you book a flight ticket. This is called reservation. There’s nothing there yet, but nobody else can claim that seat either. If you release your reservation the place can be given to someone else. Committing is actually grabbing the physical seat on the day of travel.

Hope this gives you a fair idea about various pieces of memory consumers. As I mentioned before, this is one of those hidden gem reports that never gets seen. One can learn and know about a current running system and who are using SQL Server Memory from this report easily.

I would be curious to know if in any of your systems if there is any other component apart from BufferPool or SOSNode as the top memory consumers?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSMS: Scheduler Health Report

Have you ever been to a music concert? It is the most humbling experience one can get as a music lover. The live music with hundreds of musicians in one stage brings goosebumps with the very thought. I have always been fascinated and wanted to experience this once in a lifetime and am sure that day is not far off. What strikes me big time is the conductor, standing alone with a small stick orchestrates these fabulous musicians to give all of us a delight and experience of a lifetime. This brings me to today’s topic of discussion on the Scheduler Health Report. In a way the conductor inside SQL Server is our scheduler – who makes sure all the activities and all parts get their share of time to execute. Looks like a dream job, but trust me there is lot of effort in understanding how each components works just like how a conductor really knows when to introduce a new instrument as part of the concert. Before I start explaining about this report component, it’s important to go through the basics of SQL Scheduler which would help in understanding this report.

SQL Server has a mini operating system which manages the resource by its own, that’s why you would hear term called SQLOS. By resources we mean CPU, Memory and IO available on the machine.

Whenever any request is received in SQL Server, it would be assigned to a thread and that thread would be scheduled on a scheduler. This might go to multiple scheduler in case of parallelism though. Those threads which are ready to run would be scheduled and sent to the operating system for execution. Imagine a situation of blocking where a blocked thread can’t do anything unless the resource is available. In such situation, does it make sense to send this request out to the operating system for scheduling? Of course not! That’s why this mini operating system does better scheduling and SQL can scale up very well as workload increases. Another advantage of the SQLOS layer is to reduce context switching of threads in operating system because it only sends the threads which can do some meaningful work.

To summarize, SQLOS is a mini operating system within sqlservr.exe process which takes care of managing CPU, Memory, locks, IO and a lot more. In general terms, the scheduler is a logical CPU on SQL Server side. In general, each scheduler is mapped to one logical processors exposed by the operating system. There are hidden and visible scheduler in SQL Server. They can be looked into via DMV sys.dm_os_schedulers

To know more about our Scheduler, here is the place to get this report. Right Click on Server node, choose Reports > Standard Reports > Scheduler Health.

The complete reports revolves around SQLOS. SQLOS has something called non-preemptive scheduling (also known as cooperative scheduling) which is different from the scheduling done by the operating system. Windows operating system does preemptive scheduling where a thread would get a fixed amount of time to run on the CPU. Once the time slice is completed, it would be snatched out of the CPU and put into the queue for the next chance to run. This is a fair game because all threads are getting a chance to run. On the other hand, in SQLOS a thread would do its work and come back to the scheduler by its own; no one is going to take him off the scheduler. This term is called as yielding. If a thread went out from SQLOS layer and didn’t come back – it’s called as non-yielding situation. If all schedulers have the same problem of non-yielding them you can imagine that SQL would go to “hung” state. A scheduler can be in three states – Idle (when work_queue_count <> 0), Hung (when yield_count is not changing) or Active (when it’s not in the other two states). Hence the first part of our report shows which states our Scheduler currently is in. In our case, the scheduler is in the Idle state.

The second part of the report shows the details about worker (can also be accessed via DMV sys.dm_os_workers), tasks (accessed via DMV sys.dm_os_tasks) and processes running under each scheduler. Let us understand these terms in the little details as it would help you in understanding this section of report better.

Task – represents the work that needs to be performed. It can also be called as unit of work that is scheduled by SQL Server. An example of task could be pre-login, login, query execution, logout and may more. The task can be in various states (PENDING, RUNNABLE, RUNNING, SUSPENDED, DONE or SPINLOOP). Please refer this for more details.

Worker - are the threads who would do the task given by the scheduler.
Request – is the logical representation of a request made from the client application (or work done by system threads) to SQL Server. This request would be assigned to a task that the scheduler hands off to a worker to process.

Now that our fundamentals have been sorted, let us have a look at the second report section:

My machine has currently has 8 logical processors and hence we are seeing values of Scheduler ID’s: 0 to 7. Other schedulers are having status as “HIDDEN ONLINE” in sys.dm_os_schedulers. Each scheduler has various workers associated. We can see that in column #Workers for each scheduler rows. Once we click on (+) for the scheduler, we can see details about each worker. Further clicking on (+) for each worker, we can see the work done by that worker.

I hope that this blog has helped you in understanding the basic functionality of SQLOS and how the Scheduler Report drills into the fine prints.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Developer’s Life – Every Developer is a Harry Potter

Harry Potter might not be a superhero in the traditional sense, but I believe he still has a lot to teach us and show us about life as a developer.  If you have been living under a rock for the last 17 years, you might not know that Harry Potter is the main character in an extremely popular series of books and movies documenting the education and tribulation of a young wizard (and his friends).

Throughout the seven books and eight movies, Harry Potter learns to face his fears, trust his friends, and grows as a person.  This series was so beloved because of the real life lessons each story contained.  So what can we learn about developers from the world’s most famous wizard?

So how are developers like Harry Potter?

Well, read on my list of reasons.

Never Give Up

Never give up.  Harry Potter faced some astounding obstacles in his life – first, losting his parents, living with his horrible aunt and uncle, and facing He-Who-Shall-Not-Be-Named, the most evil wizard in all history.  Developers may not face pure evil brought back to human form, but they certainly do not give up in the face of difficult problems.

Have Dependable Friends

Rely on your friends.  Throughout the books, Harry learns that he cannot stand up to his troubles alone, and that he has to trust his friends to be by his side.  Developers may not have gone to wizarding school with two of their closest friends, but they know that teamwork is extremely important to job performance.

Fake it until You Make it

Fake it until you make it.  Harry Potter was just eleven when he faced his first challenge in Harry Potter and the Philosopher’s Stone.  Think back to what you were doing when you were eleven years old – certainly not battling an evil wizard!  Developers often face challenges they have never heard or seen before, but that doesn’t mean they give up.

Go with Faith

We choose “good” every day.  In Harry Potter and the Philosopher’s Stone, the students are sorted into “houses” by a magical hat.  When it is Harry’s turn, he hopes with his whole heart to be sorted into Gryffindor, not Slytherin.  Throughout the series, this choice makes Harry question his own motives – is he good in his soul, or good by choice?  In the end, he learns that we all have to choose “good,” and wanting to choose, it is as good as being good in your soul.  Programming problems may not be the choice between good and evil, but developers often have to choose between a quick patch and a permanent solution.  Wanting to do a good job and developing a permanent solution may not be easy, but wanting to accomplish it is half the battle.

Reference: Pinal Dave (http://blog.sqlauthority.com)