SQL SERVER – Storing Data in Hindi, Chinese, Gujarati and Tamil (or any other) Language in the Same Table

Long time ago, I have written a blog which talks about multi language support available with SQL Server.

SQL SERVER – 2005 – Multiple Language Support

I have been getting below questions on regular basics and it still baffles me:

  1. How do I pass Hindi parameters in SQL Server procedure as I want to insert a row with Hindi text?
  2. What is the collation I need to use if I have to store Tamil in SQL Server tables?
  3. I am inserting the data in Gujarati but while selecting I am getting “???”

Here are some basic rules which you may want to keep in mind. While storing Unicode data, the column must be of Unicode data type (nchar, nvarchar, ntext). Another rule is that the value must be prefixed with N while insertion.

If you are using a stored procedure then while passing the parameter to stored procedure make sure that N is used before the string. Also make sure the parameter declared within the stored procedure which are used for carrying those variable are also defined as Unicode.

Here is the sample script and the output which can make sure understand the things. I have used Google translate to do translation and I am not sure how accurate it is in Kannada, Tamil and Telugu.

use master
go
If db_id('SQLAuthority') is not null
drop database SQLAuthority
go
set nocount on
go
Create database SQLAuthority
go
use SQLAuthority
go
Create Table MultiLanguage_Will_Not_Work (i int, j varchar(10), k varchar(100))
go
-- Hindi
Insert into MultiLanguage_Will_Not_Work values (1, 'with N', N'मेरा नाम Pinal है')
go
-- Tamil
Insert into MultiLanguage_Will_Not_Work values (2, 'without N', 'என் பெயர் PINAL ஆகிறது')
go
Select * from MultiLanguage_Will_Not_Work
go
Create Table MultiLanguage (i int,j varchar(10), k nvarchar(100))
go
-- Gujarati
Insert into MultiLanguage values (1, 'with N', N'મારું નામ પિનલ છે')
go
-- Telugu
Insert into MultiLanguage values (2, 'without N', 'నా పేరు PINAL ఉంది')
go
-- Kannada
Insert into MultiLanguage values (3, 'with N', N'ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ')
go
Select * from MultiLanguage
go
use master
go
drop database SQLAuthority
go

Here is the output

Same would work for any language as well. It is important for us to know the basics and this blog post is one of many basics blogs that I plan to write. Do let me know which topic you would like me to revisit as part of this – back to basics series? More ideas you give me, more interesting the series can be.

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

SQL SERVER – Configure, Monitor and Restore Backups from Mobile & Web browser

So tell me if this is a fantasy or a reality: You are on a beach, sipping on your cocktail and looking at the sea when you get a frantic call from your boss, “One of the junior DBAs has just deleted all of the data from a critical database”. You say “Hold on”, switch to a browser on your smartphone, go to your dashboard on SqlBak.com and click a Restore button on your latest transaction log backup. In a few seconds you get back to your boss and say: “I have fixed it”. Your boss is stunned thinking you are a magician and makes a note to give you a raise and a bonus while you continue enjoying the beach…

Well, the beach and the pay raise are a fantasy, but restoring your database in a couple of clicks through just a browser on your smartphone is a service you can use today – it is called SqlBak.

This is how it looks like – you select a backup job, click Restore and the latest backup will be restored to your SQL Server:

Let’s see how to make it works

First – boring stuff: go through 10 seconds signup with your Facebook, Twitter or Google account. Then (and here’s the secret to a magic of SqlBak working through a browser) – download and install a small service program to run on your SQL Server computer – SqlBak Client.

To connect this program to your SqlBak account, enter your “Secret Key” from SqlBak Dashboard

into the SqlBak Client (it will prompt you for it):

 

The service program (SqlBak Client) has practically no interface and after connecting it to your SqlBak account you can forget about it (it will also auto-update). All of the configuration, monitoring and restore interface will be online through SqlBak.com, not through the program directly.

Now the fun part begins.

Go to the SqlBak Dashboard and click “Add New Job” button.  Select the computer where you have previously installed and connected the SqlBak Client and authenticate with the SQL Server – exactly like you’d do in SSMS.

Press Continue and you get to the Job Settings page. Select the databases you want to backup:

Then Press “Add Backup Destinations” to select where you want to store the backups – you have an option of sending it to you Local/Network folder, FTP, Amazon S3, Dropbox, Google Drive, MS OneDrive or Azure Storage:

I’ve selected to send backups to Amazon S3 and a Network Folder:

Lets schedule the full backup to run every 6h and differential backup every 1 hours:

By default it takes standard backup files and compresses them to zip – lets keep it like that:

You would probably want to receive a confirmation email at least on any failure – so fill out that field:

Now save the job and press Run Now to see if everything runs correctly:

You would see a log like this:

Now we can forget about it – it will email us if anything is wrong. If we get back to it in a few days – heres what we would see a backup history like this:

For each successful backup you have the Download and Restore buttons. Let’s click on the Restore buttons to restore one of the Differential backup. Select the destination to restore from (Amazon S3 or a network folder in our case):

