# SQL SERVER – Working with Business Days in SQL Server – A Different Approach

Note: I recommend that you download XLeratorDB and try this problem along with me, this is going to be fun exercise and something new to learn.

Working with dates can be one of the more challenging aspects of IT. First, as developers we are stick with the calendar that has a non-uniform implementation of a year (approximately 365 97/400 days) causing the number of days in the year to fluctuate from year-to-year; an imprecise definition of a month (anywhere from 28 to 31 days); and requirements that our systems understand what the rest of the world means when the terms month and years are being bandied about. For example, if something is due in one month’s time does that mean in 30 days, in 4 weeks, on the same day of the month as today unless it’s not a business day, or something else?

Further, most of the time when our user are talking about days, they are not really so much concerned with the mathematical difference between two dates so much as they are concerned with the number of business days between 2 dates.  For example, when we promise overnight delivery, that promise is usually means delivery on the next business day.

SQL Server doesn’t really provide many tools that help with these type of calculations. True, SQL Server 2012 contains some interesting new date function like EOMONTH and DATEFROMPARTS, but any coding for business date logic is left to the developers

Fortunately, the team at Westclintech has a robust library of SQL Server CLR functions for handling business date logic. They have functions to calculate the:

• next business day;
• next n business days;
• previous business day;
• previous n business days;
• first occurrence of a weekday in a month;
• last occurrence of a weekday in a month;
• the number of business days between 2 dates (inclusive); and
• the number of business days between 2 dates (exclusive)

They also have a number of functions that do the following:

• convert elapsed time to a fraction of a year;
• count the number of days between two dates assuming 360-day year;
• calculate Western Easter;
• Calculate the end-of-month n months forward or backward;
• Calculate and exact date n months forward or backward;

and many more.

I have been trying their XLeratorDB library out and it allows me to do almost any kind of date calculation that I can think use very simple TSQL statements.

Here’s a typical example of the type of information that business users take for granted but that can quite difficult to calculate in SQL Server. Let’s say you needed to produce a report that showed average daily trading volumes the total number of trading days in a year as well as the total number of trading days by month for a year. With a quick internet search you discover that the stock exchange is closed on Saturdays and Sundays and on the following holidays:

• New Year’s Day (January 1st)
• Martin Luther King, Jr. Day (third Monday in January)
• Washington’s Birthday (third Monday in February)
• Good Friday (the Friday before Western Easter)
• Memorial Day (the last Monday in May)
• Independence Day (July 4th)
• Labor Day (the first Monday in September)
• Thanksgiving Day (the fourth Thursday in November)
• Christmas Day (December 25th)

Additionally, if a holiday falls on a Saturday it is observed on the previous Friday. If a holiday falls on a Sunday, it is observed on the subsequent Monday.

XLeratorDB has several functions that help to capture the holiday information above. In this example, I use the BUSINESSDATE, FIRSTWEEKDAY, LASTWEEKDAY, EASTER, and CALCDATE functions to calculate the holidays for the stock exchange.

```SELECT x.holiday ,CAST(x.hdate AS date) AS hdate FROM (SELECT wct.BUSINESSDATE(wct.CALCDATE(n.y,1,1),'M',0,'G','') AS [New Year's Day] ,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,1,1),'Mon')+14 AS [MLK Day] ,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,2,1),'Mon')+14 AS [Washington's Birthday] ,wct.BUSINESSDATE(wct.EASTER(n.y),'M',0,'P','') AS [Good Friday] ,wct.LASTWEEKDAY(wct.CALCDATE(n.y,5,1),'Mon') AS [Memorial Day] ,wct.BUSINESSDATE(wct.CALCDATE(n.y,7,4),'M',0,'G','') AS [Independence Day] ,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,9,1),'Mon') AS [Labor Day] ,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,11,1),'Thu')+21 AS [Thanksgiving Day] ,wct.BUSINESSDATE(wct.CALCDATE(n.y,12,25),'M',0,'G','') AS Christmas FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020))n(y) )p CROSS APPLY (VALUES ('New Year''s Day',[New Year's Day]) ,('MLK Day',[MLK Day]) ,('Washington''s Birthday',[Washington's Birthday]) ,('Good Friday',[Good Friday]) ,('Memorial Day',[Memorial Day]) ,('Independence Day',[Independence Day]) ,('Labor Day', [Labor Day]) ,('Thanksgiving Day',[Thanksgiving Day]) ,('Christmas',Christmas) )x(holiday, hdate) ```

