SQL tip: sub-query in select column statement

Thats why SQL rocks 😀


select top 100
	b.Id,
	b.Title,
	cast(
	(
		select
			case
				when count(i.Id) = 0 then 0
				else 1
			end
		from AnnotationFiles i where i.BibliographicRecord_Id = b.Id
	) as bit)
	HasImage
from BibliographicRecords b

Posted in MS SQL. Tags: . Leave a Comment »

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''
)

How to shrink MS SQL database log file

USE [$(databasename)]
GO

-- Drop old transactions log data (just mark for deletion)
BACKUP LOG [$(databasename)] WITH NO_LOG -- TO DISK='D:BackupSQLServer$(databasename)_log.trn'
GO

-- Physically shrinks the log file
DBCC SHRINKFILE($(databasename)_log, NOTRUNCATE)

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