ict.ken.be

Since MsSQL 2008 you can use the MERGE command for inserting or updating, here is a small example I use with mojoPortal. I call it from a custom PageInitEventHandler and allows me to track what pages a user visits.

CREATE PROCEDURE [dbo].[kenTracking_Pages_Upsert]

@UserGuid	uniqueidentifier,
@SiteGuid	uniqueidentifier,
@PageGuid	uniqueidentifier

AS

SET NOCOUNT, XACT_ABORT ON;

MERGE [dbo].[kenTracking_Pages] WITH (HOLDLOCK) AS target
USING (SELECT @UserGuid, @SiteGuid, @PageGuid) AS source (User_Guid, Site_Guid, Page_Guid)
ON (target.User_Guid = source.User_Guid) AND (target.Site_Guid = source.Site_Guid) AND (target.Page_Guid = source.Page_Guid)
WHEN MATCHED THEN
UPDATE SET Visits = Visits + 1, DateModified = GETDATE()
WHEN NOT MATCHED THEN
INSERT
(
User_Guid,
Site_Guid,
Page_Guid
)
VALUES
(
@UserGuid,
@SiteGuid,
@PageGuid
);

RETURN @@ERROR;
GO

Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Solution:

1. Right Click on your database, choose properties
2. Goto the Files
3. Change the owner textbox to "sa"
4. Press OK

SELECT queries

• use (NOLOCK)


• use EXISTS instead of IN
  eg. SELECT * FROM HumanResources.Employee E WHERE E.EmployeeID = ( SELECT EA.EmployeeID FROM HumanResources.EmployeeAddress EA WHERE EA.EmployeeID = E.EmployeeID)
  SELECT * FROM HumanResources.Employee E WHERE EXISTS ( SELECT EA.EmployeeID FROM HumanResources.EmployeeAddress EA WHERE EA.EmployeeID = E.EmployeeID)


• check if left join is faster
  eg. SELECT ProductID FROM Production.Product WHERE ProductID NOT IN ( SELECT ProductID FROM Production.WorkOrder);
  SELECT p.ProductID FROM Production.Product p LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID WHERE w.ProductID IS NULL;

INSERT queries

• use UNION ALL
  eg. INSERT INTO MyTable (FirstCol, SecondCol)
  SELECT 'First' ,1
  UNION ALL
  SELECT 'Second' ,2
  UNION ALL
  SELECT 'Third' ,3


• Use INSERT TOP where order is not important
  eg. INSERT INTO InserTesttValue (ID) SELECT TOP (2) ID FROM TestValue
  INSERT TOP (2) INTO InsertTestValue (ID) SELECT ID FROM TestValue

STORED PROCEDURE

• include SET NOCOUNT ON statement


• use schema name with object name


• use IF EXISTS (SELECT 1) instead of (SELECT *)
  eg. IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'MyTable' AND type = 'U')


• try to avoid using SQL Server cursors whenever possible


• keep the Transaction as short as possible


• use TRY-Catch for error handling
  eg. BEGIN TRY --Your t-sql code goes here END TRY BEGIN CATCH --Your error handling code goes here END CATCH


• do NOT use the prefix “sp_” in the stored procedure name


• call the stored procedure with qualified name (with schema)


• use the sp_executesql stored procedure instead of the EXECUTE statement
  eg. DECLARE @Query NVARCHAR(100); SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'; EXECUTE sp_executesql @Query, N'@Age int', @Age = 25;

To enable remote connection on SQL Server 2008 Express, see the step below:

• Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
• Enable TCP/IP protocol for SQL Server 2008 Express to accept remote connection.
• Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 Express allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.

Use the following steps to configure the Windows Firewall:

• Open the Windows Firewall dialog box and click the Exceptions tab.
• Click Add Program and Browse to find sqlbrowser.exe and click OK.
• Click Add Program and Browse to find sqlservr.exe. Click OK.
• Click Add Port and enter "SQL Service" for Name, 1433 for Port number, and select the TCP radio button.
• Click OK on the Windows Firewall dialog.

--table "xyz" cannot be reorganized because page level locking is disabled.

--ALTER INDEX IDX_NAME ON TABLE_NAME SET (ALLOW_PAGE_LOCKS = ON)

select A.Name as InName,ob.Name as DBName from sys.indexes A
left outer join sys.objects ob on ob.object_id=A.Object_id
where allow_page_locks=0 and ob.type='U'

/*
SET NOCOUNT ON
DECLARE @DBName nvarchar(50), @INName nvarchar(50)
DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)
Declare @execstr nvarchar(200)
--PRINT '-------- Vendor Products Report --------'
DECLARE Index_cursor CURSOR FOR
Select A.Name as InName,ob.Name as DBName from sys.indexes A
left outer join sys.objects ob on ob.object_id=A.Object_id
where allow_page_locks=0 and ob.type='U'
-- Select only allow_page_locks 0 and User Tables
OPEN Index_cursor
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName +' ' + @INName
--PRINT @INName
SET @ODBName = ltrim(rtrim(@DBName))
SET @OINName = ltrim(rtrim(@INName))
SELECT @execstr = 'ALTER INDEX '+@OINName+ ' ON '+
@ODBName+' SET (ALLOW_PAGE_LOCKS = ON)';
EXEC (@execstr);
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
END
CLOSE Index_cursor
DEALLOCATE Index_cursor
*/