Here are the first few rows of the resultant table.

```holiday               hdate
--------------------- ----------
New Year's Day        2013-01-01
MLK Day               2013-01-21
Washington's Birthday 2013-02-18
Good Friday           2013-03-29
Memorial Day          2013-05-27
Independence Day      2013-07-04
Labor Day             2013-09-02
Thanksgiving Day      2013-11-28
Christmas             2013-12-25
New Year's Day        2014-01-01
MLK Day               2014-01-20
Washington's Birthday 2014-02-17
Good Friday           2014-04-18
Memorial Day          2014-05-26
Independence Day      2014-07-04
Labor Day             2014-09-01
Thanksgiving Day      2014-11-27
Christmas             2014-12-25. . .```

SQL Server 2012 users will notice that the XLeratorDB CALCDATE function is very much like the DATEFROMPARTS function. In many cases, I find it easier to use the CALCDATE function, as it works just like the DATE function in EXCEL, whereas the DATEFROMPARTS function is much less flexible in terms of input. When I enter DATE(2013,3,0) in EXCEL or wct.CALCDATE(2013,3,0) they both understand that I mean 2013-02-28. However, when I try this statement in SQL Server 2012

```SELECT DATEFROMPARTS (2013,3,0) ```

I get the following result.

```Msg 289, Level 16, State 1, Line 9
Cannot construct data type date, some of the arguments have values which are not valid.```

Sure, I can probably code around that, but why would I want to?

XLeratorDB provides a function that store all the holidays in a scalar variable to be used in the calculation of business days. We can use this information to calculate the number of business days in a year.

```DECLARE @hol AS VARCHAR(MAX) = ( SELECT wct.NBD(CAST(x.hdate AS date)) FROM (SELECT wct.BUSINESSDATE(wct.CALCDATE(n.y,1,1),'M',0,'G','') AS [New Year's Day] ,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,1,1),'Mon')+14 AS [MLK Day] ,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,2,1),'Mon')+14 AS [Washington's Birthday] ,wct.BUSINESSDATE(wct.EASTER(n.y),'M',0,'P','') AS [Good Friday] ,wct.LASTWEEKDAY(wct.CALCDATE(n.y,5,1),'Mon') AS [Memorial Day] ,wct.BUSINESSDATE(wct.CALCDATE(n.y,7,4),'M',0,'G','') AS [Independence Day] ,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,9,1),'Mon') AS [Labor Day] ,wct.FIRSTWEEKDAY(wct.CALCDATE(n.y,11,1),'Thu')+21 AS [Thanksgiving Day] ,wct.BUSINESSDATE(wct.CALCDATE(n.y,12,25),'M',0,'G','') AS Christmas FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020))n(y) )p CROSS APPLY (VALUES ('New Year''s Day',[New Year's Day]) ,('MLK Day',[MLK Day]) ,('Washington''s Birthday',[Washington's Birthday]) ,('Good Friday',[Good Friday]) ,('Memorial Day',[Memorial Day]) ,('Independence Day',[Independence Day]) ,('Labor Day', [Labor Day]) ,('Thanksgiving Day',[Thanksgiving Day]) ,('Christmas',Christmas) )x(holiday, hdate) ) ```

Here’s a very simple example to get stated. We will calculate the number of business days in 2013 using the holiday variable that we just populated.

`SELECT wct.BUSDAYS('2013-01-01','2014-01-01',@hol) AS [Business Days]`

This returns the following result.

```Business Days
-------------
252```

If we wanted to know the number of business days for each year from 2013 to 2020, we could have used the following SQL.

```SELECT n.y AS [Year] ,wct.BUSDAYS(wct.CALCDATE(n.y,01,01),wct.CALCDATE(n.y+1,01,01),@hol) AS [Business Days] FROM (VALUES (2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020))n(y) ```

This produces the following result.

```Year Business Days
----------- -------------
2013           252
2014           252
2015           252
2016           252
2017           251
2018           252
2019           252
2020           253```

Here we calculate the number of business days in each month in 2013.

