Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

If you are using a stored procedures that use temporary tables, then be aware that temporary tables created within a stored procedure use the tempdb database’s collation instead of the current user database’s collation. Tempdb is created at installation and is assigned the default collation.

Use 'COLLATE DATABASE_DEFAULT' to resolve the collation conflict, e.g.

SELECT * FROM TableA
WHERE ID COLLATE DATABASE_DEFAULT
IN (SELECT TableAID FROM #temp)

References:
SQL SERVER – Cannot resolve collation conflict for equal to operation
Collation Conflict

While loop in sql

Here is how to do while loop in sql:

--script to copy UserOrganisationId record from User table to User_Organisation

Declare @UserID as int

Declare @MaxUserID as int

Declare @OrgID int

select @UserID = MIN(intuserid) from [user]

select @MaxUserID = MAX(intuserid) from [user]

WHILE (@UserID <= @MaxUserID)

BEGIN

if exists(Select * from [user] where intuserid=@UserID)

begin

select @UserID=intuserid, @OrgID=UserOrganisationId

from [user] where intuserid = @UserID

if @orgID is not null and @orgID > 0

begin

INSERT INTO User_Organisation (UserID, OrganisationID, IsDefaultOrganisation)

VALUES (@UserID, @OrgID, 1)

end

end

set @UserID=@UserID+1

END

-- alternative script

INSERT INTO User_Organisation (UserID, OrganisationID, IsDefaultOrganisation)

SELECT intUserID, UserOrganisationId, 1

FROM [User]

WHERE (UserOrganisationId IS NOT NULL) AND (UserOrganisationId > 0)

sort table randomly

The following sample shows you on how to sort sql table randomly:

select * from(

select top 20 firstname, lastname from [user]

) as result order by NEWID()

identity_insert

The following example shows you on how to insert explicit value into the identity column of a table:

set identity_insert [MyModuleElement] on

Insert into MyModuleElement

(intID, strName, strDescription, intControllerID)

Values

(48, 'test name', 'desc', 46)

set identity_insert [MyModuleElement] off

Disable Table Constraints

The following code will:

• Disable/Enable All Table Constraints
• E.g. if you have an user table and an UserLog table, UserLog have a foreign key which reference the UserID in the user table.
• Now you can disable the foreign key Constraint on the UserLog table and insert a userid which does not exist in the user table into the UserLog table. After that you can enable the constraint.

CREATE PROCEDURE [dbo].[procDisableEnableAllTableConstraints]

@TblName VARCHAR(128),

@IsCheck BIT = 1

AS

DECLARE @SQLState VARCHAR(500)

IF @IsCheck = 0

BEGIN

SET @SQLState = 'ALTER TABLE [' + @TblName + '] NOCHECK CONSTRAINT ALL'

END

ELSE

BEGIN

SET @SQLState = 'ALTER TABLE [' + @TblName + '] CHECK CONSTRAINT ALL'

END

EXEC (@SQLState)

Public Shared Sub DisableEnableAllTableConstraints(ByVal tableName As String, ByVal isCheck As Boolean)

Dim dbCon As New SqlConnection(clsConfig.BaseConnectionString)

dbCon.Open()

Dim cmd As New SqlCommand("[procDisableEnableAllTableConstraints]", dbCon)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@TblName", tableName)

cmd.Parameters.AddWithValue("@IsCheck", IIf(isCheck, 1, 0))

Try

cmd.ExecuteNonQuery()

Catch ex As Exception

Throw (ex)

Finally

dbCon.Close()

End Try

End Sub

Reference:

Stored Procedure to Disable/Reenable All Constraints on a Given Table

SQL server open connections exist in a database

If you get a “timeout connection in the database because there are no connections available in the connection pool” error, the following queries will help you diagnose the problem:

• To allow users to view current activity on the database:
  sp_who2
• To give you the total number of connections per database on a database server:
  SELECT DB_NAME(dbid) as 'Database Name',
  COUNT(dbid) as 'Total Connections'
  FROM master.dbo.sysprocesses WITH (nolock)
  WHERE dbid > 0
  GROUP BY dbid
• To get the dbid from database name
  SELECT DB_ID('MyDBName') as [Database ID]
• To give you the process Ids of existing connections in the database (not necessarily open but existing):
  SELECT spid
  FROM master.dbo.sysprocesses WITH (nolock)
  WHERE dbid = (SELECT DB_ID('MyDBName') as [Database ID])
• To give you information about the actual process id (replace 1018 with the spid):
  dbcc inputbuffer (1018)
• To kill a process
  kill 1018

Thanks Tracey.

Composite Key

• This is a very basic SQL topic but I think it is necessary to clarify the concept.
• A Primary Key uniquely identifies each row in a table, it is not always a single-column key,it could be
• a single-column key
• or a composite key
• A primary key can consist of one or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
• For example, if you have a Student table and a Course table, and one student can select many courses and one course can be selected by many students, so this is a many-to-many relationship. So you need to create the third table to define the relationship, say it's called StudentCourse. It is important to note that you only need the StudentID and CourseID in this table as a composite key. You do not need an extra identity ID column in this table to uniquely identifies each row because only having an ID column to uniquely identifies each row is not sufficient. It cannot prevent the same student selecting the same course from being inserted into this table.

Reference:

Composite Primary Keys

Primary Key

ON DELETE CASCADE

• Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table.
• If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
• If you specify this option, later when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table.
• The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.

Here is a demo on how to do it:

1. Create tables.


2. Add foreign key.






4. Specify ON DELETE CASCADE.



5. Delete a row in the parent table.


6. Child table rows got deleted as well.

If you having a M-to-M relationship, it is the same. Just add the cascade delete on the M-to-M table.

Reference:

Using the ON DELETE CASCADE Option

Transaction Isolation Level

Syntax :

SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
}

1. Read Uncommitted: current transaction can read rows modified by other not yet committed transactions
2. Read Committed: current transaction cannot read rows modified by other not yet committed transactions
3. Repeatable Read: read committed + no other transaction can modify data that has been read by the current transaction, they can just insert
4. Snapshot: current transaction can only recognize committed data modification before the start of the current transaction.
5. Serializable: repeatable read except other transactions cannot insert rows with key values that fall in the range of keys read in the current transaction.