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 – 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

SubSonic – Custom Query

SubSonic – how to execute custom query using QueryCommand and read a value from single row:

string result = String.Empty;
int id = 1;
string sqlCommand = "select Col1 from Table1 where id = @IdParam";

QueryCommand cmd = new QueryCommand(sqlCommand);

// Remember to set provider name in case if multiple databases are being
// used in the current project
// (because DataService remembers the latest executed connection string)!
cmd.ProviderName = DBTable1.Schema.Provider.Name;

cmd.AddParameter("@IdParam", id, DbType.Int32);

using (IDataReader rdr = DataService.GetReader(cmd))
{
    if (rdr.Read())
    {
        result = Convert.ToString(rdr[rdr.GetOrdinal("Col1")]);
    }
    rdr.Close();
}