MSSQL : Collation - a way out of case-insensitiveness

Sql Server Databases by default are case-insensitive, both for the object names as well as the data contained in them.

To change a database to case sensitive one, its collation needs to be changed.

To check for the existing collation, check the properties of database and look for a row with heading "Collation"
by default collation is set to "SQL_Latin1_General_CP1_CI_AS". This collation renders database as case-insensitive.

To make your database case-sensitive (as some third party software would expect them to be), we need to change the Collation for the Database.

There are many collations available. you can check them by executing the following statement
select * from ::fn_helpcollations()

Collation "Latin1_General_BIN" is found to work fine for the purpose of making Database case-sensitive.

To change collation, execute an alter statement against the Database

Alter Database TESTDB collate Latin1_General_BIN

Note that the existing tables and their columns would still follow the earlier collation unless recreated or rebuilt.

Collation on specific Tables and their columns as also be changed using 'Alter Table alter column varchar(10) collate Latin1_General_BIN null

MSSQL : Deleting duplicate rows

Select the duplicate key values into a holding table. For example:

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

Select the duplicate rows into a holding table, eliminating
duplicates in the process. For example:
SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

At this point, the holddups table should have unique PKs,
however, this will not be the case if t1 had duplicate PKs,
yet unique rows
SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

If count(*) returns more than 1 for certain rows,
determine which of the rows to delete which have the
duplicate keys but unique rows and then only process further.

Delete the duplicate rows from the original table.
For example:
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2


Put the unique rows back in the original table.
For example:
INSERT t1 SELECT * FROM holddups
    

DAC/Informatica src files connection

Informatica Server Variable > $PMSourceFileDir must be set exactly the
same as the DAC System Properties > InformaticaParamatereFileLocation as
follows: C:\oracleBI\DAC\Informatica\parameters (NB***no spaces in folder
name****)

Copy all files from OracleBI\dwrep\bin\Informatica\LkpFiles to the location specified in $PMSourceFilesDir

Copy all files from OracleBI\dwrep\bin\Informatica\SrcFiles to the location specified in $PMSourceFilesDir

Both Informatica and DAC must be able to access the folder specified above.

AWE and /3GB Switch for Windows

Windows 32-bit Operating Systems can only address upto 4GB of available memory. In order to access more than 4 gb of install memory we have to go for Addressing Windowing Extensions. For this set /PAE in the boot.init file and reboot the server.

As per blog http://blogs.msdn.com/oldnewthing/archive/2004/08/19/217087.aspx :

The two are independent. AWE is how programs access physical memory. PAE is how the CPU accesses physical memory. AWE requires PAE in order to allocate more than 4GB of physical memory. You need PAE in order to access more than 4GB of physical memory, AWE or no AWE


For systems having up to 4GB of RAM, Server spares only 2GB for user mode memory (rest of 2GB is for kernel). We can strech up to 3GB by using tag /3GB in boot.ini.

Note: /3GB switch would limit use of memory to only 3GB even if system has 16 GB RAM installed.

Windows 2003 Enterprise Edition can by default address up to 16GB of RAM.

How to update boot.init
Go to My Computer.
Right click -> Properties
Go to Advanced Tab
Startup and Recovery -> Settings
Edit.

Informatica shared memory

Informatica Server setup has a Load Manager where you can define the Shared Memory. This the total memory which server allocates to a particular session (defined by session parameter DTM Memory Buffer) default value 12000000.

MS SQL Server is too case sensitive

Databases are case sensitive, we all know. MS Sql takes it a level further (good or bad ?). It is case sensitive even to the table names.

Don't believe me, try out yourself !!!

Restore MSSQL DB from mdf,ldf and ndf files

MS Sql DB comprises mdf, ndf and ldf data files. If you happen to have taken a backup of the database (.bak), then restoring it on to a different instance is quite straight forward (using SQL Server Management Studio).
But in case you just have the database files with you, then one needs to attach it to the instance. Attach is a better technical term for this than restore.

Following is the way we can attach the Database to an instance:

use master
GO

EXEC sp_attach_db @dbname = N'NewDB',
@filename1 = N'C:\Program Files\MasterDB.mdf',
@filename2 = N'C:\Program Files\MasterDB_log.ldf',
@filename3 = N'C:\Program Files\DATA2.ndf',
@filename4 = N'C:\Program Files\DATA3.ndf'