```SELECT DATENAME(m,wct.CALCDATE(2013,n.m,1)) AS [Month] ,wct.BUSDAYS(wct.CALCDATE(2013,n.m,1),wct.CALCDATE(2013,n.m+1,1),@hol) AS [Business Days] FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))n(m) ```

This produces the following result.

```Month                          Business Days
------------------------------ -------------
January                                   21
February                                  19
March                                     20
April                                     22
May                                       22
June                                      20
July                                      22
August                                    22
September                                 20
October                                   23
November                                  20
December                                  21```

And in this example, we explicitly calculate each business date in 2013. I am using a number-generating table-valued function from the XLeratorDB/math library to generate integer values from 1 to 252 and then passing the integer values into the BUSINESSDATE function to calculate each business date in 2013 starting from 2013-01-01.

```SELECT CAST(wct.BUSINESSDATE(wct.CALCDATE(2013,1,1),'D',k.seriesvalue,'F',@hol) AS date) AS [Work day] FROM wct.SeriesInt(1,252,NULL,NULL,NULL)k```

Here are some of the rows in the resultant table.

```Work day
----------
2013-01-02
2013-01-03
2013-01-04
2013-01-07
2013-01-08
2013-01-09
2013-01-10
2013-01-11
2013-01-14
2013-01-15
2013-01-16
2013-01-17
2013-01-18
2013-01-22
2013-01-23
2013-01-24
2013-01-25
2013-01-28
2013-01-29
2013-01-30
2013-01-31...
2013-12-19
2013-12-20
2013-12-23
2013-12-24
2013-12-26
2013-12-27
2013-12-30
2013-12-31```

XLeratorDB also provides a function that allows me to specify what the weekend days are when I want to do the business date calculation.

In this example, I want to calculate delivery dates for packages using the holidays that I have already calculated about, but I don’t want Saturday’s to be a weekend day, as deliveries can be made on a Saturday.

```SELECT n.shiptype ,CAST(wct.BUSINESSDATEWE(wct.CALCDATE(2013,11,25),'D',n.d,'F',@hol,0,NULL) AS date) AS [Expected Delivery] FROM (VALUES (1,'Overnight'),(2,'Two-day'),(3,'Regular Ground'),(4,'Regular Gound'),(5,'Bulk'))n(d,shiptype)```

This produces the following result.

```shiptype       Expected Delivery
-------------- -----------------
Overnight      2013-11-26
Two-day        2013-11-27
Regular Ground 2013-11-29
Regular Gound  2013-11-30
Bulk           2013-12-02```

As you can see, no delivery was predicted for Thanksgiving (2013-11-28), but Saturday (2013-11-30) was included.

The XLeratorDB business date functions are included in the XLeratorDB / financial libraries which run in SQL Server 2005, 2008, and 2012. I have explored just a small part of the functionality that is available for date calculation in SQL using these libraries and you can find out more about them by visiting www.westclintech.com where you can download a free 15-day trial. I think that these function make date calculations in SQL Server much easier. Let me know what you think.

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

# SQL SERVER – How to Allow Only Alphabets in Column – Create Check Constraint to Insert Only Alphabets

Here is the question I just received in email.

“Pinal,

I searched your blog at http://search.sqlauthority.com but I could not find a solution to my problem.

I have a column where I only want to allow alphabets. If anyone wants to insert number or any other value in that table, I want the user to see the error from SQL Server.

A very interesting question – user can create a check constraint over column to allow insertion of alphabets in the column. Here is a sample script where I demonstrate how users can create constraint over column first so it only allows alphabets.

```USE tempdb GO -- Create Test table CREATE TABLE TestTable (ID INT, FirstCol VARCHAR(100), CONSTRAINT FirstCol CHECK (FirstCol NOT LIKE '%[^A-Z]%')) GO -- This will be successful INSERT INTO TestTable (ID, FirstCol) VALUES (1, 'SQLAuthority') GO -- This will throw an error INSERT INTO TestTable (ID, FirstCol) VALUES (1, 'SQLAuthority 1') GO -- Clean up DROP TABLE TestTable GO```

If you know any other method, please post in the comment. I will post an alternate solution with due credit to this blog.

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

# SQLAuthority News – Notes from SQLLive 360 – Orlando, Nov 2013 – A Great Experience

