0

The Easy Way to fix Server Collation in MS SQL Server

by Jon 27. September 2010 21:11

When you have different developers, customers and databases your database collation is bound to get out of sync.  Making sure you have matching collation is important because it can cause problems when it comes to running SQL queries and can impact performance if you are joining text fields.  There isn't an easy way to change the collation across an entire server because the collation is stored against the fields in each table.  I looked at a number of different scripts but found the following script was the easiest way to fix the problem.  Simply set @toCollation to the collation you want in your database, and run the query.  The query will output a List of ALTER COLUMN SQL Queries which you can copy into a query window and execute to fix your database.

declare  @toCollation sysname 

SET    @toCollation = 'Latin1_General_CI_AS' --  Database default collate

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +

       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +

       CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'

            WHEN DATA_TYPE in ('text','ntext') then ''

            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 

             THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )

            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END

       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE

                                           WHEN 'YES' THEN 'NULL'

                                           WHEN 'No' THEN 'NOT NULL' 

END

FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES

ON  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME

AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA

WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')

AND TABLE_TYPE = 'BASE TABLE'

 and COLLATION_NAME <> @toCollation

You will find most of the queries will work, for the queries that dont work due relational joins you can open the table in enterprise manager in design mode and fix the small number of remaining offending fields manually really quickly.  Within moments you have a database with matching collation accross all its tables.

 

Tags: , ,

SQL | MSSQL Server

0

Calculating IOPS per user in an Asp.net MSSQL System

by Jon 11. May 2010 09:52

I just had a customer contact me asking for read/write IOPS per user so they could correctly specify a SAN.  IOPS means I/O Operations per second, so they want to know how many disk operations occur under normal usage on a server.  

How do we find this out for a dotnet based system?  Well basic really when you think about it.  I logged into our development server and Started performance monitor and logged the background Disk Read/Write /Sec and wrote down the average values when our system wasnt being used.  I cleared the display and then started to use our web based system in anger, flipped back over to performance monitor and wrote down the new average Disk Read/Write /Sec values.  Subtracting the values I had just written down from the background usage of the server gives you the IOPS per user.

Tags: , , , , ,

asp.net | SQL | TechSupport

0

When you have multiple optional parameters in a stored procedure...

by Jon 30. March 2010 09:47

If you have a number of optional parameters in a stored procedure (that are disabled with boolean logic) that will dramatically change the way the query works depending on the parameter it is best to add (WITH COMPILE) to the stored procedure, to ensure that the best query plan is always used.  The first time a stored procedure is executed, the parameters you have passed will determine the way it is executed.  If you first run the stored procedure against an empty database, used some duff test fields, or you have a report based stored procedure that works in a different way depending on parameters the first call may not be the ideal query plan.  By forcing the query to be recompiled for these optional varied you will massively boost performance.  This feature should be used sparingly, but there are often places where it needs to be essential (Some reporting stored procedures/stored procedures used to search)

ALTER Procedure [dbo].[Report_ExportReworkData]
    @ProductOption as nvarchar(50),
    @SerialNumber as nvarchar(50),
    @WorksOrder as nvarchar(50)

-- Make sure this gets compiled each time to avoid incorrect parmeter sniffing
WITH RECOMPILE

as
select
    ......
where
    (len(@ProductOption) = 0 or WorksOrders.PartNo = @ProductOption) and
    (len(@WorksOrder) = 0 or WorksOrders.WorksOrderNo = @WorksOrder) and
    (len(@SerialNumber) = 0 or  MfgRecords.SerialNumber = @SerialNumber)

Tags: , , , , , , ,

development | SQL

Powered by BlogEngine.NET 2.0.0.36
Original Design by Laptop Geek, Adapted by onesoft, and finally some tiny tweaks by JonAlb