How to create temporary SQL table? Just look at below CREATE TABLE statement.
CREATE TABLE #tempTable (
ID int,
Name char(30) )
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SNAPSHOT / SERIALIZABLE (Transact-SQL)
12 Sep
Posted by Jayce as Programming - 632 views
SET TRANSACTION ISOLATION LEVEL is the controls that lock and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. There are 5 levels for SET TRANSACTION ISOLATION LEVEL. There are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT and SERIALIZABLE. By default, SQL Server set ‘SET TRANSACTION ISOLATION LEVEL READ COMMITTED’ as default. So what does each of them mean?
SET DEADLOCK_PRIORITY LOW / NORMAL / HIGH (Transact-SQL)
12 Sep
Posted by Jayce as Programming - 231 views
By setting DEADLOCK_PRIORITY, system will determine the relative importance that the current session continue processing if it is deadlocked with another session. It can be set with numeric too.
For example, if SET DEADLOCK_PRIORITY LOW, this session will become victim of deadlock if other session is set to NORMAL / HIGH. While SET DEADLOCK_PRIORITY NORMAL, it will become victim only when other session is set to HIGH.
When SET NOCOUNT ON, the count is not returned. While SET NOCOUNT OFF, the count is returned. The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
Example,
– SET NOCOUNT to ON to no longer display the count message.
SET NOCOUNT ON;
GO
SELECT TOP(5) product
FROM dbo.test
WHERE producy LIKE ‘A%’;
GO
What is a SQL stored procedure? SQL stored procedure is a saved collection of Transact-SQL statements that that can take and return user-supplied parameters. It is kind of the programming for SQL. It is supporting Microsoft .NET Framework common language runtime (CLR) method with Microsoft SQL Server 2008.
Example of CREATE PROCEDURE (Transact-SQL),
IF OBJECT_ID ( ‘dbo.sp_test’, ‘P’ ) IS NOT NULL
DROP PROCEDURE dbo.sp_test;
GO
CREATE PROCEDURE dbo.sp_test
AS
SELECT LastName, FirstName, JobTitle, Department
FROM dbo.test;
GO
And the stored procedure can be executed by
EXECUTE dbo.sp_test;
GO
– Or
EXEC dbo.sp_test;
GO
– Or, if this procedure is the first statement within a batch:
dbo.sp_test;