Earlier I wrote about my 3 technical session at SQL Live in the event at Orlando, Florida in Nov19-20, 2013. I had a great time presenting at the SQL Live event. I presented following three sessions at the event Presenting 3 Session at SQL Server Live, Orlando, Florida.

• SQT10 “What Did I Do?” T-SQL Worst Practices
• SQW12 Ancient Problems and Modern Solutions – Troubleshooting CPU
• SQW04 Database Performance Tricks Every SharePoint Admin Must Know

I have been to Florida before but it was my first time visiting Orlando and I enjoyed every bit of it. The event was very well organized and attendees were extremely energetic. Every session was very well received. The best part was every session was about 1 hour & 15 minutes and there was enough time between two of the sessions. The break between session provided ample time for attendees and speaker to network among themselves.

Here are the few photos from the event.

Pinal at SQLLive

Great Audience at SQLLive

Pinal at SQLLive

Round Table of Performance Tuning

I had a great time with Aaron Blackner at Pluralsight booth

Pinal presenting at SQLLive

Pinal presenting at SQLLive sporting Pluralsight Vision

Audience gathered for Pluralsight Giveaway

Hard Rock Cafe at Orlando, Florida

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

# Personal Technology – How to do Exercise and Stay Relaxed while in Office – Tips for Developers

While the modern day worker is usually no longer subjected to 10, 12, or 20 hour shifts, and can look forward to much more pleasant working conditions, modern office life has its own fair share of health and safety concerns.  For example, sitting at a desk and staring at a computer screen all day, as most developers do, is not as back-breaking as hauling heavy loads or working in a factory, but the human body was not built to sit and stare at awkward angles all day.  Our wrists are poorly designed for the angles forced on them by keyboards, and carpal tunnel develops.  Computer screens cause eyestrain and dryness.  Computer chairs cause back and neck pain.  Is it any surprise that many developers go home and feel as if they were working in a factory?

Because modern employers recognize that happy, healthy employees are productive employees, there are a wide range of exercises you can do to keep you loose, limber, and energized.  Many of them you can easily accomplish without leaving your chair or requiring a lot to space to jump and lunge, and best of all you won’t be disturbing any nearby co-workers.

### Stretching

Stretching is an easy way to take a quick break from work and pause to recognize any sore or tense spots.  When you get really absorbed in a task, it can be easy to ignore the fact your neck hurts, you’re staring at your screen at an awkward angle, and that you’re slouching.  Stretching helps you readjust and better yet, gives you the kind of 30 second pause you might need to break through writer’s block or a logical problem.

Stretch 1: intertwine your fingers and reach your arms towards the ceiling, palms up.  Roll your neck.  When you release, check your posture.  I bet you’re sitting up straight now!

Stretch 2: Lift your legs out straight in front of you, flexing your feet and ankles.

Stretch 3: Lean left and right, hinging at the waist (as if you were trying to bring your head to your hip).  Stretch your left hand above you as you lean right, and your right hand above you as you lean left  – as long as you don’t feel like you’ll fall out of your chair.

Stretch 4: Keeping your lower body as still as possible, twist your upper body as far to the left as possible, then as far to the right as possible.  Do not over-twist or force your upper body further than is comfortable using the table or arm of the chair.  These stretches should energize, not hurt!

### Avoiding Carpal Tunnel

As mentioned before, keyboards and computer mice hold our wrists at an awkward, upward angle, pinching important nerves and tendons between the bones of the hand.  Symptoms start with numbness and tingling, and quickly move on to pain and the loss of mobility.  Ergonomic keyboards and mice can only do so much – we have to stop and stretch periodically as well.

Stretch 1: Stop typing and give yourself a little hand massage.  Whatever feels good – the moving your wrists out of the typing position is what is key.

Stretch 2: Roll your wrists clockwise and counterclockwise for a few revolutions.

Stretch 3: Go for a walk. This isn’t going to directly impact your wrists, but the break from typing is most important.

Other solutions: wrist braces to hold your wrist in a neutral position are helpful when breaks are impossible or you already have some nerve damage.  A better chair or back brace to help posture will also help your wrists, believe it or not.  If at all possible, trade tasks with another employee every week or two, to help the both of you avoid repetitive motion injuries.

### Eyestrain

