wiki.borovicka.name programming:mssql

programming:mssql:backup

2018-06-21T19:48:03+02:00

Database Backup * simply backup the database on disk using T-SQL BACKUP DATABASE [DatabaseName] TO DISK = N'C:\path\to\backup.bak'

programming:mssql:closeconnections

2018-06-21T19:48:03+02:00

Close Connections * Code below quickly closes all connections to database. use master ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- your code ALTER DATABASE DBname SET MULTI_USER

programming:mssql:columntocsv

2018-06-21T19:48:03+02:00

Column to CSV * scalar valued function that converts given column into comma separated values The first (intuitive approach) * This approach is very intuitive and straightforward, however pretty slow SELECT @s = isnull(@s + ', ' + CONVERT (varchar(10),NumericValue), CONVERT (varchar(10),NumericValue)) FROM Measurement ORDER BY ColumnA;

programming:mssql:commontableexpression

2018-06-21T19:48:03+02:00

Common Table Expressions Standard query USE AdventureWorks2008R2; GO -- Define the CTE expression name and column list. WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. ( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) -- Define the outer query referencing the CTE name. SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear…

programming:mssql:explodedates

2018-06-21T19:48:03+02:00

ExplodeDate * function, which returns days between given range as a table, * uses CTE, no additional tables needed Sql statetement WITH DatesTable AS ( SELECT CONVERT(datetime,'2015-01-01') AS [Date] UNION ALL SELECT DATEADD(DAY,1,[Date]) FROM DatesTable WHERE DATEADD(DAY,1,[Date]) <= CONVERT(datetime,'2015-09-01') ) SELECT [Date] FROM DatesTable OPTION (MAXRECURSION 1000);

programming:mssql:fastcount

2018-06-21T19:48:03+02:00

Fast COUNT * This technique shows how get count from MSSQL fast, if you have lot of rows. * I is significantly faster than select count(*) ... * But in some cases result couldn't be exact. * The code bellow is code for creating stored procedure, which returns row count of table, which name was passed as a procedure parameter

programming:mssql:getdateonly

2018-06-21T19:48:03+02:00

MSSQL GetDateOnly * Function returns date part of the datetime. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: Tomas Borovicka -- Create date: 14.4.2009 -- Description: -- ============================================= ALTER FUNCTION [dbo].[GetDateOnly](@date DATETIME) RETURNS DATETIME AS BEGIN RETURN CONVERT(datetime,CONVERT(VARCHAR(25), @date, 104),104) END

programming:mssql:importfromexcel

2018-06-21T19:48:03+02:00

Import data from excel sheet to MSSQL Show configuation settings. sp_configure If you don't see 'Ad Hoc Distributed Queries' option in table. Allow show advance configuation settings. sp_configure 'show advanced options', 1 reconfigure Allow OpenRowset/OpenDatasource queries.

programming:mssql:lpad

2018-06-21T19:48:03+02:00

LPAD in MSSQL Funkce doplni retezec do dane delky, zadanym znakem. Right(Replicate('*',20) + string,20)

programming:mssql:miscellaneous

2018-06-21T19:48:03+02:00

Miscellaneous Difference between UNION and UNION ALL Unions are used to join tables on column level, usually when we need to join two sets from the same table into one result. But what is difference in UNION and UNION ALL? UNION ALL * simply joins two results and does not do anything else with result set.

programming:mssql:novinkymssql2008

2018-06-21T19:48:03+02:00

Novinky v MSSQL 2008 MS Fest 2009 - Novinky v MSSQL 2009 - , Slidy Table-value type Umožňuje definovat tabulkové datové typy. Např: Předávání proceduře neznámý počet parametrů. Příklad: create type IntegerTypeList as table ( Number int ) create procedure ShowList(@list IntegerTypeList readonly) AS BEGIN SELECT * FROM @list END

programming:mssql:readingcsv

2018-06-21T19:48:03+02:00

Reading CSV files in MSSQL bcp utility The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. See bsp utility on Technet. Allow xp_cmdshell -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this…

programming:mssql:resetidentity

2018-06-21T19:48:03+02:00

Reset identity * Identity property in MSSQL server auto increment column value. * When we remove rows identity column continues with incerment value, but sometimes we need reset identity to predefined value. * We have three choices: * DBCC CHECKIDENT

programming:mssql:restartservisce

2018-06-21T19:48:03+02:00