You will see a restore progress bar:

In a few minutes your database is restored.

Absolutely brilliant! I know of no other product that would allow me to do the same from just a browser. It took quite a few pictures to describe the process, but in reality it takes just a few minutes and pretty self-explanatory.

In addition to backups this service would monitor that your SQL Server is alive and would email you if it goes offline – very useful.

Companies that want to provide Sql Server private label backups themselves can configure the service so it is branded to their company.

Some companies with strict security requirements may be hesitant of storing SQL Server login credentials with SqlBak. I’ve contacted the developers and they have told me that they are working on a version that would give user a choice of entering login credentials on the client – this way they would never leave the SQL Server.

Conclusion: I have seen many SQL backup programs before, but I am seriously impressed with the ease of using SqlBak – the only SQL backup software where you can configure, monitor and restore backups right from the browser.

If you enter the promo code Pinal2015 on the Sign Up page and you will receive $20 credit to your account.

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

SQL SERVER – COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression

In earlier blogs on “Fundamentals of Columnstore Index”, I took a simple approach on taking a tour of how to create a simple Columnstore index. Having said that, SQL Server 2014 supports columnstore and columnstore archival compression for columnstore tables and indexes. Columnstore tables and indexes are always stored with columnstore compression. Most data warehouses have some data that is frequently accessed and some that is accessed more infrequently.

You might be wondering the use case scenario for these commands. For example, in reality the data may be partitioned by date and the most recent data is accessed much more frequently than older data. In such cases the older data can benefit from additional compression at the cost of slower query performance. I talk to a lot of customers about this special feature:

  • To enable this scenario SQL Server added support for archival compression of SQL Server column stores from SQL Server 2014.
  • The archival compression option is enabled on a per-table or partition (object) basis; a column store can contain objects with archival compression applied and objects without. To allow for easy extensibility of existing on-disk structures, archival compression is implemented as an extra stream compression layer on top of Columnstore index implementation.
  • The Columnstore compression implementation transparently compresses the bytes being written to disk during the column store serialization process and transparently decompresses them during the deserialization process. Stream decompression is always applied when data is read from disk. Data is not cached in memory with stream compression.

The further reduction obtained by archival compression is substantial, ranging anywhere from 25% to 60%+ depending on the data. These are rough numbers based on what I have seen at customer interactions in the past.

  • Use COLUMNSTORE_ARCHIVE data compression to compress columnstore data with archival compression.

Let us look at how this can be achieved using TSQL commands.

To Apply Compression:

The simple TSQL commands to play with ColumnStore and columnStore Archive bits in single ALTER script:

-- Adding Archive bit for single Partition
ALTER TABLE tbl_myColumnStore
REBUILD PARTITION
= 1 WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;
-- Adding Archive bit for all Partition
ALTER TABLE tbl_myColumnStore
REBUILD PARTITION
= ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;
-- Adding Columnstore for all parition and Archive for few Partition
ALTER TABLE tbl_myColumnStore
REBUILD PARTITION
= ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE ON PARTITIONS (1,3)) ;

Use COLUMNSTORE data compression to decompress archival compression. This resulting data will continue to be compressed with columnstore compression.

To Remove the Archive Compression

Similar to above commands, we can also use to remove our archival compression on specific partitions or on objects. A representative command looks like:

-- Turning Archive bit off to Columnstore for all Partitions
ALTER TABLE tbl_myColumnStore
REBUILD PARTITION
= ALL WITH (DATA_COMPRESSION =  COLUMNSTORE) ;

As I wrap up this blog, I would like to know how many of you out there are using the archival columnstore in your environments. How much compression are you getting and what is the space savings because of this? I would love to hear from your experience as we learn together.

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

Interview Question of the Week #008 – Write Scripts to Convert String to Title Case or Proper Case

Just received a question in email:

“I just got out of the interview and I was asked to write scripts to convert a string to Title Case. I was also asked to refer to the website over here to validate my answer after I complete writing script.

Do you think it is not fair to ask such questions in an interview?”

Well, this time the question is not about writing scripts, but more about if this kind of questions is a good question for interview or not. The question is a bit open ended and my answer can be vague or full of philosophy of it depends. However, I will try to answer it bit more honest and clear.

I believe all questions are good question in the interview. It is never about the question’s validity, but more about quality of answer and attitude decides the capability of the user. If I ever face a situation where I am asked questions to write script to convert a string to title case, I would happily sit down and attempt to write it if I want the job and I am up for the challenge. If I do not know how to write it, I will honestly say I do not know and let the interviewer decide the next step. If you believe this kind of question is inappropriate for the job which you have applied, well, you can always mention that after you solve it.

Well – in my early career, I have written similar script and I have posted that on this blog post well. You can refer that in this blog post – SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case. I strongly suggest that you read the comments as those are packed with the wealth of the information. Here is the script from the blog post.

