MS SQL bulk export / import

To export in native format execute from the command-line:

bcp MyDatabase..MyTable format nul -n -x -f C:\MyFolder\MyTableFormat.xml -T
bcp MyDatabase..MyTable out C:\MyFolder\MyTableData.dat -n -T

To deal with tables containing XML datatype the format file (MyTableFormat.xml) must be modified: add MAX_LENGTH=”8001″ to the FIELD element that represents column containing XML!

To import data in existing table that already contains data execute SQL:

print 'Inserting MyTable with KEEPIDENTITY ...'
INSERT INTO [dbo].[MyTable]
WITH (KEEPDEFAULTS, KEEPIDENTITY)
([Id],[Col1],[Col2],[ColXml])
SELECT [Id],[Col1],[Col2],CONVERT(xml, [ColXml])
FROM
OPENROWSET
(
BULK 'C:\MyFolder\MyTableData.dat',
FORMATFILE='C:\MyFolder\MyTableFormat.xml''
)
as t1
WHERE t1.Id NOT IN (SELECT Id FROM [dbo].[MyTable])

If you don’t want to preserve Id values from imported table then instead of previous example execute following:

print 'Inserting MyTable with autoincrement ...'
INSERT INTO [dbo].[MyTable]
WITH (KEEPDEFAULTS)
[Col1],[Col2],[ColXml])
SELECT [Col1],[Col2],CONVERT(xml, [ColXml])
FROM
OPENROWSET
(
BULK 'C:\MyFolder\MyTableData.dat',
FORMATFILE='C:\MyFolder\MyTableFormat.xml''
)

Advertisements

SQL Express DB performance – auto-close evil

A quote from the original post:

By default, Express Edition creates databases with the Auto Close option enabled. This option allows SQL Server to close the physical operating system files for the database when all connections to the database are closed. The advantage here is that individual workstation installations can have database files copied around as easily as document files. This is not so advantageous in a server setup, as the overhead of opening the files and starting up the database can cause poor response times.

To check this setting for an individual database, right click the database in Management Studio, choose Properties, go to the Options page, and verify the state of the Auto Close option (it should be at the top of the list). You’ll almost certainly want to set this to False for every database on a dedicated server, with the possible exception of extremely seldom used databases.

See the original post here.

MS SQL – Table Variables & Temporary Tables (Transact-SQL)

Temporary Tables

  • visible only in current session of SQL Server;
  • automatically droped on session close;
  • can’t have foreign key constraints;
  • are created in temporary database of SQL Server tempdb;
  • should manually drop to keep cached only in memory.
CREATE TABLE #temp (id int);

INSERT INTO #temp
   SELECT id FROM Table1 WHERE ;

-- Use #temp data here.

DROP TABLE #temp;

Table Variables

  • if you are using SQL Server 2000 or higher;
  • always stay in memory;
  • don’t need to be dropped;
  • more flexibility.
DECLARE @temp table (id int);

INSERT INTO @temp
   SELECT id FROM Table1 WHERE ;

-- Use #temp data here.

Conclusion

If you have SQL Server 2000 or higher always should use table variables.

MS SQL – Get Installed Languages (Transact-SQL)

Useful query to list installed language codes:

select alias, msglangid from sys.syslanguages;

See more on MSDN: http://msdn.microsoft.com/en-us/library/ms190303.aspx

Create/Drop MS SQL FullText Catalog & Index (Transact-SQL)

-- Change default full-text language to Neutral!
-- It can be skipped, if specific language code will be provided when creating index.

USE Master
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default full-text language', '0';
RECONFIGURE WITH OVERRIDE;
GO

USE [MyFulltextDatabase]
GO

-- Drop catalog if was created
-- Drop indexes first

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[MyTable]'))
   ALTER FULLTEXT INDEX ON [dbo].[MyTable] DISABLE
GO

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[MyTable]'))
   DROP FULLTEXT INDEX ON [dbo].[MyTable]
GO

IF  EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'FullTextCatalog')
   DROP FULLTEXT CATALOG [FullTextCatalog]
GO

-- Create new catalog in current database

