Store unicode data in Sql Server

Sql Server allows storing unicode data using appropriate datatypes i.e. NVARCHAR, etc. But we can also store unicode data in non-unicode data types by specifying COLLATE keyword too. Important point here is, no matter what method you use, you must specify ‘N’ before the unicode literal in your INSERT statements.

Example:

CREATE TABLE dbo.Unicode_Test
(
 Language_Name VARCHAR(80) NOT NULL
 , Native_Name NVARCHAR(100) NOT NULL
)
ON [PRIMARY]
GO

INSERT INTO dbo.Unicode_Test (Language_Name ,Native_Name)
VALUES (
 'Abkhaz' -- Language Name
 , N'аҧсуа бызшәа, аҧсшәа' -- Native Language
 ),
 (
 'Arabic' -- Language Name
 , N'العربية' -- Native Language
 ),
 (
 'Armenian' -- Language Name
 , N'Հայերեն' -- Native Language
 ),
 (
 'Assamese' -- Language Name
 , N'অসমীয়া' -- Native Language
 );
GO

Important part here is to have ‘N’ before the native language literal (unicode column value).

Here is the result:

Results of Unicode Table

When you INSERT the same data into the same table without ‘N’ the result will bevery different.

INSERT INTO dbo.Unicode_Test (Language_Name ,Native_Name)
VALUES (
 'Abkhaz' -- Language Name
 , 'аҧсуа бызшәа, аҧсшәа' -- Native Language
 ),
 (
 'Arabic' -- Language Name
 , 'العربية' -- Native Language
 ),
 (
 'Armenian' -- Language Name
 , 'Հայերեն' -- Native Language
 ),
 (
 'Assamese' -- Language Name
 , 'অসমীয়া' -- Native Language
 );
GO

Results without N

N tells Sql Server that the literal that is encapsulated in the quotes is a unicode data type and must be treated that way.

Hope this helps,

_SqlTimes

Read Full Post »

Sql Server Function: FILEPROPERTY

Today, we will look at one of the obscure functions of Sql Server. It is quite useful in certain situations.

FILEPROPERTY: This function allows us to query certain properties of a given database file. For example:

• Space Used
• Is this a Primary file or a Log file
• …etc

USE [tempdb]
GO

SELECT FILEPROPERTY('tempdev', 'IsPrimaryFile') AS [Is Primary File]
SELECT FILEPROPERTY('templog', 'SpaceUsed') AS [Number Of Pages Allocated]
GO

Results of FILEPROPERTY Query

Another example:

EXEC sp_msforeachdb 'use ?; Select ''?'' DBName, Name FileNme, fileproperty(Name,''SpaceUsed'') SpaceUsed from sysfiles'
GO

FILEPROPERTY with sp_msforeachdb

Hope this helps,

_SqlTimes

Read Full Post »

Sql Server procedure xp_fixeddrives

Today, we will look at one of the obscure built-in procedures in Sql Server. There is not much documentation that I could find on this, except a couple of other blog posts. May be for a good reason, but here it is.

EXEC xp_fixeddrives
GO

Gives you the free space available on all the drives attached to the machine on which Sql Server instance is running.

Result:

xp_fixedDrives

Hope this helps,

_SqlTimes

Read Full Post »

Sql Server: CDC (Change Data Capture) Configure Retention period

CDC (Change Data Capture) is a pretty robust technology. We’ve been using it for a while now and it works great. As it is built on top of another established robust technology Replication, I was more confident to continue using it in our production environment.

One aspect of CDC that has consumed a little more time for us was the CDC Cleanup operation (purging). Since there are a number of CDC tables available to query the internals of CDC progress, at at given time, it makes troubleshooting easier.

Our environment and my question from a few days ago:

We have CDC enabled on SQL Server 2008 with retention re-configured to a number other than the default 3 days (or something similar).

When we first configured it (with default options), it worked fine. But since the day we changed the retention period from default to something like 25 days, it stopped working correctly. It would run for many hours (15 to 20 hours). So, after a few days we disabled the job (not good, but we had other priorities to deal with than the data retention).

Now after 10 months, the disk is close to getting full. So we need to re-configure it and start purging data. A few days ago, just to test how it works, I ran a test run. I checked the timestamp of the oldest LSN and added a month to it and changed the retention period to something like 10 months + 2 days. So when the clean up job runs, it should come back with success immediately, as there is no data older than 10 months. But it does not. Its still runs for many hours.

Now I am starting to wonder if the retention period (column) in msdb.dbo.cdc_jobs table works or not.Aaron Bertrand mentioned in his blog that he did not have perfect success with this setting. Maybe this setting is not used by the Clean up job.

I posted this question a few days ago and after some experiments, I arrived at this answer and posted it back in the same site (StackOverflow).

Answer:

CDC cleanup job  relies on msdb.dbo.cdc_jobs table. As far as I know, the only way to configure retention period is by updating the retention column in the msdb.dbo.cdc_jobs table. You want to update the retention column of the record with 'cleanup' in job_type column. Retention is mentioned in number of minutes of data to retain. So if you want to retain data only for 1 day, update the column with 1440 (number of minutes in a day).

It could be done in two ways:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 349082;
-- or
UPDATE msdb.dbo.cdc_jobs SET retention = '349082' WHERE job_type = 'cleanup'

If you have more than one CDC running, then using the appropriate CDC instance name.

A Little more info:

When the CDC cleanup job runs, it purges all data in the CDC tables up to this retention date. You can stop theCDC cleanup job any time while it is running. When you start it, it will pick up from where it left off. So if you stopped the job while it is running today and start to run it tomorrow it will start from where it left off yesterday and perform purge until the retention period mentioned in the msdn.dbo.cdc_jobs table.

CDC is pretty robust technology.

Hope it help,

_SqlTimes

Read Full Post »

Sql Server: When was the last time STATISTICs were updated on a table

This is one of the most frequent questions during performance tuning (or during the maintenance plan discussions). There is a quick way to check this for all the STATISTICS on all tables. The function STATS_DATE returns the date when the stats were recomputed.

Example:

SELECT OBJECT_NAME(object_id) AS [Table_Name]
 , STATS_DATE(object_id, stats_id) AS [State_Updated_Date]
 , name AS [Stats_Name]
FROM sys.stats
WHERE OBJECT_NAME(object_id) NOT LIKE 'sys%'
ORDER BY STATS_DATE(object_id, stats_id) DESC

The function STATS_DATE() takes two parameters: object_id of the table and stats_id of the statistic.

Hope it helps,

_SqlTimes

Read Full Post »