Adding a primary key can be done either after a table is created, or at the same a table is created. It is important to note, that by default a primary key is clustered. This may or may not be the preferred method of creation. For more information on clustered vs non [...]
Archive of entries posted by Derek D.
SQL Server Select
The SQL Server Select statement is the first statement used when returning data. It is the most used and most important statement in the T-SQL language. The Select statement has many different clauses. We will step through each clause further in the tutorial, however now, we will look at Select itself.
The following [...]
Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF.
This error occurs when trying to insert into a column containing an identity. An Identity column is not able to be inserted into without the use of a special command mentioned below. Identity columns are columns that automatically increment when a value is inserted into a row. They are commonly used [...]
SQL Server Kill
The kill command is used against a SPID. (Server Process ID). This command is typically used because something is misbehaving. In order to use the Kill command, simply type the word “kill” followed by a space and then the number of the Server Process ID to kill.
– Kill Server process ID 98
kill 98
The [...]
Best SQL Server Pagination Method
The best pagination method is actually mind-bogglingly simple. Aren’t all things like this?
It involves using (the mighty powerful) ROWCOUNT statement — twice.
Anyways, the code:
CREATE PROCEDURE [dbo].[spUsersPaged]
(
@RowBegin int,
@MaxRows int
)
AS
BEGIN
DECLARE @StartID int
SET ROWCOUNT @RowBegin
SELECT @StartID = UserID
FROM dbo.Users u
ORDER BY UserID
– Set maximum number of [...]
Using sp_who2
This article shows the usage of sp_who2. To diagnose system slowdowns, see (Troubleshooting SQL Slowness).
One of the first lines of defense in determining the causes of database slowdowns is to use sp_who2. sp_who2 shows all the sessions that are currently established in the database. These are denoted as SPID’s, or Server process Id’s. [...]
Cannot Log in Remotely to SQL Server Express
SQL Server Express 2008 does not allow remote connections by default. This is done by design as SQL Express is often used either as a development tool, or as a local installation for resident applications. Another thing to note, is that SQL Express uses dynamic ports by default so in order to connect, The SQL [...]
SQL Management Tools
Working in Administration or Development, we find ourselves performing the same tasks over and over. Often we do not recognize the time each of these menial tasks take and how much time out of our day they waste. Luckily, there are external tools we can rely on to make our lives easier.
SSMS Tools [...]
Compare Stored Procedure Dataset Output
Comparing the results of two different stored procedures output can be difficult task. The first difficulty is to return the result sets of the stored procedures into temporary tables. There are two different methods of doing this outlined in the link above. For this example we will use the local linked server [...]
Configure Change Data Capture Settings
After setting up change data capture (CDC), there are a few configuration options that allow for optimal performance based upon applications load.
Using the default values, the most number of transactions that can be grabbed every 5 seconds is 5000. Remember, a transaction is not the same as a database row. One transaction can [...]