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 … Okumaya devam et Difference between char nchar varchar nvarchar fields
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 … Okumaya devam et SQL Shrink Database Error : databaseid was skipped because the file does not have enough free space to reclaim.
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) … Okumaya devam et Shortest and Best Tsql Split Function Based on Xml Functionality
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 … Okumaya devam et Difference Between Tsql NewId() & NEWSEQUENTIALID()
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
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 … Okumaya devam et How to Get Changed Row Count in a Log Data File
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 = … Okumaya devam et How to Continue Insert against Foreign Key Constraint Error