0

Create a Database using SQL

by Jon 27. April 2011 22:37

Dynamic SQL saves the day.  I needed to script create some databases but I want to specify a number of parameters to be used as part of the Create Database Command:

  1. I want to specify where the database files will be created
  2. I want the all databases to be Prefixed with a standard set of characters
  3. I want to create one or more databases
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
-- Please set the following Two Variables Correctly

Declare @InstallDbPath as nvarchar(500)
Declare @DbPrefix as nvarchar(50)
set @InstallDbPath = 'D:\Databases\QA\'
set @DbPrefix = 'QA_'

------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
-- Dont Change any SQL after this line, just set the Variables at the above
-- Some Variables that we will use
Declare @InstallDbName as nvarchar(50)
Declare @SQLCmd as nvarchar(max)
Declare @DbName as nvarchar (50) -- DbName Changes, its the name of the database we want to create

-- TODO : Change this to run in a loop, reduce repeated SQL

------------------------------------------------------------------------------------
-- Change the Variable @DbName  to be the name of the databse you want to create

-- Create Database1
set @DbName = 'Database1'
set @SQLCmd = 'CREATE DATABASE ' + @DbPrefix + @DbName + ' ON PRIMARY
(NAME = ''' + @DbPrefix + @DbName + '_Data'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Data.mdf'', SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
LOG ON
(NAME = ''' + @DbPrefix + @DbName + '_Log'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Log.mdf'', SIZE = 4096KB , MAXSIZE = 2GB, FILEGROWTH = 10%)' 
exec (@SQLCmd)

-- Create Database2
set @DbName = 'Database2'
set @SQLCmd = 'CREATE DATABASE ' + @DbPrefix + @DbName + ' ON PRIMARY
(NAME = ''' + @DbPrefix + @DbName + '_Data'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Data.mdf'', SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
LOG ON
(NAME = ''' + @DbPrefix + @DbName + '_Log'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Log.mdf'', SIZE = 4096KB , MAXSIZE = 2GB, FILEGROWTH = 10%)' 
exec (@SQLCmd)

-- Create Database3
set @DbName = 'Database3'
set @SQLCmd = 'CREATE DATABASE ' + @DbPrefix + @DbName + ' ON PRIMARY
(NAME = ''' + @DbPrefix + @DbName + '_Data'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Data.mdf'', SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
LOG ON
(NAME = ''' + @DbPrefix + @DbName + '_Log'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Log.mdf'', SIZE = 4096KB , MAXSIZE = 2GB, FILEGROWTH = 10%)' 
exec (@SQLCmd)

Tags:

SQL

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

1

Select Top N using a stored procedure parameter

by Jon 13. September 2010 17:22

If you want to allow your users to select the top N rows to reduce the strain your database, but you want to be able pass N in as a parameter as a stored procedure just wrap the Parameter in () and it wont complain.

Alter Procedure cp_StoredProcedureName

@TopN as integer

as

select top (@TopN) Field1,Field2 from TablleName

where Field3 = "Something"

Tags:

SQL

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