Restart MSSQL server instance * Restart MSSQL server instance using NET START/STOP. * Plan job for automaticall restart using plan :: CREATED BY BOROVEC :: restarting MSSQL serverices :: c:\RestartSQLServer.bat @ECHO OFF NET STOP SQLSERVERAGENT NET STOP MSSQLSERVER NET START MSSQLSERVER NET START SQLSERVERAGENT :: for every day at 23:00 clock use in cmd: at 23:30 /every:M,T,W,Th,F,S,Su c:\RestartSQLServer.bat :: CZ: at 23:30 /every:Po,Út,St,Čt,Pá,So,Ne c:\RestartSQLServer.bat :…

programming:mssql:runningprocesses

2018-06-21T19:48:03+02:00

Show running processes and killling EXECUTE SP_WHO -- See which process in running KILL 53

programming:mssql:sendmailclr

2018-06-21T19:48:03+02:00

Send mail using CLR Stored Procedure * In SQL server you have two ways how to send mail using T-SQL, first is xp_sendmail and second is Database Mail. But this functionality isn't in Express edition, how allow it describes article here. * This article describes step by step how to implement own SendMail procedure using CLR stored procedures.

programming:mssql:setidentity

2018-06-21T19:48:03+02:00

SET IDENTITY * Allows explicit values to be inserted into the column with the identity property. SET IDENTITY_INSERT SomeTable OFF GO INSERT INTO SomeTable(IdentityColumn) VALUES(1) GO SET IDENTITY_INSERT SomeTable OFF

programming:mssql:setnocount

2018-06-21T19:48:04+02:00

Disable printing number of affected rows - SET NOCOUNT * SET NOCOUNT allows disable (or enable) printing number of affetced rows as part of stored procedure result. * SET NOCOUNT ON - the count is not returned. * SET NOCOUNT OFF - the count is returned.

programming:mssql:shrinkdatabase

2018-06-21T19:48:04+02:00

Shrink DataBase * it shrinks database Code: use master BACKUP LOG dbToShrink WITH TRUNCATE_ONLY DBCC SHRINKDATABASE (dbToShrink)

programming:mssql:split

2018-06-21T19:48:04+02:00

Split * function splits text with given delimiter into a table set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE FUNCTION [dbo].[Split] ( @RowData NVARCHAR(2000), @Delimeter NVARCHAR(5) ) RETURNS @RtnValue TABLE ( ID INT IDENTITY(1,1), Data NVARCHAR(10) ) AS BEGIN DECLARE @Iterator INT SET @Iterator = 1 DECLARE @FoundIndex INT SET @FoundIndex = CHARINDEX(@Delimeter,@RowData) WHILE (@FoundIndex>0) BEGIN INSERT INTO @RtnValue (data…

programming:mssql:synchonizetables

2018-06-21T19:48:04+02:00

Synchronize tables * it anables synchronizing databases without data lost * it works on mssql server 2008 Code: PRINT 'Starting [dbo].[OsobaTyp] Syncronization' GO IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'xxxSyncxxxOsobaTyp') DROP TABLE [dbo].[xxxSyncxxxOsobaTyp] GO CREATE TABLE [dbo].[xxxSyncxxxOsobaTyp]( [ID] [int] NOT NULL, [Ident] [nvarchar](50) NULL, [Popis] [nvarchar](255) NULL, ) GO SET NOCOUNT ON INSERT INTO [dbo].[xxxSyncxxxOsobaTyp]([ID…

programming:mssql:t-sql

2018-06-21T19:48:04+02:00

Techniky T-SQL Transact-SQL (T-SQL) je proprietární rozšíření pro SQL od společností Microsoft a Sybase. Microsoft tento jazyk pužívá ve svých produktech Microsoft SQL Server . T-SQL poskytuje širokou škálu funkcionality pro programáory nebo databázové administrátory. Transact SQL umožňuje řízení SQL dotazů, podmíněné zpracování konstrukcemi IF, ELSE, CASE, nebo WHILE, využití zabudovaných funkcí MSSQL serveru, nebo také sekvenční zpracování pomocí kurzorů. Velká síla T-SQL je v uložených proce…

programming:mssql:testconnection

2018-06-21T19:48:04+02:00

Test connection to MSSQL server * Simply create an empty file with UDL extension, for example “Test_MSSQL_Connection.UDL” . * Double-click the file, SQL connectivity window should pop up. * Then you can test your connection to MSSQL server.