CREATE FUNCTION udf_TitleCase (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE
@Index INT
DECLARE
@Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString =
STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @Index <= LEN(@InputString)
BEGIN
SET
@Char = SUBSTRING(@InputString, @Index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF
@Char != ''''
OR
UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString =
STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET
@Index = @Index + 1
END
RETURN
ISNULL(@OutputString,'')
END

You can use this script as follows:

SELECT dbo.udf_TitleCase('This function will convert this string to title case!')

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

SQL SERVER – Script to Find Strings with Same Characters

One of my blog followers asked me if it is possible to check whether all the characters of a column value is same. For example if there are values like KKK and KKT, the value KKK should be the output as all characters are same.

There is a method without using WHILE Loop

Create the following data set

CREATE TABLE #TEMP(COL VARCHAR(20))
INSERT INTO #TEMP
SELECT 'AAAAA' AS DATA UNION ALL
SELECT 'NKKKKKKKKKK' UNION ALL
SELECT '32000000' UNION ALL
SELECT '11111111' UNION ALL
SELECT '1002' UNION ALL
SELECT 'OOOOO'UNION ALL
SELECT 'M'

Run the following code

SELECT
COL FROM #TEMP
WHERE
REPLACE(COL,LEFT(COL,1),'')=''

The result is

COL
--------------------
AAAAA
11111111
OOOOO
M

The logic is to get first character and replace that character by empty string in the column. If the final result is empty string, it means that all characters are same. Otherwise all characters are not same.

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

SQL SERVER – FIX – Error: 905, Severity: 21, State: 1 – Database ‘xxx’ cannot be started in this edition of SQL Server because it contains a partition function ‘xxx’.

Recently one of my blog reader contacted me via mail and told that after upgrading from Enterprise Evaluation Edition to Standard Edition, the database was not accessible. To troubleshoot, I have asked them to share SQL Server ERRORLOG. There are various ways to read Errorlog. Here is the method to read Errorlog:

  • In Object Explorer, expand a server, expand Management, and then expand SQL Server Logs.
  • Right-click a log and click View SQL Server Log.

Other ways to get Errorlog are explained in detail by Balmukund (b|t) on his blog (Help : Where is SQL Server ErrorLog?)

In the Errorlog, I asked him to look for any error related to the database which is not accessible and soon they shared below messages:

2015-02-20 13:47:36.65 spid7s      Error: 905, Severity: 21, State: 1.
2015-02-20 13:47:36.65 spid7s      Database ‘My_Database’ cannot be started in this edition of SQL Server because it contains a partition function ‘myRangePF1′. Only Enterprise edition of SQL Server supports partitioning.
2015-02-20 13:47:36.65 spid7s      Error: 933, Severity: 21, State: 1.
2015-02-20 13:47:36.65 spid7s      Database ‘My_Database’ cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Explanation

From the error we can see that the database is using partition function which is enterprise only feature. Here is the link which has edition comparison.  The database contains one or more partitioned tables or indexes. Standard edition of SQL Server cannot use partitioning. Therefore, the database cannot be started correctly. Partitioned tables and indexes are not available in every edition of Microsoft SQL Server. Books online topic “Features Supported by the Editions of SQL Server 2012” has complete list of feature.

Solution

I asked him to restore the database MDF and LDF files to another instance which is either developer, enterprise or enterprise evaluation edition. Then get rid of the features which are enterprise only feature. They can easily be found using the DMV sys.dm_db_persisted_sku_features

USE <DatabsaeName> -- Change the name to the database where we need to check
GO
SELECT *
FROM sys.dm_db_persisted_sku_features

Note that the query has to be run in the database which is under question, not the master database. Here are the possible values on SQL Server 2014.

  • TransparentDataEncryption
  • Compression
  • ChangeCapture
  • ColumnStoreIndex
  • InMemoryOLTP
  • Partitioning

Once the object referring to that feature is removed, the backup can be taken and restored on “lower” edition of SQL Server.

Here is another error which can be received while restoring database using compression

Msg 909, Level 21, State 1, Line 1
Database ‘SqlAuthority’ cannot be started in this edition of SQL Server because part or all of object ‘PageCompressionTest’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

I always advice to run the DMV and check the features whenever there is a downgrade of edition. Even though evaluation to standard is an upgrade but I would call it as downgrade because there are few feature of enterprise editions would not be available in standard edition.

Have you ever encountered any downgrade scenarios and got some errors?

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

SQL SERVER – Finding Top Offenders in SQL Server 2012 – Notes from the Field #068

[Note from Pinal]: This is a 68th episode of Notes from the Fields series. Performance tuning gets easier with SQL Server 2012. For the first time, you can capture a workload and find your top offenders inside of Management Studio without having to write a single line of T-SQL or use any third party tools.

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 in their career – how to find top offenders in SQL Server. 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 biggest mistakes with performance tuning is not knowing where to start.  Sometimes performance tuning can be like finding a needle in a haystack.  This is because you might not have a great process to help you find your top offenders. In this short five minute video below you will be able to utilize extended events to aggregate and group completed statements so you can find your top offenders without having to write any T-SQL code.

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)