CREATE FULLTEXT CATALOG [FullTextCatalog]
ON FILEGROUP [PRIMARY]
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION [dbo]
GO

-- Create a new index on the specified table

CREATE FULLTEXT INDEX
	ON [dbo].[MyTable]
	KEY INDEX [PrimaryKeyName]
	ON [MyFulltextDatabase]
	WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON [dbo].[MyTable] ADD ([MyColumn1] LANGUAGE 0x0)
GO
ALTER FULLTEXT INDEX ON [dbo].[MyTable] ADD ([MyColumn2] LANGUAGE 0x0)
GO

More info about FullText indexes: http://msdn.microsoft.com/en-us/library/ms187317.aspx

ALTER VIEW drops index!

Recently I got error:

View ‘dbo.MyMaterializedView’ is not an indexed view. Full-text index is not allowed to be created on it.

All database scripts are being in Visual Studio using Database project type. Upgrades also are being managed by making separate scripts folder in same project and including those scripts in predefined sql file: “Scripts/Post-Deployment/Script.PostDeployment”. In this way we can include upgrade to the project output – big sql file for deploy.

We are using fulltext index in our DB. To use fulltext predicates (CONTAINS, FREETEXT) on view we created materialized view (WITH SCHEMABINDING):

CREATE VIEW [dbo].[MyMaterializedView]
with schemabinding
AS
SELECT
dbo.TabelA.PKCol as [Id],
dbo.TableA.Col1,
dbo.TableB.Col2
FROM dbo.TableA INNER JOIN dbo.TableB
ON dbo.TableA.PKCol = dbo.TableB.FKTableA
GO
-- Create unique clustered key for use in FullText index
CREATE UNIQUE CLUSTERED INDEX [PK_MyMaterializedView]
ON [dbo].[MyMaterializedView] ([Id]) ON [PRIMARY]
GO
-- Create FullText index
CREATE FULLTEXT INDEX
ON [dbo].[MyMaterializedView]
KEY INDEX [PK_MyMaterializedView]
ON [MyFullTextCatalog]
WITH CHANGE_TRACKING AUTO
GO
-- Specify columns to FullText index (neutral language is used)
ALTER FULLTEXT INDEX ON [dbo].[MyMaterializedView] ADD ([Col1] LANGUAGE 0x0)
GO

After a while developer created new upgrade that changes view by adding a new column to it:

ALTER VIEW [dbo].[MyMaterializedView]
with schemabinding
AS
SELECT
dbo.TabelA.PKCol as [Id],
dbo.TableA.Col1,
dbo.TableB.Col2,
dbo.TableA.Col2
FROM dbo.TableA INNER JOIN dbo.TableB
ON dbo.TableA.PKCol = dbo.TableB.FKTableA
GO

As result search query that uses this view fails after DB have been upgraded!
And most strange thing is that deploy script didn’t show any error message while executing ALTER VIEW statement 😦
The root of the problem is trivial: ALTER VIEW statement drops all indexes that were applied on view – also FullText index!
Conclusion: indexes must be recreated after altering view:

ALTER VIEW [dbo].[MyMaterializedView]
with schemabinding
AS
SELECT
dbo.TabelA.PKCol as [Id],
dbo.TableA.Col1,
dbo.TableB.Col2,
dbo.TableA.Col2
FROM dbo.TableA INNER JOIN dbo.TableB
ON dbo.TableA.PKCol = dbo.TableB.FKTableA
GO
-- Alter view command drops view index!
CREATE UNIQUE CLUSTERED INDEX [PK_MyMaterializedView]
ON [dbo].[MyMaterializedView] ([Id]) ON [PRIMARY]
GO
-- Also Full Text index must be recreated!
CREATE FULLTEXT INDEX
ON [dbo].[MyMaterializedView]
KEY INDEX [PK_MyMaterializedView]
ON [MyFullTextCatalog]
WITH CHANGE_TRACKING AUTO
GO
ALTER FULLTEXT INDEX ON [dbo].[MyMaterializedView] ADD ([Col1] LANGUAGE 0x0)
GO