Staring at a computer screen causes eye strain.  This is a simple fact.  No matter what kind of anti-glare coating, eye shade, or LED screen you have, eye strain will happen.  The most common culprit – not blinking!  Blinking is an involuntary reaction, on par with breathing or digestion.  But something happens to human physiology when we are confronted with a shining monitor, and studies show our blinking rates can drop by half when staring at a screen!  It is no wonder our eyes get tired and dry.

Exercise 1: This one is easy.  Stick a bright post-it note on your screen, somewhere where you will be forced to see it all the time, and write “BLINK” on it in large, bold letters.  Believe it or not, this will help.

Exercise 3: Keep your monitor at eye level (even if this means propping it up on books or lowering your chair), at most a few inches below eye level.  This will help your posture, too.

Exercise 4: Take a walk.  That’s right!  It’s good for your wrists and good for your eyes.  Even if you just go get a sip of water once an hour, your eyes (and wrists, and back, and legs…) will thank you.

### Get Up and Move!

Perhaps you have a hard time sitting still, or you would just like to get a little more exercise.  Either way, there are certainly exercises you can do, even in a cubicle, to get you up and moving during the day.

Exercise 1: Get out of your chair and stretch.  Interlacing your fingers and stretching your hands above your head (palms up), stretch up on your tip-toes, and then bend down to touch your toes.  It gets the blood flowing.

Exercise 2: If you have the room, do some lunges.  Place your hands on your hips and lunge forward with your right leg, bending your left leg 90°.  Do not extend your knee past your ankle, as this can cause damage to your joints.  In one swift motion, return to standing and repeat on the left side.

Exercise 3: This one requires less room.  Stand up out of your chair and be sure to move it out of your way.  Then slowly bend your knees and lower down, as if you were sitting in an invisible chair (remember, move your real chair, no cheating!).  Again, be sure to keep your knee in line with your ankle to avoid injury.  To make it harder, raise your arms over your head and try these squats.

Exercise 4:  If you are blessed with a lot of office space, get down on the floor for some push ups and crunches.  To avoid getting too sweaty work in sets of 10 or 20 a couple times a day.

Exercise 5: If you don’t want to wrinkle your work clothes, try working your abs with a “plank.”  Support the weight of your body in a push-up position (knees up or down, with arms fully extended or on your elbows).  Aim for holding a plank for 30 seconds (work up to it in 10 second intervals if needed), and work up to a minute or two.  A yoga mat can store neatly in your office, in a drawer or closet, if you are really dedicated to office fitness!

### The Easiest Exercise

If you don’t have the space for some of these moves, of if your office is very open and you feel embarrassed about sitting in an invisible chair in front of all your co-workers, there is one sure-fire way to help eye-strain, wrist fatigue, boredom, and writer’s block.  Take a walk!  Walk around the perimeter of the cubicles, walk around the building outside.  Walk to get your lunch a block further away than usual.  Walk down the stairs to use a further away printer.  You don’t need any more equipment than just your two legs and comfortable shoes.  Even if you can only leave your desk for quick 30-second bursts, it will add up over the course of the day.

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

# SQL SERVER – Wrap on Series of Temporary Statistics

This is the final post in the series of the Temporary Statistics. I have previously written about this subject in a few posts. I have received quite a few questions about this series so I decided to include all the questions in this one blog post. Before you read this blog post, here are a few blog posts related to this subject.

Here are a few questions which I have received.

Question: Can I update the temporary statistics?
Answer: No, the user cannot create or update temporary statistics. SQL Server only can create or update temporary statistics. However, the user can drop the statistics with the help of DROP STATISTICS command.

Question: Is there any way the user can retain created temporary statistics permanently on SQL Server.
Answer: You can script out the temporary statistics and create them in the database with the steps described in the blog Scripting Statistics for Read-Only Database and Making it Permanent

Question: If database is read-only where are the temporary statistics are stored?
Answer: In the TempDB. Hence when you restart your SQL Server services, all the temporary statistics disappear.

Question: How can I know that if the statistics created is temporary or permanent?
Answer: You can run the SELECT query on sys.stats catalogue view and checkout the status of the index. If the is_temporary column is 1 for statistics it is temporary and if it is 0 it is permanent. Read more about that over here:  Statistics for Read-Only Database are in TempDB.

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

# SQL SERVER – Finding Frequently Running Query and Elapsed Time – Notes from the Field #005

