Category: SQL

Simplest way to restore MSSQL Server database

Here’s a simplest way I found (so far) to restore a database in MS SQL Server. First part lists “logicalnames” from the backup that you want to restore. Modify the path so it points to the actual path where your backup file is: RESTORE FILELISTONLY FROM DISK = N’C:\[path]\[db_name_backup].bak’ GO This shows LogicalName and PhysicalName […]

How to delete all stored procedures from MSSQL database using cursor

Here is a code for deleting all stored procedures in SQL Server database using cursor. DECLARE @name varchar(500) DECLARE @sql varchar(max) SET @sql = ” DECLARE cur CURSOR FOR SELECT [name] FROM sys.procedures OPEN cur FETCH NEXT FROM cur INTO @name WHILE @@fetch_status = 0 BEGIN SET @sql = ‘DROP PROC ‘ + @name PRINT […]

SQL Data Types vs. C# Data Types

This article is just a reference of SQL Data Types to C# Data Types. SQL Server data type CLR data type (SQL Server) CLR data type (.NET Framework) varbinary SqlBytes, SqlBinary Byte[] binary SqlBytes, SqlBinary Byte[] varbinary(1), binary(1) SqlBytes, SqlBinary byte, Byte[] image None None varchar None None char None None nvarchar(1), nchar(1) SqlChars, SqlString […]

Clustered vs. Non-clustered indexes in SQL Server

In this article I will make attempt to outline differences between clustered and non-clustered indexes in SQL Server. Be fully aware that it is not going to be comprehensive explanation, but rather brief overview highlighting purpose of both types of indexes. Indexes Indexes are one of the most important part of database optimization, especially for large […]

How to refer to previous row in SQL 2005

In this post I will describe simple mechanism allowing to refer back to previous row in SQL Server 2005/2008 database table.   For the purpose of this article let’s create a table dbo.AgentLog:   create table dbo.AgentLog ( AgentID int ,LogInTime datetime ,LogOutTime datetime ) go insert dbo.AgentLog select 1, ‘2010-09-08 09:40:00.000’, ‘2010-09-08 10:14:00.000’ union all select […]

Next Page »