Difference between char nchar varchar nvarchar fields

Query Result
Query Result

Almost most .Net Developers knows difference between char nchar varchar nvarchar fields. Or i they think they know.

Here are some samples to tell about differences with results,

In this sample our database is configured as Turkish_CI_AS Collation
/*
* Our Database>Tables>Fields are Turkish_CI_AS Collation
* So it is already for save Turkish specific fields
*/

/*Variables*/
Declare @char char(5) = ‘abcş ‘
Declare @nchar nchar(5) = ‘abcş ‘
Declare @varchar varchar(5) = ‘abcş ‘
Declare @nvarchar nvarchar(5) = ‘abcş ‘

/*Lets look at texts results*/
Select @char as txtChar,@nchar as txtNChar,@varchar as txtVarChar,@nvarchar as txtNVarChar
/*Lets look at character length*/
Select Len(@char) as txtLenChar,Len(@nchar) as txtLenNChar,Len(@varchar) as txtLenVarChar,Len(@nvarchar) as txtLenNVarChar
/*Lets look at fields byte length results*/
Select DataLength(@char) as txtDataLenChar,DataLength(@nchar) as txtDataLenNChar,DataLength(@varchar) as txtDataLenVarChar,DataLength(@nvarchar) as txtDataLenNVarChar

Results,

Query Result
Query Result

 

So “abcş ” totally 5 chars with space, but you will see that LEN function gives us only char length, but if you look at real storage size it will reflect the real size. Unicode (N) fields like nchar and nvarchar data types keeps more size on server because every character will be defined with 2 bytes. So in this example keeping data in N fields increase the storage size.

But what will be different, if my server was in a different collation ?

So to simulate this, i will change collations on demand of query like this,

Just change the declared variables and run the query,
/*Variables*/
Declare @char char(5) = 'abcş ' Collate Latin1_General_CI_AS
Declare @nchar nchar(5) = 'abcş ' Collate Latin1_General_CI_AS
Declare @varchar varchar(5) = 'abcş ' Collate Latin1_General_CI_AS
Declare @nvarchar nvarchar(5) = 'abcş ' Collate Latin1_General_CI_AS

And Results will be,

Query Result
Query Result

 

So you will see that “ş” character is saved as “s” because your local database will not able to keep unicode characters unless you marked them.

So you should change your query to store Unicode characters in a server is not match with your Collation setting,

 


/*Variables*/
Declare @char char(5) = N'abcş ' Collate Latin1_General_CI_AS
Declare @nchar nchar(5) = N'abcş ' Collate Latin1_General_CI_AS
Declare @varchar varchar(5) = N'abcş ' Collate Latin1_General_CI_AS
Declare @nvarchar nvarchar(5) = N'abcş ' Collate Latin1_General_CI_AS

will be give you the first result set and data lengths.

As a result,

  • Using N or not, means this data type can store Unicode Characters but you should specifiy this in your query like N’your chars’
  • If you don’t need to store N-Unicode fields do not use N-Unicode data types. Otherwise you will spend more storage area
  • So if you are using a ORM tool like Entity Frame Work, you can change default datatype settings or mappings for data types
  • If you don’t make an international project you prefer to use your own matching Collation settings for your server or your table or your field
  • Char fields are fixed-length data types, so use that fields if you are using Post Code or fixed number or like identity fields. It will increase query performance.

SQL Shrink Database Error : databaseid was skipped because the file does not have enough free space to reclaim.

If you can’t shrink database mdf file even there is lots of empty space in it, and getting an error says that “databaseid was skipped because the file does not have enough free space to reclaim.” try to this script;

Get file id to shrink,

SELECT * FROM sys.sysfiles

Shrink file that you want,

DBCC SHRINKFILE (1,1) –Second parametet is target Size in megabytes

Shortest and Best Tsql Split Function Based on Xml Functionality

There are lots of split function for tsql, most of them is lots of lines of code !
Always on some cases we need to split concatted fields with a delimeter.

I think this this one is the best and the shortest split function for tsql based on xml functionality

CREATE FUNCTION [dbo].[SplitByXmlBase](@str varchar(max),@delimeter char)
RETURNS @table TABLE(Value varchar(4000))
AS
Begin
Declare @x XML
Select @x = cast(''+ replace(@str,@delimeter,'')+ '' as xml)


Insert @table
select t.value('.', 'varchar(4000)') as Value from @x.nodes('/A') as x(t)
return
End

Difference Between Tsql NewId() & NEWSEQUENTIALID()

Every one knows that NewId() generates a guid in mssql but what is NewSequentialId() ? Well if you tried to run NewSequentialId function you will get an error. NewSequencialId is used for default value of an table. If you have a table and with a guid field put that function for default value so it gains performance and especially ordered guid for inserted records. It important that ordered records shoult be same with the ordered guid on some times. So this makes ordering property, and performance inserting and selecting on that table.

Show Database Tables Row Count, Used Space

There is lot of alternative to that code shows space usage, row count, index size and unused space of tables in a database.

CREATE TABLE #sizereport (
name varchar(250),
rows varchar(250),
reserved varchar(250),
data varchar(250),
index_size varchar(250),
unused varchar(250)
)

Insert #sizereport (name,rows,reserved,data,index_size,unused)
exec sp_msforeachtable 'sp_spaceused "?"'

Select * From #sizereport
drop table #sizereport

How to Get Changed Row Count in a Log Data File

This code helps you to show how many rows are deleted, inserted or updated that read from database log file.

Declare @tableName as Varchar(100);
Set @tableName = 'SampleTableName';

Select
Sum((Case When Operation ='LOP_INSERT_ROWS' Then 1 End)) [Rows Inserted]
,Sum((Case When Operation ='LOP_MODIFY_ROW' Then 1 End)) [Rows Updated]
,Sum((Case When Operation ='LOP_DELETE_ROWS' Then 1 End)) [Rows Deleted]
From ::fn_dblog(null, null) logdata
inner Join
(
Select [Transaction id],[End Time]
From ::fn_dblog(null, null) sub1
Where
Operation='LOP_COMMIT_XACT'
And Exists
(
SELECT [Transaction id]
FROM ::fn_dblog(null, null) sub2
Where
allocUnitName + '.' Like '%.' + @tableName + '.%'
and sub1.[Transaction id]=sub2.[Transaction id]
)
) commitedtran on commitedtran.[Transaction id] = logdata.[Transaction id]
And commitedtran.[End Time] between CAST('10/21/2007' AS DATETIME) and CAST('1/1/2008' AS DATETIME)

How to Continue Insert against Foreign Key Constraint Error

On some stiations you need to insert batch records. And because of primary key constraint all insert roll back and can’t continue. So using IGNORE_DUB_KEY=ON is makes you ability to insert without rollback whole transaction. So without any error you can insert whole batch except dublicate records.

ALTER INDEX PK_Calls ON .Calls
REBUILD WITH(IGNORE_DUP_KEY = ON )

LDAP Query in Ms Sql Server

Here is an query example to query on LDAP from Microsoft Sql Server,

SELECT *
FROM OPENQUERY(BankEuropaAD,
'SELECT ipPhone, displayname,givenName,sn,SamAccountName, protocolSettings
FROM ''LDAP://trbeuadc02''
WHERE objectCategory = ''Person'' AND
msExchUserAccountControl=''0'' AND ipPhone >''1'' ')
Rowset_1