[Note from Pinal]: This is a fifth episode of Notes from the Field series. What happens when your server performs badly. The very first reaction is identifying what is running on the server. Once you identify what is running on the server we want to identify two of the most crucial information about running query – 1) How often the query runs and 2) What is the duration of the query. In this blog post Brian hits very important concept about performance tuning.

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

While I primarily focus on security, as a DBA it’s important for me to be able to performance tune as well. As a consultant, one of the things I’ve seen that affects performance are queries that run often. If we just look at a given query running one time, it doesn’t set off our flags because the query runs quickly and doesn’t take up a lot of resources. However, the problem is that the query in question runs often. As a result, the cumulative effect does cause a performance hit to the overall system.

Finding the Queries Running Frequently
Often times, the client doesn’t realize that a particular query is running as often as it does. They may be using Profiler or a server-side trace and looking at the execution time for queries and, of course, they’re focusing on long running queries. As a result, they don’t see the queries executing thousands of times and causing the whole system to drag. A simple query such as the following will reveal what’s running often based on a minimum count of executions.

```DECLARE @MinCount BIGINT ; SET @MinCount = 5000; SELECT st.[text], qs.execution_count FROM sys .dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text( qs.sql_handle ) AS st WHERE qs.execution_count > @MinCount ORDER BY qs.execution_count DESC; ```

This isn’t to say that every query running a lot is causing a significant performance issue. However, this it helps to have a list to look at and investigate. Even a marginal improvement on a query that executes often can result in a noticeable improvement of a system.

Looking at Total Elapsed Time

One way to determine which queries to focus on first is to look at the total_elapsed_time column in sys.dm_exec_query_stats. What you may find is the query that runs most often isn’t the one that has run the longest cumulatively. Let’s modify our query to include the total_elapsed_time column and to order by it in descending order.

```DECLARE @MinCount BIGINT ; SET @MinCount = 5000; SELECT st.[text], qs.execution_count, qs.total_elapsed_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text( qs.sql_handle ) AS st WHERE qs.execution_count > @MinCount ORDER BY qs.total_elapsed_time DESC;```

High execution count and high total elapsed time are good indicators of queries to look at in addition to the infamous “long running queries.”

A Caveat

The sys.dm_exec_query_stats and sys.dm_exec_sql_text dynamic management view/function reset when SQL Server restarts as they are kept in memory. Therefore, the execution count is cumulative from the last time SQL Server has restarted. Therefore, if SQL Server has restarted recently, such as after patching, you’re not going to receive accurate stats based on the usage of the application.

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

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

# SQL SERVER – Scripting Statistics for Read-Only Database and Making it Permanent

This is a follow up blog post to my earlier blog posts about statistics for Read-Only Database. Please read the following blog post in this series to get further idea about this subject:

One question which keeps on coming to my Inbox which was based on Read-Only Database and its best practices. Everybody wanted to know that they do understand that they have a read-only database and its statistics is in the TempDB. When TempDB is restarted it resets the statistics. If the user knows that next time they are going to execute the same kind of queries on the Read-Only database and it will once again create the similar statistics in TempDB, is there any way to create those statistics in the Read-Only database so next time when the server restarts (or service restarts) the statistics remains in the database instead of disappearing with TempDB?

In simple words the question is – How to move statistics from temporary status  to permanent status in the Read-Only database?

This is very interesting question and indeed a very easy one. First of all users should only move the statistics from the TempDB database to Read-Only database if he is confident that the same statistics are created multiple times and there is absolutely no change in the queries which are running against the Read-Only database. In my career I have seen Read-Only database which are refreshed daily, weekly or at intervals. If you have such database in that case, you should not think of moving the statistics from the TempDB till the database is in a consistent stage where data is not moving much.

Here is a summary of how we can do the same.

Step 1: Change Database status to Read-Write

```USE [master] GO ALTER DATABASE [dbname] SET  READ_WRITE WITH NO_WAIT GO ```

Step 2: Follow the blog post to create statistics from Read-Only Database How to Script Statistics from SSMS? – Scripting Statistics is Disabled in SSMS

Step 3: Execute the script generated from Step 2

Step 4: Change Database status to Read-Only

```USE [master] GO ALTER DATABASE [dbname] SET  READ_ONLY WITH NO_WAIT GO ```

Let me know what you think of this blog post series.

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