Sql Server Function FORMAT to format currency, strings, text and numbers for different cultures

Quick one today:

Sql Server has interesting functions; Today we’ll look at one.

Let’s start with a simple example:

--
-- FORMAT function with integer
--
SELECT FORMAT(12312312,'#,###,###,###,###')
GO

Result:

Format_Function_Custom

 

 

--
--  FORMAT function with date
--
DECLARE @DT DATETIME = '2014-10-24'
SELECT FORMAT(@DT, 'yyyy/MM/dd', 'en-US') AS [US_Date_with_slash]
     , FORMAT(@DT, 'd', 'en-US')          AS [US_Date_with_hyphen]
     , FORMAT(@DT, 'D', 'en-gb')          AS [GB_Date_with_slash]
     , FORMAT(@DT, 'd', 'en-gb')          AS [GB_Date_with_hyphen]
GO

Format_Function_Date

Read MSDN for more information.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Start, Stop, Delete server side trace using t-sql

In troubleshooting some issues with databases, sometimes you need to capture server side traces. Sometimes Profiler could help capture specific events as needed. Another option is to use the server side stored procedures to capture trace into a file.  There are several points to keep in mind when you run server side traces, but for this post, the content will be limited to stopping and starting server side traces.

These are the steps I follow to create server side traces:

1. Step 1: Create trace definition using sp_Trace_Create
2. Step 2: Add individual events to capture using sp_Trace_SetEvent
3. Step 3: Add filters to limit the amount of transactions you capture using sp_Trace_SetFilter
4. Step 4: Start the trace using sp_trace_setstatus

Now, following are the steps to start, stop and delete the definition from Sql Server.

Get a list of all traces that are running. TraceID is used as a parameter for setting status.

--
-- Get a list of all traces running
--
SELECT *
FROM sys.traces
GO

Now using the trace ID set the status to either start, stop or delete.

--
-- Start trace
--
EXEC sp_trace_setstatus 2, 1
GO

--
-- Stop trace
--
EXEC sp_trace_setstatus 2, 0
GO

--
-- Delete trace definition
--
EXEC sp_trace_setstatus 2, 2
GO

Note: Please note that Microsoft is planning to discontinue this feature as they encourage usage of Extended Events

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: Update ‘Auto Created’ Statistics and all statistics on a table

Recently, at one of our customer sites, we noticed this interesting anomaly.

In this database, there are several tables and each table has several statistics. Statistics could be created in several ways.

• Index based statistics
• Column Statistics
  • User created
  • Auto created Statistics (that are created by Sql Server)

Problem

When they perform nightly maintenance, some how the ‘auto created’ statistics were not getting updated. The ‘is_auto_update_stats_on’ & ‘is_auto_update_stats_async_on’ options are set to 1. But still some statistics are not getting updated.

Usually, there are many ways to update statstics:

• Use maintenance plan to perform ‘Update Statics’ (most popular)
• Use “UPDATE STATISTICS” command to update selective tables or selective statistics
• sp_updatestats : Updates all statistics in the entire database
• etc

In a  database environment, given the size and traffic and possible maintenance window duration, the option we choose varies. If you have sufficient maintenance window, we could use a SQL Job to update statistics on all tables in a database using a maintenance plan approach or ‘sp_updatestats’. But in some cases, we may not have enough time to run updates on all statistics. So, we end up selecting a list of statistics and running “UPDATE STATICS <stats_name>” on each of them.

Solution

Guess what happend, in this case. Yes, the other (auto created) statistics will not be updated. And this is what happened at our customer site. They could not afford to perform complete update stats each night and ended up choosing a few stats each night. This inadvertently resulted in ‘auto created’ statistics from not getting updated.

Index stats will be updated when you rebuild indexes.

The lesson is not to depend on the database settings alone (‘is_auto_update_stats_on’ & ‘is_auto_update_stats_async_on’). Have your own maintenance plans to update statistics all on all tables.

Options:

1. Use UPDATE STATISTICS
2. Use sp_updatestats
3. Use maintenance plans

Option 1

Rather than choosing each statistic name in “UPDATE STATISTICS” command, just give the table name. It updates all statistics on that particular table. Below is the image from our lab server as I was running UPDATE STATISTICS <table name>.

In the below image, is a snapshot from the table while the statistics were being updated. Hence you see some are updated and some are still in the process of getting updated. Interesting point is that the ‘auto created’ statistics also get updated using this command with table name.

--

-- Update stats for the entire table

--

UPDATE STATISTICS dbo.CPE_RA_Archive WITH FULLSCAN

Result

Update Statistics

Option 2

--
--  Update statistics for entire database
--
EXEC sp_updatestats 'resample'

Result

UpdateStatics with Procedure

Option 3

Use maintenance plan to perform “Update Stats” on the entire database. This in turn calls UPDATE STATISTICS on each table resulting in updating both user & auto created stats.

Hope this helps,
_Sqltimes

Read Full Post »