Hi,Here is an alternative for your solutionSELECT CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND,0,CAST(TMP.TotalTime as DATETIME))),0) as TIME)FROM( SELECT CAST(timeduration AS TIME) TotalTime FROM mytable) TMP

Adding Time in HH:MM Format - Useful SQL Query

by abdul sami

select aday, code, convert(varchar(5), min(atime), (108))MinTime, convert(varchar(5), max(atime),(108)) MaxTime from testgroup by aday,codeorder by aday asc

ADO.Net Connectivity using Stored Procedure and Output parameters

by Ankit Bansal MVP

This articles explains the insertion of data into SQL Database using Stored Procedures with Output Parameters. Some Bootstrapping is also used to create a good looking HTML.

Aggregate Product function extends SQL

by DrABELL

Technique to extend capability of standard SQL by adding the Aggregate Product Function

All the jobs in SQL Server

by seankk

All the jobs in SQL Server

by Wendelius

Or simply:exec msdb..sp_help_jobsp_help_job (Transact-SQL)[^]

Always close your cursor

by Elina Blank

When cursors are inside transaction, it is sometimes easy to forget to close the cursor

Always close your cursor

by Elina Blank

An alternative (simpler way :) ) is:BEGIN TRANDECLARE IdCursor CURSOR FOR SELECT Id FROM MyTable OPEN IdCursor FETCH NEXT FROM IdCursor INTO @CurrentIdWHILE (@@FETCH_STATUS) = 0 BEGIN -- do work IF @@error 0...

Always close your cursor

by Kabwla.Phone

You also might want to look into the "LOCAL" keyword.This limits the scope of the cursor to the context in which it is created.DECLARE IdCursor CURSOR LOCAL FOR SELECT Id FROM MyTableThis is beneficial for many reasons.

Always close your cursor

by Elina Blank

To see the default value of the cursor scope:select * from sys.databases(if value of "is_local_cursor_default" is 0 then scope id global, otherwise it is local).The scope is GLOBAL by default (for compatibility reasons).To change default scope use following:ALTER DATABASE...

Always use Binding Variables in SQL queries

by Wild-Programmer

Boost application speed and performance

Always use Binding Variables in SQL queries

by reshi999

Thanks for that. In MS SQL Server, I use the following technique to avoid full compile:DECLARE @sql VARCHAR(100)DECLARE @pk INTSET @pk = 2SET @sql = 'SELECT id, pcname FROM pod WHERE id = ' + CAST(@pk AS VARCHAR)EXEC (@sql)-- or -- EXEC sp_sqlexec @SQL

Amount to Words

by NightWizzard

Convert amounts to their spoken equivalents

AngularJS & Web API Active Directory Security

by Bert O Neill

AngularJS and Web API Active Directory Security (Authorisation)

Another reason for getting the error “A network-related or instance-specific error occurred while establishing a connection to SQL Server…” in MS...

by Sadeque Sharif

Another way to solve the issue of “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”

Applying Database Principal Throughout the Server (for all Databases) for a Particular User

by Manjuke Fernando

How to give db_datareader access to a specific user across all the databases on a particular SQL Server

Applying Running Total to a Result set

by Chamila Nishantha

This tip describes how to add a running total column to a result set

Applying Running Total to a Result set

by Wendelius

This is an alternative for "Applying Running Total to a Result set"

Assign random value in set to column when updating a table

by Haydn Chapman

A method to update a table column with a random set of specified values

Attunity Connectors to Transfer Data from Oracle to SQL Server using SSIS

by Divyant Kulshrestha

This tip is useful when we have to move data from Oracle database to SQL Server database or vice-versa using SSIS.

Auditing Failed Logins in SQL Server: Looking Beyond Post-event Analysis

by SatyLepide

By analyzing log-in attempts on SQL server, admins can build the complete picture out of the sequence of events that led to compliance failure or unauthorized access.

Auto Increment Column Options in Different Databases

by DiponRoy

How to auto increment column options in databases

AutoCompleteExtender working with EntityFramework! (Reload 2015)

by Eduardo Yost

Creating a Web User Control containing an AutoCompleteExtender for data through WCF (JSON) allowing to store the entity selected through EntityFramework. Entity object serialization from the server to the client and the client to the server.

Automatic Missing Indexes Creation Statements

by GabrieleTronchin

Automatic creation of non clustered indexes using system SQL entities

Autosuggest TextBox from database column in Windows Forms

by Deepak_Sharma_

This article will show how to create an auto-suggest TextBox that will suggest data from a SQL Server database column.

Backing up an SQL Database in C#

by OriginalGriff

One of the things that it seems far to hard to find is a reliable way of backing up a database, and restoring it again later. Particularly for development, where the SQL server instance and the database are likely to be on your development machine, and very likely to be damaged by code.

Backup and Restore SQL Server database

by Ahmed Naqibul Arefin

How to backup and restore SQL Server database schema

Backup and Restore SQL Server database

by vikas_panchal

If you use SQL Server 2005, then do this:Go to your database and right click on the database name.Go to Task and choose Backup and Enter, your backup is created.Then if you want to restore it:Make a new database and right click.Choose Restore database and choose Backfile.Select...

BackUp and Restore with SMO of SQL Server 2008

by Hernán Hegykozi

This tip will show how to perform a backup and restore database from an application in VB.NET 2010.

Basic SQL Auto-Purging for Document Management

by Dan Steuer

This article will help manage automatic database purges according to basic Document Management principles.

Basic Troubleshooting for Microsoft SQL Table Lock

by NaibedyaKar

How to check the query locking the table and to unlock it

Be Very Careful When You Write SQL Trigger

by Mahmud Hasan

Be Very Careful When You Write SQL Trigger

Best practice when getting the structure of the resulting query or table.

by zvin

Use the SET FMTONLY ON and OFF option

Best Practices to tune up T-SQL and SP

by Drew Rhoades

I can't imagine that a correlated subquery is your best answer here:SELECT name FROM teacher WHERE EXISTS (SELECT 1 FROM student WHERE teacher.teacher_id = student.teacher_id)Instead, how about an INNER JOIN?SELECT DISTINCT teacher.nameFROM teacher INNER JOIN student ...

Beware of empty Table-valued Parameters

by Heino Zunzer

The possibility to build stored procedures with table-valued parameters is a great new feature in SQL Server 2008, especially if utilized with the also newly implemented MERGE-command. Alas, there are some restrictions (TVPs have to be bound to a User-defined Table Type, have to be passed as...

Big n Bulky "How To" Document for ASP.NET MVC, Entity Framework, LINQ, HTML 5 and JQuery

by Bhushan Mulmule

This is terribly unorganized document with mix of MVC, Entity Framework, LINQ, HTML 5 and JQuery how tos...

Bitwise OR aggregate

by PIEBALDconsult

Performing a bitwise OR aggregate in SQL Server

Build a Calendar Without Pre-Existing Tables

by #realJSOP

Using common table expressions to build data from thin air.

Building hierarchy using Recursive CTE

by Hiren solanki

Building hirarchy in the sense of hirarchy of manager, employee though both are an employee of an enterprise but both are related or depend on hierarchical level.

Bulk Copy/Insert from a CSV file to SQL Directly

by Bhishma Anil Kajaria

Browse and Copy data from your CSV file to SQL directly without any Windows application...

Busy Production Database Update Without Timeout

by Prasannakumaran Sarasijanayanan

Load data to temporary table, make any additional update if required, create necessary indices, finally DROP live table and RENAME temporary table to production table.

C# / VB.NET / C++ CLI: Create, read and write MS Access (mdb, accdb), MySQL, SQL Server, SQL Server Compact and SQLite databases

by Emiliarge

Both MS Access formats (mdb and new - accdb), MSSQL 2008 R2, MSSQL CE, SQLite, MySQL

Calculate aggregates for dynamic columns using UNPIVOT

by Wendelius

This tip describes how to calculate an aggregate for dynamically defined columns using UNPIVOT clause.

Calculate Geo Distance Using SQL Server

by Nirav Prabtani

How to calculate distance between two geolocations or find out distance within a specific radius by SQL Server

CASCADE in SQL Server with Example

by vinothharshad

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table.

CASE/WHEN return type

by Pranay Rana

CASE/WHEN return type

by Morten Nilsen

Unless you are placing this code in a stored procedure, you could just as well do this:SELECT *FROM ( SELECT 'A1' AS String1,'B1' AS String2, GETDATE() AS DateCol UNION ALL SELECT 'A2','B2', GETDATE()) AS dORDER BY And then append the sanitized column name to sort...

Catch-all clause in combo box filters for SQL Reporting Services

by George Tryfonas

A (hopefully) simple way to implement a wildcard selection in combo box filters for Microsoft SQL Reporting Services

Challenges in Data Migration Testing

by AgarwalPriyanka, ErGauravSharma

Challenges faced in data migration testing

Check Digit Vertical and Horizontal

by Hernán Hegykozi

This tip explains how to calculate the Check Digit Vertical (CDV) and Horizontal (CDH) by SQL Server.

Check if a number is a power of 2 with Sql Server 2012

by Wendelius

This tip shows how to get rows from a table where a field contains a number that is a power of 2 using LOG function.

Clearing Transaction Logs

by Sunasara Imdadhusen

Clear the transaction Log if you are facing a low disk space

Clearing Transaction Logs

by thatraja

By this way tooHow to Shrink SQL Server database log files?[^]

Clearing Transaction Logs

by sx2008

The root cause why the transaction log file grows so big is the wrong recovery model.The recovery model is set to full by default.99 percent of all SQL Servers never make use of this model because there is no backup of the transaction log file.So the great majority of all databases should...

CLR-addon for DATE and Split-String

by Levente Kupás

CLR-addon for T-SQL DATE transformation and Split-String

COALESCE and ISNULL in SQL Server - Part 2

by Uppuluri Aditya

COALESCE and ISNULL in SQL Server - Part 2

Code Generator for SQL Server

by Homero Ibarra Ramírez

Tool on T-SQL to code basic generator for SQL Server

Common Table Expression to find all related nodes

by Jobless Creature

CTE To find all the related nodes in a hierarcy

Concatenate rows with comma separated string

by Ravi Tuvar

Concatenate rows with comma separated string in SQL.

Concatenate many rows into a single text string using SQL Server 2008

by Ramanujam Shankar

Using COALESCE function - concatenate many rows into a single text string output in SQL Server.

Concatenate many rows into a single text string using SQL Server 2008

by e-Lopes

How to concatenate many rows into a single text string using SQL Server 2008.

Concatenate many rows into a single text string using SQL Server 2008

by Sanjay K. Gupta

How to concatenate many rows into a single text string using SQL Server 2008

Concatenate many rows into a single text string using SQL Server 2008

by PIEBALDconsult

See also Stupid CTE tricks -- string concatenation[^]

Concatenating Strings with a Custom SQL Aggregate Function

by Daniel Miller

This tip describes the code to create a custom SQL Server aggregate function that concatenates multiple string values into a single delimited value.

Concept mapping between SQL Server and Oracle

by SergioAmorim

SQL Server and Oracle terms may match, but they can have a very different meaning. This article will help you match one system to the other.

Connect to SQL Server using Windows Authentication

by Masteramuk

JAVA - Connecting to SQL Server database using Windows Authentication or Active Directory

Connecting Windows Form Application With ADO.NET in C#

by Bikash Karmokar

Connecting Windows Form Application With ADO.NET in C#

Convert a CSV delimited string to table column in SQL SERVER

by Kumar Pankaj Verma

How to convert a delimited string in SQL Server to a table column.

Convert column into rows without using pivot

by VINEET MISHRA 086

Convert column into rows without using pivot function in SQL

Convert CSV list into a single column multiple rows

by Sunasara Imdadhusen

Convert RTF to Plain Text (Revised Again)

by NightWizzard

Handling for hex expressions and the trailing '}'

Convert SQL Server Database to PostgreSQL

by RickZeeland

This console application converts an SQL Server database to PostgreSQL.

Converting comma separated data in a column to rows for selection

by OriginalGriff

If you have an SQL column which contains a comma separated list of names, you may have wondered how to return it as individual rows in a SELECT statement. It's not too bad, really!

Cool Insert Generator

by Ashkan.hosseini

Stored procedure that generates insert stored procedure for tables

Copy/Synchronize Table Data between databases

by Anurag Gandhi

A Query to copy an individual table data between databases

Counting All Rows of All Tables in a Database

by Antonio Soares

This tip shows three alternatives (an intuitive and less efficient, a more efficient one and a third more efficient one) to count all rows of all tables in a database.

Create & Populate Time Dimension with 24 Hour+ Values

by Mubin M. Shaikh

Create time dimension with 24 hour plus values and time buckets in your data warehouse

Create a Sample SQL Database in Less Than 2 Minutes

by Pascal Ganaye

This SQL stored procedure creates a database from scratch with some meaningful content.

Create CSV File in Microsoft SQL

by Rajesh_K_Sharma

How to create CSV file in SQL

Create Documentation for SQL Server Tables

by Alexandros Pappas

This tool reads the descriptions of the columns of the SQL server and creates a Word document using SoftArtisans' WordWriter.

Create Insert SQL Statement from the Records in an Existing Table

by markkang

This tip describes how to generate insert SQL statement from the records in an existing table in SQL server database

Create job to export Stored Procedure daily

by Aadhar Joshi

Creating job in sql server which automates taking backup of all stored procedures and functions in physical drive.

Create Linq to SQL connection with select, insert, delete, update

by SuhasHaridas

Create Linq to SQL connection with select, insert, delete, update

Create SQL Job for SSIS Package by using C#

by Sandeep Kumar Tripathi

How to create SQL Server Agent Job to schedule SSIS Package

Creating a SQL Server Database Programmatically

by Roger Wright

A simple example of creating a new database on a SQl Server instance

Creating a SQL Server Database Project Use in Visual Studio 2012 and Use TFS for Version Control

by Diluka Wittahachchige

Creating SQL Server database project use in Visual Studio 2012 and maintain versions use in TFS

Creating SQL Server Login programmatically

by Ramy Mahrous

IntroductionI got this question from Daniweb C# forum, and of course whenever I need to do something against SQL Server object from .NET I go to SMO (SQL Server Management Objects) which provides a great functionalities to do anything with SQL Server instead of going on SSMS and create some scri

Creating SQL Server Login programmatically

by elizas

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. We can integrate SMO into any .NET based applications.SMO is also compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, which makes it easy to manage a...

CRUD in ASP.NET MVC 4 with KnockOut.JS

by Indresh_Prajapati

This tip will help beginners to implement CRUD operations in ASP.NET MVC 4 with scripting language as KnockOut and Database as MS SQL 2008R2.

CRUD in ASP.NET MVC 5 using WebAPI 2 with AngularJS

by Indresh_Prajapati

This tip will help beginners to implement CRUD operations in ASP.NET MVC 5 using WebAPI 2 with scripting language as AngularJS and Database as MS SQL 2008R2.

CURSOR in T-SQL

by Prakash Lekhak

Dynamic SQL to get the list Table names

Daily reminder using SQL Agent and SignalR

by Akmishra0719

Daily reminder using SQL Agent and SignalR

Data binding in WPF DataGrid control using SQL Server database

by Deepak_Sharma_

In this article I will explain how to display data from a SQL Server database table in a WPF DataGrid control

Data Change Tracking Solution for Auditing SQL Server

by Bart Ducal

Universal data change tracking solution for auditing SQL Server databases

Data mining SQL Server Agent

by Gregory Gadow

Search job commands for text, find jobs by schedule and other spiffy things

Data Services - Best practices in Business Intelligence Development: SSIS, T-SQL

by Bertin @nonodata.com

It is often important to have general guidelines when putting together a Data Analysts Team.

Data Type Synonyms

by Sumit Garg From Chandigarh

Introduction...

DataReader into IEnumerable with C#

by Jaume González

How to convert any datareader into generic list.

DBMS Models

by Sonali Agarwal

There are different types of Database Management System models used for organizing the logical data. Relational model is most popular and widely used model in DBMS.

Debugging DataSet Constraint Errors

by cjb110

How to identify the cause of the DataSet Constraint Exception

Default keyword in SQL Server

by Mel Padden

Default keyword in SQL Server

Delete All Rows in a Table in Entity Framework

by DiponRoy

Delete all the records from the table using EF

Delete Duplicate Rows in SQL Server

by Arkadeep De

How to remove duplicate records using CTE in SQL

Delete Similar Databases

by Manas Bhardwaj

I had these similar databases on my dev environment which I had to delete multiple times during development. As this process was becoming more and more mechnical, I wanted to have a script which would do this for me.

Deleting Users With No Database Mappings

by Darek Danielewski

A small script to remove old user accounts

Dequeue workitems using MERGE

by Darek Danielewski

This article demonstrates how to safely dequeue items from a shared table

Designing Business Intelligence Reporting & Dashboards with Tableau Desktop using Microsoft Dynamics

by Venkat Chaitanya Kanumukula

Tableau is one of the powerful reporting tool which is on the market for over a decade. Here's a quick tour in designing BI reports with Tableau using Microsoft Dynamics AX 2012 R3/ Microsoft Dynamics 365 Finance and Operations.

Detecting Concurrency Issues with Entity Framework

by Robert Lindblom

Detecting concurrency issues when using Entity Framework model first and SQL Server

Detects Differences Between Databases

by GabrieleTronchin

Compare two databases to detect which object has been modified from an old version of the same database.

Determine Column Name

by Corporal Agarn

Using imported tables that may have a different column name to determine what it is.

Determine the datatype for excel import

by govardhan4u

Determine the datatype of a column in a Excel file in SSIS

Difference between CROSS APPLY and OUTER APPLY of SQL XML

by jsd24

The difference between CROSS APPLY and OUTER APPLY of SQL XML

Difference between SQL Server Char and Varchar Data Type

by Basavaraj P Biradar

Difference between SQL Server Char and Varchar data type.

Difference Between Two datetime Values in Hours, Minutes, etc.

by Wendelius

This tip shows how to calculate a difference between two datetime values and to express the difference in years, days, hours, minutes and so on.

Display Last Activity Date and Time in Microsoft SQL

by Yugal Pandya

Display last activity Date and Time smartly like Google and Facebook in Microsoft SQL

Does a Database/Table/Column Exist in SQL Server

by ♥…ЯҠ…♥

Query to check Database, Table, Column exists in Sql Server

Drop all the tables in a database

by Gaushick

Drop all the tables in a database(give the database name and table name in the below query).

Drop all Triggers belonging to any schema in MS SQL Server

by Matthew Taylor

How to remove Microsoft SQL Server triggers associated with a custom schema.

Drop any general SQL Server object

by Michiel du Toit

Simple SP to drop any general type of SQL Server object

DROP IF EXISTS in SQL Server 2016

by Jovan Popovic(MSFT)

SQL Server 2016 introduces the new DROP IF EXISTS syntax

DROP IF EXISTS in SQL Server 2016

by Suvendu Shekhar Giri

Enhancement on dropping an object with check for existence in SQL Server 2016

Dynamic ORDER BY with multiple criteria in T-SQL

by Bob Crowley

How to use a CASE statement in ORDER BY to avoid dynamic SQL

Dynamic Pagination

by RahulAJoshi

Dynamic paging in grids

Dynamic Pivot Query in SQL Server

by Chetan Naithani

Transforming dynamic row-level data into columns

Dynamic Query and Incorrect Syntax Near 'GO' Error

by Moheeb U.

Here, I have tried to figure out some basic concept about dynamic query.

Dynamic SQL Connection in ASP.NET 3.5 with User Controls

by ss9o9o9o

Transferring backup files from a remote SQL Server and Dynamic SQL Connection with Restore utility.

Dynamic SQL From DB Table with Parameters

by Johnny L Washington

How to execute store SQL with parameters via Procedure

Dynamically Setting a Parameter from a C# Variable in Crystal Reports

by Kinyanjui Kamau

I have written this tip to help developers automatically set Crystal Report parameters from variables in code (WinForms) at report run time.

Easy Compare DB

by Eligio Morgado H.

Compare two SQL Server databases and show difference in tables and fields

EF6 Migrations and TimeStamp Bug with CodeFirst

by Herman.Instance

Migrations does not apply to T-SQL rules for ALTER TABLE ALTER COLUMN

Effective Paging Using LINQ (Sample Code)

by Sarvesh Kushwaha

Binding Gridview (or any other data control) with effective paging using LINQ

Effectively Generate Dynamic User Control Forms

by Youqi Ma

Using a controller to manage dynamic control form events and creation.

Efficient paging using SQL script

by Robby Tendean

How to create an efficient paging using SQL script

Efficient paging using SQL script

by NeoPunk

Hi, what about such solution?DECLARE @pageNumber INTDECLARE @rowsOnPage INTSET @rowsOnPage=5SET @pageNumber=2DECLARE @totalItemsToSelect INTSET @totalItemsToSelect=@rowsOnPage * @pageNumberSELECT TOP (@rowsOnPage) * FROM MainTrans WHERE ID IN( SELECT TOP...

Efficient paging using SQL script

by jeantoledo

Take a look at this:DECLARE @ActualRow = 1; /*Start in first item*/DECLARE @ItensCount = 100; /*100 itens for page*/DECLARE @Page = 1; /*Start in first page*/SELECT * FROM(SELECT *, ROWNUMBER() OVER(ORDER BY Name) as ROWNUM FROM Users) as UsWHERE ROWNUM BETWEEN...

Efficient paging using SQL script

by kamiktk123

string Query = string.Format(@"SELECT * FROM (SELECT *, " + @"ROW_NUMBER() OVER(ORDER BY PRODUCT_ID DESC ) AS ROW_NUM FROM({0}) " + @"AS TEMP_INNER) AS TEMP_OUTER WHERE ROW_NUM BETWEEN '{1}' AND " + @"({2} + {3}) - 1", strSql, startRowIndex, startRowIndex, maximumRows);

Encrypt all Stored Procedures with PowerShell

by kennysun

encrypt all stored procedures in SQL server with powershell

Encrypting all Stored Procedures in a Database

by jroughgarden

This is an alternative for "Encrypting all Stored Procedures of a Database".

Encrypting all the Stored Procedures of a Database

by qadirv

Quick C# console application to encrypt all your database stored procedures

Ensure Your Code First DB is Always Initialized

by Brady Kelly

A quick and easy way to ensure that your Code First DB Initializer is always run when your app starts, not just on the first data access operation

Entity and Mapping Class Generator for EF

by Daniel Miller

This tip provides a simple solution to automate the generation of entity and mapping classes for Entity Framework.

Entity Framework Not Inserting Rows into SQL Server Database Table

by GuyThiebaut

Solving the "Store update, insert, or delete statement affected an unexpected number of rows (0)" response.

Entity Framework on MySQL with nHydrate

by Christopher R Davis

Use model driven development with a MySql backend

Entity Model to Excel using LINQ in C#

by Debopam Pal

How to bring data from Entity model to Excel file using LINQ.

Error Message: Saving Changes Is Not Permitted

by Michael Haephrati

How to overcome an error message preventing you from saving a change in an SQL Server database

Example of gridview rowcommand on Button Click

by Nandakishore G N

Example For Gridview RowCommand Event

Execute SQL Query / SQL Command on SQL Server Using the Command Prompt

by IkhwanKrisnadi

How to execute SQL Query / SQL Command on SQL Server using command prompt.

Execute SQL Query / SQL Command on SQL Server Using the Command Prompt

by manish76235

SQLCMD can be used to login to the Sql Server as well. One can only provide Server address, User Name and Password at the command prompt and press enter.The command prompt will change to 1>. Now any sql query can typed and by pressing enter in the next prompt a GO command and pressing enter...

Execute SSIS Packge by using C# and SQL Server

by Sandeep Kumar Tripathi

Here are the different ways to execute an SSIS Package by using C#.

Export data from SQL Server as XML

by seankk

Export data from SQL Server to MS Access

by Muhammad Shahid Farooq

The purpose of this article is to provide a fast but easy way to export data from SQL Server to MS Access.

Extending DATEADD Function to Skip Weekend Days

by Emiliano Musso

A quick method to add days to a date, skipping weekend days if the number of days added results in a Saturday/Sunday

Fast and Simple IDataReader Implementation to Read CSV Files

by shijo joseph

A simple and fast CSV reader which you can directly use against Oracle and SQL Server databases or for any direct implementation

Fast way to deal with foreign keys

by Alexander Konstantinov

Two stored procedures helping developers do less things when they create tables and indeces

Federate Your Data for Internal and Limited External Consumption

by B. Clay Shannon

Create peace and unity in your organization by separating data and coding responsibilites

Fibonacci sequence using SQL Server CTE

by Wendelius

Generate Fibonacci numbers using CTE

Find all SQL Server DateTime Formats

by R. Giskard Reventlov

When using DateTime with SQL Server, I often forget what the parameters are that allow formatting DateTime in specific ways. When that happens, I fire up this code fragment in SQL Server Management Studio and it neatly lists out every possible parameter with example output.declare @Loop...

Find all Stored Procedures having a given text in it

by Basavaraj P Biradar

How to find all the Stored Procedures having a given text in it.

Find all Stored Procedures having a given text in it

by BlackburnKL

We use a slightly modified approach that returns results from more than stored procedures (although it could be modified to return only results for stored procedures). We use this code in a stored procedure that requires a parameter containing the text to search for.@StringToFind...

Find all Stored Procedures having a given text in it

by Jaume Pascual

SELECT DISTINCT so.name FROM dbo.sysobjects so inner join .dbo.syscomments sc on so.id=sc.id WHERE so.xtype='P' AND sc.text like '%SearchString%'Much faster!

Find All the Days Between Two Dates

by S.P.Tiwari

Find all the days between two dates using SQL Server

Find Duplicate Files in FILETABLE

by Sarathi Balakrishnan

Remove duplicate files with same file content, even with different file name using FILETABLE with simple CTE.

Find duplicate values in SQL Server

by bbirajdar

How to find duplicate values in SQL Server.

Find Missing Date Ranges in SQL Server

by Ramanujam Shankar

Find missing date periods from a table.

Find paricular word or text in all database objects on the server

by 1Lam

Create the following table on a local database:CREATE TABLE [dbo].[TB_TEMP_TABLESTORE]( [SEARCH_STRING] [nvarchar](128) NULL, [DB_NAME] [nvarchar](128) NULL, [OBJECT_NAME] [nvarchar](128) NULL, [OBJECT_TYPE] [nvarchar](50) NULL) ON [PRIMARY]Rename Yourdatabase to the same...

Find particular word or text from the entire stored procedure

by Sunasara Imdadhusen

Find particular word or text from the entire stored procedure

by NGO Quang Minh

You can create a simple stored procedure like that:DECLARE @StringToSearch varchar(100)SET @StringToSearch = 'GetEmployee'SET @StringToSearch = '%' +@StringToSearch + '%'SELECT Distinct SO.NameFROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND...

Find particular word or text from the entire stored procedure

by tarun_j200

SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE definition LIKE '%GetEmployee%'

Find particular word or text from the entire stored procedure

by CaldasGSM

since syscomments is nvarchar(4000) the word may be split into several rows.. so you concat them.. or at least pair them up... here is the sp I use to search on the stored proceduresCREATE PROCEDURE [dbo].[sp_FindInProc] @sText NVARCHAR(MAX) = '%'ASBEGIN SET NOCOUNT ON ...

Find particular word or text from the entire stored procedure

by ednrg

Not necessarily an alternative, but a wrapper. I created it as a quick stored proc, and put it in the master db, so it can be used in any database.Usage: exec fi 'last_name'.USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[fi](...

Find Sp from database which is related to(using) table XXX

by Pranay Rana

Problem : - During project development I got the requirement that I have to modified the column name of the table which is used in the no of procedure i.e i have to found all procedure related to that table and modify it For the above problem following are the solution that i used First ...

Find text in Stored Procedures, View, Trigger, and Function

by Nelson Kosta Souto

Find text in Stored Procedures, View, Trigger, and Function.

Find Which Column in Which Table Updated from logfile

by Mehdy Moini

How to know which table is updated in the current logfile time.

Finding available free space in the disks of SQL Server

by Saral S Stalin

Simple query to find the available hard disk space in the server

Finding numbers from a text in SQL Server.

by Saral S Stalin

The below function will find numbers from a given text in table format.

Finding numbers from a text in SQL Server.

by André Kraak

This is an alternative for "Finding numbers from a text in SQL Server."

First and Last Occurrence of Day of Week Within a Month using SQL

by Wendelius

This tip describes how to fetch the first or the last occurrence of given day of week within a month by using a single SQL statement.

Fix SQL Server DTC Errors on Duplicated Hyper-V VMs

by Zhuyun Dai

Fix multiple SQL Server DTC errors when we use distributed transactions between VMs duplicated from the same template on Hyper-V

Forgot Password With Check Email From Database

by Muhammad Aqib Shehzad

Forgot Password and Email Check from Database

Formatting Local Datetime to UTC in SQL Server

by Ani_0707

This article intends to describe a simple and elegant way of converting local datetime to UTC and store it in DB.

Functionality as same Val() function in Access in Sql Server

by Vivek.Net

Generate a Table Variable from Query Metadata

by Michael Selector

A code to declare a table variable which will be populated from a query

Generate auto row number for a table

by Md. Marufuzzaman

Generate auto row number for a table

by Mukund Thakker

SELECT ROW_NUMBER() OVER(ORDER BY [col_1]) as RowNumber,COUNT([col_1]) OVER () AS RecordCount,* FROM TableName

Generate auto row number for a table

by YSLGuru

If you have the ability to change it, you may want to consider moving away from the 36 digit alphanumeric created by GUID() as a Key. This is often misused in SQL-Server by developers who have not had proper training/education in SQL Server and or RDBMS. The GUID() is not intended to be a...

Generate Calender Recurring Dates Automatically with SQL Job

by Pavan Thakur

I had a requirement to create automatic date entries into Scheduler SQL table. Whenever a Schedule with RecurrenceRange was set with 'No EndDate', the dates will automatically get added from backend for next year through a job.

Generate Data Dictionary from SQL Server

by Johirul Islam Tarun

Easily find table description.

Generate Insert script to extract data

by Rishikesh_Singh

Generate Insert script to extract data

Generate Script with Data from Database

by Sunasara Imdadhusen

SQL Server 2005 : Generate Script with Data from Database – Database Publishing Wizard

Generate Script with Data from Database

by tarun_j200

SSMS also provides the option to generate SQL script file which contains both schema and data.Right click the database and select Generate Scripts under Tasks Menu.Click Set Scripting Options on the left hand menu (in SQL Server 2008 R2) then click Advanced button, there is an option under...

Generate XML in ASP.NET MVC5 and AngularJs using Database Value

by Md._Mehedi_Hasan

This tip will help you to generate .xml file in AngularJs and ASP.NET MVC5 using XElement of C#.

Generate XML Using FOR XML

by Harsh Gandhi

Generate XML Using FOR XML ; Difference between different modes (AUTO, RAW, PATH, EXPLICIT) of FOR XML to generate XML in SQL Server

Generating a set of random numbers in SQL Server

by Wendelius

Generate a set of random numbers by using CTE

Generating and Splitting a Delimited String Column

by Tharaka MTR

This article describes the easiest way to create and process delimited row using SQL XQuery.

Generating Data Dictionary for SQL Server with CatFactory

by HHerzl

HowTo for generating a data dictionary from existing database

Generating Desired Amount of Rows in SQL using CTE

by Wendelius

How to generate desired amount of rows in SQL Server using CTE

Generating HTML Reports for Dynamic Table Structures

by Sergii Syrovatchenko

This tip describes the most convenient way of process automation of generating HTML reports for dynamic table-structures and sending them to different people.

Generation of Dynamic Parameterised Report with DropDown List using SSRS

by Purushotham Agaraharam

Creating a parameterised report using SSRS.

GeoJSon to C# Object using Json.NET

by Shamas Imran, Muhammad N@beel

Convert Coordinate data to strings to be stored in database.

Get accurate age by date

by Md. Marufuzzaman

Get accurate age by date using MS SQL Server

Get Better Performance in Dotnetnuke Framework

by Trung Nguyen Son

How to analyse and tune your dotnetnuke site to get best performance.

Get days of a month in SQL

by rajivlipu

select days_of_month=day(dateadd(mm,-1,'12-feb-2010'),-1) :laugh:

Get days of a month in SQL

by g_p_l

@dt is a datetime valuederive the first of this month, add a month to itthen subtract 1 day to get the last day of this monthselect days_of_month=day( dateadd(d,-1, dateadd(m,1, dateadd(d,1-day(@dt), @dt) ) ) )

Get my database server with current database.

by Md. Marufuzzaman

This is a very simple use of SQL Server global variables. Microsoft SQL Server provides a massive number of global variables, which are very effective to use in our regular Transact-SQL. Global variables represent a special type of variable. The server always maintain the values of these...

Get SQL Server Database Connection String Easily from Visual Studio

by Srinubabu Ravilla

How to get SQL Server database connection string easily from Visual Studio

Get SQL Server Table Information

by Dave Elliott

Global Stored Procedure to retrieve tabular information or a CSV list of columns for a database table.

Get Table Column Value as Comma Separated Value (CSV) in SQL

by Arslan Elahi

Get the Comma Separated Value of one or multiple columns from SQL table

Get Tables List With Its Dependent Tables Names

by Dhol Gaurav

Get Tables List With Its Dependent Tables Names

Get time difference in the format "HH:MM:SS" for two different DateTime values in SQL Server

by shivaramakrishnaReddy

Get time difference in the format "HH:MM:SS" for two different DateTime values in SQL Server.

Get Top X rows from a table

by Mahmud Hasan

How to use variable in SELECT TOP SQL command

Getting a list of SQL Server modules to refresh

by PIEBALDconsult

Using a Common Table Expression to produce a list of Views, Procedures, and Functions

Getting a List of SQL Server Modules to Refresh

by PIEBALDconsult

How to get a list of SQL Server modules to refresh

Getting all table and schema names containing particular column name

by Mahmudul Haque Azad

Getting all table and schema names containing particular column name

Getting the Current Time from Any Time Zone

by vikram.mahapatra

How to get the current time from any time zone

GO Statement can also be used to Execute Batch of T-SQL Statement Multiple Times

by Basavaraj P Biradar

GO Statement can also be used to execute batch of T-SQL statement multiple times

Good Universal Paramerter and Stored Procedure Execution

by Robert Bettinelli

Check this for a good universal way of running stored procedures..

Grouping dynamically on different columns in single query

by RahulAJoshi

Grouping dynamically on different columns in single query

Grouping Records with SQL Server 2008

by Omar Frometa

Grouping Sets is the new SQL Server 2008 Operator

Hide Hierarchy levels under Granularity level in Role-Played Dimension

by db_developer

There is no way to show hierarchy level in one dimension of one cube and to hide that hierarchy level in the same dimension (role-played dimension) of the same or other cube. Solution is to add additional hierarchy without levels to hide to this dimension.For example,There is dimension...

Holidays Table and GetWorkDays Function in SQL Server

by Igor Krupitsky

Fast function that excludes weekends and holidays

How to add an identity column to a table with data?

by Sudheer Reddy Battula

Add an identity column to a table with data

How to Calculate the Check Digit on a Columbia Gas Account Number

by cattman1970

Function that consumes a Columbia Gas account number of 11+ digits and returns the check digit

How to Compare Database Schema in VS 2012, How to Update the Target Database to the Server

by Syed Moula Ali

How to compare database schema in VS 2012 and how to update the target database to the server

How to connect to Analysis Services (SSAS) from a different domain user

by kasiarun

How to connect to Analysis Services (SSAS) from a different domain user

How to convert nvarchar, money and smallmoney values to int

by RaviRanjanKr

This tip enables you to convert nvarchar, money and smallmoney values to int.

How to convert UTC time to Local Time

by Panayotis Matsinopoulos

Store your date time values in UTC format and convert it to local time when you present it to the user.

How to convert UTC time to Local Time

by Tom Clement

I wanted to point out that while I agree with the general approach (storing DateTime in UTC and converting into local time for display as well as accepting input in local time), it's not always what the requirements call for. If you're creating a meeting calendar, then of course it's perfect,...

How to convert UTC time to Local Time

by Philippe Mori

In my opinion, Local to UTC time conversion should be done nearer to UI level. That is, you should pass UTC time to your query and the time should be converted later when it is displayed to the user.Doing the conversion in code will simplify SQL queries and thus probably improve overall...

How to Copy a SQL Server Table?

by Sudheer Reddy Battula

Copy a SQL Server table

How to Count All Tables, Rows, and Rows Per Table

by Daniel Miller

This code snippet shows how to count all the tables and rows in a SQL Server database

How to Create and Use a Self-referencing Hierarchical Table

by E. Scott McFadden

This article explains how to create and use a self referencing key in a SQL Server Table.

How to create Database User programmatically?

by Ramy Mahrous

Well... we need to assign a server login to has access on a database, let's say the SQL Server Login we had created programmatically should has access on database "Database1" and for some reasons we should do that programmatically Well. Create any C# project type (Windows, Class Library or even Cons

How to empty SQL Server 2005 database using cursor & sys.objects (Deleting All Tables, stored procedures, views & UDF’s)

by kiran dangar

Understrading sys.objectsSys.objects is a system VIEW in SQL Server 2005, for each SQL database there is a separate sys.object view which gets stored within databse itself.Using Sys.objects returns list of all database objects and its types, type can be either of given below:DB OBJECT...

How to empty SQL Server 2005 database using cursor & sys.objects (Deleting All Tables, stored procedures, views & UDF’s)

by VDmitrovsky

1) You cann't drop any object in schemas other than dbo, so you MUST use sys.schemas system view properly.2) You cann't drop tables even in dbo schema if they are referenced by other ones, not dropped so far. Drop all FKs before tables.dva

How to empty SQL Server 2005 database using cursor & sys.objects (Deleting All Tables, stored procedures, views & UDF’s)

by kiran dangar

For Ravi LVS,The way you have suggested to Empty the database by just deleting and recreating it, is not at all smarter way when you are about to empty a remote database located on live database server, There are times when you don't hold such rights of deleting database on Live...

How to enum SQL Server instances in network

by Maciej Los

This tip is desired for those who want to list all available SQl Server instances that are in a network.

How to Establish Dedicated Admin Connection (DAC) to SQL Server

by Wendelius

Few tips for connecting to an SQL Server instance using DAC

How to execute multiple database script files from a specific directory

by S. M. Ahasan Habib

I will explain very simple way to execute multiple script files (*.sql) from a directory.

How to fetch data from the previous or next rows in the resultset

by Wendelius

This tip demonstrates the use of LAG and LEAD functions in SQL Server.

How to Find a Text in SQL Server Stored Procedure

by Ameet Parse

This article describes How to find a Text in Stored Procedure (Checked on SQL Server 2008)

How to Find a Text in SQL Server Stored Procedure

by Ameet Parse

This is an alternative for "Find all Stored Procedures having a given text in it"

How to find column or text in entire stored procedure of a database?

by Rajat-Indiandotnet

Find column or Text in entire stored procedure

How to fix "LocalDB - Requested Login failed"

by Ed Nutting

How to fix LocalDB / SQL Server: "Cannot open database "XYZ" requested by the login. The login failed. Login failed for user ABC"

How to Format Repeating Values in SQL Result Set

by Wendelius

A little tip on how to format repeating values in SQL output using LAG function

How to Generate T-SQL Code by Using Dynamic SQL?

by Sergii Syrovatchenko

T-SQL code generation by using Dynamic SQL for routine tasks automation.

How to get an empty GUID in SQL Server

by nit_singh

A method of comparing with an empty GUID

How to get the details of Open Transaction in SQL Server

by tarun_j200

How to Guarantee a Fixed Amount of Rows in a Table

by Wendelius

This tip describes how to guarantee a fixed amount of rows in a table by using triggers

How to implement ASP.NET Membership into a new database

by ChienVH

This article will show you how to implement Asp.Net membership into a new database

How to Insert HTML Table Data into SQL Server in a Single Batch by Using JQuery Ajax and Structured Parameter

by Amit Singh Baghel

Saving Details Table Data (An HTML Table) in Database by converting it into a jagged array and passing it to a table type parameter

How to Modify Views, Triggers and Procedures from Code

by NightWizzard

Read and/or modify database objects like views, triggers, stored procedures and functions from .NET code.

How to Partition Table in SQL Server

by Kale Yogesh

How to Partition Table in SQL

How to place an even-numbered SSRS report parameter at the start of a new row

by Karsten Krug

A workaround for reporting services' missing ability to customize report parameter placement.

How to prevent SQL Injection in Stored Procedures

by Shao Voon Wong

How to prevent SQL injection attacks when using dynamic SQL in stored procedures

How to Protect from SQL Injection in PhP based website

by chetan dudhagara

How to Protect from SQL Injection in PhP based website

How to remove duplicate rows in SQL Server 2005 when no key is present

by Wendelius

This tip describes how to delete duplicate rows from a table that doesn't have a key.

How to remove duplicate rows in SQL Server 2005 when no key is present

by Costica U

Here is another solution:IF OBJECT_ID( 'tempdb..#TestTable' ) IS NOT NULL BEGIN DROP TABLE #TestTable ENDCREATE TABLE #TestTable ( Column1 varchar(1), Column2 int )INSERT INTO #TestTable VALUES ('A', 1);INSERT INTO #TestTable VALUES ('A', 1); --...

How to remove duplicate rows in SQL Server 2008 when no key is present

by Wendelius

This tip describes how to delete duplicate rows from a table that doesn't have a key.

How to remove duplicate rows in SQL Server 2008 when no key is present

by Ron sikander

Find out the number of duplicates in the table:select count(*), VISTX_ST_FIPS,VISTX_CTY_FIPS,VISTX_TC from TR00.NV_STARTER_INDEXgroup by VISTX_ST_FIPS,VISTX_CTY_FIPS,VISTX_TC having count(*)>1--- Delele them using following statementDELETEFROM TR00.NV_STARTER_INDEXWHERE...

How to RESET identity columns in SQL Server

by Tejas Vaishnav

Resetting an identity column in SQL Server

How to Resolve: "The Data Source value in the connection string specifies an instance of SQL Server that is not installed" or "LocalDB instance...

by yash soman

The error occurs when we try to create a new Microsoft SQL Server Database File (SqlClient) using Visual Studio IDE

How to Retrieve Dynamic XML Data from T-SQL

by Bhushan W. Juare

The different ways of retrieving XML data from SQL Server.

How to Save Excel Files to SQL Server and Make them downloadable from an ASP.NET Web API Project

by B. Clay Shannon

Save Excel (or other types of) files to SQL Server, then read them out again and make them downloadable via a link on an ASP.NET Web API page

How to Search a Column Name Within All Tables of a Database and How to Search Stored Procedures Containing a Particular Text

by Sandesh M Patil

How to search a column name within all tables of a database and how to search stored procedures containing a particular text

How to search between two dates and get all records

by SA - Hibbert

Return all records between two dates that include time in SQL.

How To Select Record Based on Row Number

by Abhishek Sur

It is a common issue of many of the guys on how we can fetch a record based on its record number. It is really easy to get first 10 records usingSELECT TOP 10 * FROM EMPBut when we want to fetch records in such a way say I need to fetch record from 40 to 50. This comes very handy when...

How to Set Default Value or Binding for BLOB DataTypes on SQL Server

by Wonde Tadesse

This tip/trick helps to set a default value for blob data type such as Image, VarBinary.

How to setup database versioning tool RoundhousE

by Rohan Kishor Garud

RoundhousE (RH) is a database migrations engine that uses plain old SQL Scripts to transition a database from one version to another.

How to Split Microsoft SQL Server Table Row Data

by Md. Marufuzzaman

This tip shows you an easy way to split Microsoft SQL Server table row data.

How to Take Backup from SQL Azure using Database Deployment Technique

by P_Dash

Taking a SQL Azure database backup to Local instance from an SQL Azure instance using Deployment Technique

How to trace SQL Server Express without using SQL Profiler

by BillyGoatGruff

SQL Profiler is not included with SQL Express, this tip will help if you need to track client activity.

How to truncate log file in SQL Server 2008

by Kumar Pankaj Verma

Truncating log file in SQL Server using T-SQL when log file is too large and database becomes unresponsive.

How to update a row with another row if column values are null in SQL

by ManojKumar19

Update row column with another row column in same table SQL only if it is null.

How to Use a Stored Procedure Inside an SQL Query

by OriginalGriff

How do you call a Stored Procedure inside a query and use the SELECT results? Tricky...or is it?

How to Use Office365 Email with SQL Server 2000

by MAW74656

This is a drop-in solution that mimicks the functionality of xp_sendmail (including query results), without installing Outlook on the server.

How to Use SFTP within SSIS

by mgoad99

Using winSCP to download a file through SFTP within an SSIS package

How To Use SQLCMD Utility in SQL Server 2008?

by Bhushan W. Juare

This tip shows how to use SQLCMD utility in SQL Server 2008.

How to Write a Single Stored Procedure for Delete/Retrieve all Table of Records

by Bojjaiah

Single Stored Procedure for delete/retrieve all table of records

I don’t know why my boss always gets angry

by Md. Marufuzzaman

I don’t know why my boss always gets angry when I alter Microsoft SQL Server database objects such as storedProcedure, functions, views, etc.

I don’t know why my boss always gets angry

by jwood_denver

Why don't you just use a query like this?SELECT CAST(serverproperty(N'servername') AS varchar(50)) AS [Server Name], db_name() AS [Database Name], SCHEMA_NAME(schema_id) AS [Schema Name], [name] AS [Object Name], [type_desc] AS [Object Type], [create_date] AS [Create...

Identifying and Deleting Duplicate Records from SQL Server Table

by Aqeeel

Identifying and deleting duplicate records from SQL Server Table

Import a MS SQL Database to Amazon RDS

by Uladzislau Baryshchyk

In this article, I'll show you how easy it is to move your MS SQL database to Amazon RDS cloud

Import CSV or txt File Into SQL Server Using Bulk Insert.

by Arvind Singh Baghel

Import CSV or txt File Into SQL Server Using Bulk Insert.

Import or Restore BACPAC File using SQL Server Management Studio

by Satya Karki

How to import or restore BACPAC file using SSMS

Improving Backup Performance

by Satnam Singh CapGemini

Improving backup performance

Index and Sort Order

by Mr. Joydeep Das

What the effects of Ordering in Index

Inno Setup Script to Install SQL Server 2008 R2 with Tools

by RickZeeland

Inno Setup script to install SQL Server 2008 R2 with Tools, creating an instance with special rights and displaying the log.

Insert default value[s] in a table

by Mehul M Thakkar

I was facing the problem of inserting a default value on single column table and I have achieved via this way.insert into > default valuesAlso this could be used for many columns in a table defining with default constraints.ThanksMehul Thakkar

INSERT with SELECT vs INSERT with VALUES

by mravikiran

INSERT with SELECT vs INSERT with VALUES

by Andrew Rissing

You might want to do some further testing... It seems if you perform the following, the values are actually the same:SET NOCOUNT ONDECLARE @TMP TABLE( EmpName VARCHAR(50), Age INT)PRINT CONVERT(VARCHAR, GETDATE(), 121)INSERT INTO @TMP(EmpName, Age)SELECT 'Ravi Kiran',...

Insert/update/delete records in a single statement

by Praveen Meghwal

How we can write a single statement to perform insert, update and delete operation

Install SQL Server 15.x on Ubuntu Linux Server 18.04 LTS

by Nabil Droussi

This article will walk you with steps on how to install, configure and connect to SQL Server 15.x on,Linux Ubuntu Server 18.04 LTS

Install SQL Server 2005 and 2008 on the same computer

by Ravi Tuvar

Installing SQL Server 2005 and 2008 in the same computer.

Installing a CLR Stored Procedure on SQL Server

by Sarkis Matossian

The steps to install a CLR Stored Procedure in SQL Server

Instead of Triggers in Object-Relational (O/R) Mapping

by h_wiedey

Usage of instead of triggers in Object-Relational (O/R) Mapping

Interview Question: What are the Differences between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT()

by Sandeep Singh Shekhawat

How to get the last inserted identity value of an auto increment column of database tables using SQL Server variable (@@IDENITY) and functions (SCOPE_IDENTITY() and IDENT_CURRENT())

Introduction to Named Calculations in SQL Server Analysis Services

by Mubin M. Shaikh

Create Virtual Column Using Named Calculation in SQL Server Analysis Services

Introduction to Named Query in Sql Server Analysis Services

by Mubin M. Shaikh

Create Named Query in Data Source View While Designing OLAP Cube in SSAS

Is MS SQL Server Service Broker Responding to Read Uncommitted Data?

by Herman.Instance

When using SqlDependency, is it possible that a detected change leads to 0 rows in the SqlDependency results?

Issues after Upgrade to SQL Server 2017 – Cannot Bulk Load Data Into the Table with Primary Key Constraint on it

by Satnam Singh CapGemini

Cannot Bulk Load Data Into the Table with Primary Key Constraint on it

Keeping a Database Server Healthy

by wizardzz

I would definitely include some security measures in here as they can compromise the data being stored. Though the system may remain up after a breach, it will be too late to prevent damage once it's done:16. By all means necessary, take measures against SQL injections. This is probably a...

Keeping a Database Server Healthy

by __Untitled

Actually, we think xp_cmdshell is not safe enough, and need to be enabled using surface area configuration explicitly (SQL 2005). We use xp_delete_file instead, sample code:DECLARE @l_Runtime DATETIME, @l_BackupFileName VARCHAR(256), @l_FileExists INTSET...

Know More about SQL Constrains

by Mohammed Nazer

SQL Constrains details and its types

Leveraging SQL Server - Custom Rule Engine

by Pareek Ankit

Leveraging SQL-Server - Custom rule engine

List All Tables and Columns in a Database

by AspDotNetDev

This query will show you all tables and columns in a database, and it can be pasted into Excel for easy manipulation.

List blocking processes in SQL server

by Chris Maunder

A quick script to enable you to find the processes that are blocking in SQL server

List blocking processes in SQL server

by Chris Maunder

Here's an alternate version that doesn't use sp_who yet provides a little more information. It also provides the option to kill the blocking processes themself.IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ListBlocking]') AND type in (N'P', N'PC'))EXEC...

List Primary Key and Foreign Key Relationship in Database - SQL Server

by Hussain Patel

Display the list of all Tables, the Referenced columns, the Referencing Table, the Referencing columns and the Constraint name in a database

List SQL Server table column name as rows

by Chamila Nishantha

Simple query to get table column name as rows

List SQL Server table column name as rows

by PIEBALDconsult

This is an alternative for "List SQL Server table column name as rows"

List SQL Server Table Column Name as Rows

by Wendelius

This is an alternative for "List SQL Server table column name as rows".

Load GridView from Excel

by Manikandan Sekar

Loading Data from Excel

Localization with Entity Framework

by Federico Colombo

How to replace textual properties of EF Objects with translations

Loop through all tables in SQL Server

by pankajupadhyay29

Use this when you want to perform a task on all tables in a database

LPAD and RPAD functions in SQL

by Member 10510822

LPAD and RPAD function works for padding in SQL Server

LPAD and RPAD Functions in SQL

by PIEBALDconsult

This is an alternative for "LPAD and RPAD functions in SQL"

Merge in SQL Server 2008

by PratapReddyP

Merge in SQL Server 2008.

Merlin Framework 2013

by Marc Leger

ORM, databinding, asynchronous data access, and transactions

Microsoft ASP.NET Identity MerlinFramework 2.0

by Marc Leger

Microsoft ASP.NET Identity MerlinFramework

Microsoft SQL Intellisense Not Seeing My Table

by DataBytzAI

How to refresh intellisense after adding new objects in Microsoft SQL Management Studio

Microsoft SQL System View Version Map

by Paw Jershauge

MS SQL system views, mapped over version.

Migrate reporting services Database to Another Server

by Atul_Kapoor

SQL Server Reporting database migration from SQL Server 2005 to SQL Server 2008 R2

Migrating Access Jet Data to SQL Server

by Mike Winiberg

GOTCHAs and tips useful when migrating a complex Access JET application to SQL Server 2012

Monitor Database Changes

by Member 7874525

This how you can monitor your database changes

Monitoring Real-time Database Changes in SQL Server in C#

by Telegram:@ArastoAhmadi

Tutorial to monitor database changes without using tools and by using a simpler trick

More SQL XML, This Time Just Using SQL

by MarcusCole6833

Make some SQL XML dot style another way

Move Database File from One Drive to Another Drive

by User-12551084

Moving database file from one drive to another drive

MS SQL SERVER APPLY AND JOIN Keyword

by Dinesh K.S. Kushwaha

Tip on CROSS APPLY and OUTER APPLY

Multiple SQL INSERT Operations with a Single Command

by OriginalGriff

Inserting a number of rows in a database isn't difficult, but how do you do it in a single command without concatenating strings?

MyMDb your local IMDB store

by Paw Jershauge

Want your own movie database??? Download IMDB in 20 minutes.

Name resolving problem with DTC (Distributed Transaction Coordinator)

by Oscar Arros González

A solution for the name resolving problem with DTC.

New High-Availability/Disaster-Recovery solution provided by SQL Server 2012

by yuanfeng.ma

New high-availability/disaster-recovery solution provided by SQL Server 2012.

Node.js Connect to Local SQL (Trusted User)

by A.Ebrahimi

Connect to local database on MS SQL Server in local machine and develop node.js code

nth Highest Salary in SQL Server 2005

by Mohd_Ahmad

nth Highest Salary in SQL Server 2005

by thatraja

SELECT TOP 1 salaryFROM (SELECT DISTINCT TOP n salaryFROM employeeORDER BY salary DESC) aORDER BY salarywhere n > 1SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value[^]

nth Highest Salary in SQL Server 2005

by Costica U

The original solution has some issues. Check the following script:IF OBJECT_ID('tempdb..#salary') IS NOT NULL BEGIN DROP TABLE #salary ENDCREATE TABLE #salary ( Salary INT )INSERT INTO #salary(Salary) VALUES(5) -- 1INSERT INTO #salary(Salary)...

Number of different ways to get total number of rows from tables

by Pranay Rana

Getting a SQL Server RowCount Without doing a Table Scan

Number of occurences of any record in a column

by Durgesh Swarnkar

Getting the count of records in a particular column.

OLAP CUBE Optimization Technique and a Small Trade off

by SERokon

OLAP cube performance optimization technique

Order by Clause with Column_Index instead of Column Name

by RaviRanjanKr

A tip enable you to learn how to use Column_Index instead of Column Name with Order by clause

Pagination with DataList

by John Bhatt

The most versatile control of ASP.NET, DataList, lacks one thing Pagination, built in.

Passing a datatable to a Stored Procedure in SQL Server 2008

by zvin

Passing a datatable to a Stored Procedure in SQL Server 2008 which helps improve performance.

Passing DbNull.Value to an SQL Image column

by OriginalGriff

If you try to pass a DbNull.Value to an SQL Image column, you get an exception: "Operand type clash: nvarchar is incompatible with image" - not sure why it's throwing that, but it can be done.

Passing NULL and table values to a parametrized SQL query

by Darek Danielewski

The importance of using parameterized SQL queries has been well established, but passing table values or occassional NULL values has always been a problem ... until now ...

Passing Object Collections to ASP.NET Web API

by Tom Glick Philadelphia, US

POC to demonstrate passing of collections of complex objects to ASP.NET Web API Controllers.

Paste Table Data into an Editor Then Quickly Format for Good Appearance

by RedDk

Use ALT+SHIFT to "block" select text (thinking of justifying columnar relations of words/data/etc. so the visual appearance is perfectly tabular) ... and TAB (TAB+SHIFT to undo) to MOVE SELECTION right (and left) ...

Pay Attention to What "Script...To" Generates

by #realJSOP

It doesn't always happen the way you expect.

PHP and Accessing MS SQL Server in Unix/Linux

by C Is Sharp

PHP and Accessing MS SQL Server in Unix/Linux.

PHP Sample of Database Connection Configuration File having Both Connection String for MySQL and MSSQL

by Micah Ebohon

Having MSQL and ODBC connection string in one configuration file for your application

Pivoting on SQL Server Varchar Data Types

by Sifiso W Ndlovu

This tip illustrates a workaround to SQL Server pivoting on variable character data types.

Plug-in Architecture To Work for Multiple Platforms/Techs

by Reader Man San

Plug-in Architecture To Work in WinForms MVC + WPF MVVM + ASP.NET MVC + Mobile (Xamarin MVC/PhoneGap MVC) + Win 10 Universal App MVC

Preparing “Data Dictionary” using SQL Query

by Chirag Vidani

Prevent Brute Force Attacks on SQL Server

by Trond Lind

Prevent brute-force login attacks on a remotely accessible SQL Server database using T-SQL

Preventing SQL Injection Attacks

by Nagaraj Muthuchamy

This article gives an introduction on SQL Injection attacks and tips to prevent it.

Publish RDL Files To SSRS using C#

by Sameh Abdel Mongy

Deploy Reports rdl files to SSRS using C# script

Query Excel or CSV files with T-SQL

by Federico Di Marco

Query Excel or CSV files with T-SQL without importing them in a table first

Query for running total in SQL Server

by Mukit, Ataul

Calculate running total or cumulative sum from a table in a SQL Server database.

Query for running total in SQL Server

by Elina Blank

Using window functions (partition by) will speed up the process:SELECT Val, SUM(Val) OVER (Partition BY [YourPartitionValue] Order BY [OrderColumn] as CumulativeSumFROM [Table]

Query for running total in SQL Server

by iamalik

I guess this is more faster:SELECT T1.Field1, SUM(T2.Field1) FROM [Table] T1, [Table] T2WHERE T1.Field1 >= T2.Field1GROUP BY T1.Field1

Query for running total in SQL Server

by S Douglas

Another way of getting totals, whether it be by group or set is to use the CUBE and ROLLUP predicates.By group (each grouping of FIELD_DESCRIPTION will have a total sum):SELECT FIELD_DESCRIPTION , SUM(FIELD_VALUE)FROM tbl_DataGROUP BY FIELD_DESCRIPTIONWITH CUBEOr:By Cube...

Query for running total in SQL Server

by fordc03

This will work on SQL 2000/2005/2008.Most cumulative aggregations have constraints such as a begin and end date or grouped by customers.Your solution is good for a single aggregation without much filtering. People will find that the query plans generated would be very similiar to that of...

Query Results Column Information

by Dave Elliott

Find the column information of a SQL query

Quick Safe SQL Snippet

by groggyjava

SQL 2012 Code Snippet for quick blank transaction

Quickest Way to Create a .NET Database Application

by Subha.N

Tip to create a database application using C# .NET in the shortest time possible.

Random Date/Time Test Data Generation

by Gene R. Browning

Generate random dates (and times if you like) for your test data environment.

Read & Insert Data Using Stored Procedures in C#

by gayani dassa

Read & Insert Data Using Stored Procedures

Read a file using transact-sql.

by Md. Marufuzzaman

How easy to read a file without using of the seven extended stored procedures.

Reading Excel file in SQL Server

by pankaj_254768

Shows how to read an Excel file using SQL query commands from SQL Server

Real Time HTML Page Content Update with Blazor and SqlTableDependency

by Christian Del Bianco

In this simple example, we are going to see how to update an HTML page when a SQL Server table change occurs, without the need to reload the page or make asynchronous calls from the client to the server.

Record Count of Tables in SQL Server

by thatraja

The below query can be used to get the record count of all tables in the current database.

Record Count of Tables in SQL Server

by Md. Marufuzzaman

This is good... You can also consider this link.[^]

Record Count of Tables in SQL Server

by tarun_j200

Also use either this one:SELECT OBJECT_NAME(SYSINDEXES.id), SYSINDEXES.rowcntFROM SYSINDEXES INNER JOIN SYSOBJECTS ON SYSINDEXES.id = SYSOBJECTS.idWHERE SYSINDEXES.indid < 2 AND SYSOBJECTS.xtype = 'U'or this one:SELECT OBJECT_NAME(id),rowcnt FROM SYSINDEXES WHERE...

Record Count of Tables in SQL Server

by Pranay Rana

Number of different ways to get total number of rows from tables

Record Count of Tables in SQL Server

by Graham Cottle

Sometimes it is necessary to have the schema name included as well. I concatenate with the table name for my convenience, but it is not vital to do so.SELECT sys.sysindexes.rows, sys.schemas.name + '.' + sys.objects.nameFROM sys.objectsINNER JOIN sys.schemasON ...

REMOTING using SQL Server

by Member 3128846

Example to implement remoting concept using SQL Server database

Reorganize and Rebuild Indexes and Update Statistics

by GabrieleTronchin

A simple stored procedure to add at your database to keep it reactive

Replace Cyrillic Letters with Visually Similar Latin Letters Using T-SQL

by Pavel Sinkevich

If data source is populated manually, there is a typical problem: a mix of visually similar Cyrillic and Latin letters. This tip describes how to fix it.

Reporting Services Hide dulpicate row

by Mycroft Holmes

When grouping data within a List control I need a header table and child table. The data comes from 1 datatable where the 1st #n columns are duplicated for each detail rows.The usual answer to this is to set the hide duplicates value in the textbox. This suppresses the text but the row is...

Return Comma Separated String using SQL

by Yogesh Gulve

Return Comma Separated String using SQL

Rowversion: Version Stamping A Row

by SonuKSingh

rowversion datatype in SQL Server: Track which row has been modified

Run All Subscription Reports At Once in SSRS Manually

by Mubin M. Shaikh

Manually Run SSRS Scheduled Reports

Run Single Page Applications (SPA) Template using AngularJS, Web API, Owin Token Based Authentication and Mediator Design Pattern

by Ahmet AYDIN

This post describes step by step how to run a Single Page Application template using ASP.NET Web API 2 and angularJS and how to use Web Application Project whose name is Dalyan.

Save your Data from LDAP Injection Attack

by Jason Parms

Lightweight Directory Access Protocol (LDAP) Injection

Saving Changes Is Not Permitted On SQL Server 2008 Management Studio. How To Solve?

by IkhwanKrisnadi

How to solve when saving changes is not permitted on SQL Server 2008 Management Studio

Search in your DB-Objects With Multiple Keywords

by hmdmgd3

SQL code allows you to find objects using multiple keywords in your DB

Searching within all the Stored Procedures in a database

by Saral S Stalin

You can search for any string with in the TSQL scripts of all the Stored Procedures in a database using the syscomments table.

Select a Default Row in SQL

by DiponRoy

Select a default row for a query that returns no rows

SELECT from Stored Procedure in SQL Server

by sachin bankar

How to use the result set from a Stored Procedure in a Select statement.

Select Statement to HTML Table

by Luca Astolfi

User defined procedure for make an HTML table from T-SQL Select statment

Selecting Valid Records

by Wendelius

This tip demonstrates how to select valid records from a table that contains validity date limits.

Selective masking in SQL server 2005

by Jobless Creature

Masking selected characters in SQL, hiding few characters

Session Killer for SQL Server

by mparvez

Session killing from a SQL Server database.

Set Date/Time Format for a Web Page

by piyush_singh

This article will help us in setting the format of date values coming from a database.

Setting deadlock priority in SQL Server

by Saral S Stalin

Explains how to run a script with higher priority to avoid it getting terminated in a deadlock situation.

Setting up an MS Outlook Appointment C#

by DecodedSolutions.co.uk

This is a simple article describing how to create an add an outlook appointment to your MS Outlook Calender.

ShoppingCart using Gridview in ASP.NET

by headshot9x

How to create ShoppingCart using Gridview in asp.net with code VB

Shortest Path in SQL Server using hierarchyid

by Mehdy Moini

Implementing a map as a tree, and finding the shortest path, between the streets, is what you will see.

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

by Kale Yogesh

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Simple Daily SQL Server Database Backup Batch File

by Mike Trank

Simple Daily SQL Server Database Backup Batch File

Simple SQL Connection String Creation

by OriginalGriff

Did you know that Windows provides a built in way to build SQL connection strings for you? Neither did I ... until I found UDL files.

Simple SSRS Report using MDX Query

by Ajit Kumar Thakur Microsoft

This tip contains simple step by step solution to read SSAS cube data using MDX query and display in SSRS report.

Simple Technique to Exclude Column from SELECT * FROM TABLENAME

by SERokon

Exclude Column from SELECT * statement

SMO 101 - Getting all DB's

by bmckuhen

How to get a quick list of all DBs using SMO

Snippet and Surround features in SQL Server 2012

by Tigya

Snippet and Surround features in SQL Server 2012.

So you need to sort that Parameter list in SSRS from a SharePoint List

by rnbergren

Gives a run down of how to sort parameters for sharepoint lists selections

Solution to annoying error 0029 - Cannot convert type 'T' to 'System.Data.Linq.Link'

by Itai Basel

Finally found a solution to the annoying LINQ to SQL dbml error: CS0029 : Cannot implicitly convert type 'int' to 'System.Data.Linq.Link'.

Some Important SQL Queries

by Mani Bhushan

Some Important SQL Queries

by jim lahey

As a C# developer, I much prefer to perform this kind of task with SMO. The API is really nicely structured and gives quick, strongly-typed access to the nuts and bolts of SQL Server.Using SQL 2008 and Visual Studio 2010, you need to add the following references from C:\Program...

Sort AlphaNumeric Data in Sql

by Anurag Gandhi

I have gone through a lot of examples to sort alpha numeric data in sql. Here is my way:

Sorting 'Total' After Data Values

by PIEBALDconsult

Interspersing 'Total' rows among summarized data rows in the correct order

SOUNDEX() in Microsoft SQL 2005

by bbirajdar

Use of soundex() function in SQL server

sp_extendedproperty

by Paw Jershauge

An easy to use extendedproperty procedure (alternative to sp_dropextendedproperty, sp_addextendedproperty, sp_updateextendedproperty)

Sp_MSForeachTable to update tables having common columns

by Niladri_Biswas

Spilt DateTime Ranges in SQL Server

by DiponRoy

Split date time ranges into days or possibly other ranges using CTE in SQL Server

Split function

by db_developer

Split User Defined Function, MS SQL Server 2008

Split function in SQL

by Gauri Chodanker

SQL function in SQL

SQL - Converting a varchar parameter to a table

by Glagace_7763

Create a table from a Varchar list value

SQL Atomic Operation on UPDATE and DELETE

by Robby Tendean

How to do SQL atomic operations on UPDATE and DELETE syntax.

SQL Change Data Capture (CDC) table not working after adding new columns to the source table and Using CDC with a frequently changing schema

by Rajesh Manjarekar

When CDC is turned ON against a table and the underlying table schema changes, such as adding/removing a column, changing datatype etc

SQL function to verify 'Check Digit' of given VISA/Master Card

by Rajesh Anuhya

It help's to verify if the card number provided by the user is valid or not.

SQL Group by With Joins

by Pranay Rana

How to use Group By clause when joining to table Let's consider one scenario where I have two table employees (contains employee detail) and sales (contains infomation about sales done by employee).Structure of...

SQL Query with C# Typed Library - CooQ

by Guni Van

Mapping your database with C# object class, then make typed-safe query like SQL syntax

SQL script for creating an ASP.NET Identity Database

by Mr.Thursday

SQL script for creating ASP.Net Identity Database

SQL Script to Drop and Re-create All Foreign Keys in SQL Server

by Nitinpatel

This SQL script will generate a script to drop and create all foreign keys in database.

SQL Server - BLOB Import and Export

by mkader

SQL scripts to insert File to BLOB field and export BLOB to File

SQL Server - How to create LinkedServer using SMO

by Kanasz Robert

SQL Server - How to create LinkedServer using SMO

by Vivek Johari

Nice tip Kanasz :) You can also create the linkedserver through SQL SERVER MANAGEMENT STUDIO by following the following pathClick on the Server ObjectsRight click on the Linked servers under the Server Objects and select the New linked Server option.

SQL Server : How to Keep Database Restore History

by aasim abdullah

Backup and then restore of a database is a normal job for developers and DBAs. This script will be helpfull to findout, from which path, and which backup file a database was restored.

SQL Server 2008 Error: Saving changes is not permitted

by Prasad CM

How to fix this error.

SQL Server 2016 Helps Us to Protect the Sensitive Data

by Krishna KV

Dynamic masking using SQL Server 2016

SQL Server Assembly Creation

by deepakaitr12345

How to create an assembly in SQL Server and store it in the database.

SQL Server Backup Utility

by Charles T. Blankenship

Backup and Restore SQL Server utility (where there is no SMSS onsite)

SQL Server Connection Dialog (Extensible)

by Chris Winkelmann

Display a SQL Connection Dialog with extensible functionality to persist the resulting connection string to any location...

SQL Server Database Backup Status Report

by Dathuraj Pasarge

Know whether your databases are backed up successfully in the last 24 hours or not !!!

SQL Server failed to load assembly with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

by xszhu

32 bit or 64 bit SQL Server

SQL Server Health Check Script with Powershell

by Atul_Kapoor

Perform SQL Server Instance health check simultaneously on more than one server using Powershell

SQL Server Lock Critical Section

by Hernán Hegykozi

Marks a statement block as a critical section by obtaining the mutual-exclusion lock

SQL Server Logon Triggers Trouble

by Dimitri Witkowski

Recently I had a problem: I was developing a logon trigger for SQL Server, and there was a bug in it. After deploying the trigger to the server he didn't allow me to login anymore. I was in panic and thought I would have to reinstall SQL Server.Fortunately, I've found a solution how to...

SQL Server Management Studio: Basic Startup Options

by aasim abdullah

Using SQL Server Management Studio, have you ever come a cross following questions: Why every time, a new query window is open with MASTER database.? On opening SQL Server Management Studio, I need a new query window automatically ?

SQL Server Restart Health Check Script

by Atul_Kapoor

Steps for deploying Restart Health Check script in a SQL Server environment.

SQL Server Scripts: Get All Nested Stored Procedures List (Procedures with Dependent Procedures)

by aasim abdullah

A stored procedure can be called from another stored procedure as nested stored procedure. How to get a list of these stored procedures with their nested one

SQL Server StringPadding (String function)

by Surender Singh (CodeToastDev)

Custom StringPadding functions for SQL Server

SQL Server Template Explorer

by Sivaraman Dhamodharan

How do you use the Template explorer and assign parameter values.

SQL Server XIRR Function

by Michael Ecklin

This tip describes Microsoft SQL Server implementation of XIRR function.

SQL Server: Automatic Query Execution at Every Instance Startup

by aasim abdullah

Automatic query execution at every instance startup.

SQL Server: Bookmark Lookups, a Query Performance Killer

by aasim abdullah

You have place indexes but still queries are slow... are you facing bookmark lookup problem ???

SQL Server: CASE with Different Clause

by DiponRoy

Using CASE with variable/SET, SELECT/UPDATE statement, WHERE clause, JOIN & ON clause

SQL Server: Changed data capture without using Triggers

by aasim abdullah

How to use DELETED and INSERTED virtual tables to capture data change in result of any INSERT, UPDATE or DELETE statement.

SQL Server: Doing String Split And Join

by DiponRoy

How to do string split and join in SQL Server

SQL Server: Find Table Order/Generation And Relation Details

by DiponRoy

A utility query to find table generations in SQL Server relational database

SQL Server: Find Week Start And End DateTime

by DiponRoy

Finding week start and end date time from a given date time

SQL Server: Get All Databases Size

by aasim abdullah

A simple query to get all databases size on a given instance.

SQL Server: How to Get All Indexes List With Involved Columns Name

by aasim abdullah

Get all indexes list in detail, with their involved columns

SQL Server: How to Refresh Intellisense Cache

by aasim abdullah

How to Refresh Intellisense Cache in SQL Server

SQL Server: Keeping Log/Alert for Job Disable/Enable Status

by aasim abdullah

How do you know, if someone disabled your job at production database server. Who is the calprit. How to send a mail alert for this change ??

SQL Server: Restore Failure from Enterprise to Standard Edition

by aasim abdullah

Restore failure from Enterprise to Standard edition in SQL Server.

SQL Server: SQL script to find upcoming birthdays

by prashant0556.s.more

The script helps to find out upcoming birthdays even when days cross a year.

SQL Server: TOP…WITH TIES - A Beauty of TSQL

by aasim abdullah

The TOP clause is commonly used to get the top required rows from a result set. The beauty of this clause is that it can be used with the WITH TIES clause to retrieve all similar rows to a base result set.

SQL Synonyms :.: Avoiding Dynamic SQL

by Claudio cespon

A first approach to avoid Dynamic SQL in stored procedures

SQL Zip Compression, RegEx and Random Functions

by Brad Joss

SQL CLR Functions for Zip Compression and Regular Expression evaluations

SQL: Column Values as Comma Separated String

by Srinivas Kalabarigi

Column values as comma separated string in SQL

SQLAgent XPs disabled after repair of MSSQL 2008 R2

by Jan Steyn

How to resolve this.

sql-functions for better estimation of route-distances and traveling times in pure sql (without routing)

by Peter BCKR

a simple way to estimate as accurately as possible route-distances and traveling times without exact route calculation in sql

Sqlserver Trigger a quick look

by Prasanta_Prince

Sqlserver Trigger

Sqlserver Trigger a quick look

by SalizarMarxx

This article demonstrates the #1 failure when people write triggers.When writing SQL triggers you can NEVER be sure that you will ONLY have one record when processing the trigger results.In Your example only the first record from the INSERTED table will be captured, or worse random bits...

SqlServer: Parameterized Query With IN() Clause C#

by DiponRoy

A utility class to send parameters for IN() operator in SQL using parameterized queries

SSIS 2008 drop last data row from *.csv or *.txt file while import data using SSIS DataFlow Task..

by Rajesh Manjarekar

I found out that a possible cause is the difference between the SSIS 32 bits and 64 bits environment

SSIS insert bulk of data into remote table with trigger

by Ramy Mahrous

hmmm, nice we have SSIS it makes life easier but it usually comes with non-understood problems like "SQL Server Destination" (2185) failed with error code 0xC0202071 it means when you insert data into table in remote SQL Server don't use SQL Server Destination component however use OLE DB Destinatio

SSIS: Archive or Copy Files using dynamically specifying FileSpec file types for File Enumerator in Foreach loop

by Rajesh Manjarekar

How do I set FileSpec dynamically for looping files in a foreach loop container?

SSIS: Load Data in Dimension Table by Merge Join

by mparvez

How to load data from an Excel file to load in a dimension table in an incremental way.

SSIS: Load Data in Dimension Table Using SQL

by mparvez

Load data in Dimension Table using SQL.

SSIS: Normalize Table by Lookup

by mparvez

How to normalize a table by using lookup transformation.

SSIS: Transform data using Regex

by mparvez

Transform data by using regex and Script Component.

SSIS-Fuzzy lookup for cleaning dirty data

by mparvez

SSIS fuzzy lookup for cleaning dirty data.

SSRS - Report Viewer - Hide PDF Export option

by Chirag Vidani

How to hide PDF Export option in Report Viewer

Steps to install Microsoft SQL Server 2008 Express Edition (Standard or with Advanced Services)

by Jesus Carroll

This brief article show how to install quickly Microsoft SQL Server 2008 Express Edition Standard or with Advanced Services

Stored Procedure Data Gateway

by jstntham

Utility to auto generate individual Stored Procedure classes corresponding to each Stored Procedure on a Microsoft SQL Server database.

Stored Procedure Generator For SQL Server

by Mahesh Bailwal

This is a stored procedure generator for SQL Server

String concatenation in Transact-SQL

by Anton Burtsev

Performing fast concatenation of strings from different records

String concatenation in Transact-SQL

by AspDotNetDev

I like the original solution, especially when compared to this (which I am posting just to show that there are other solutions):-- Initialize table.CREATE TABLE #BigStrings( StringID bigint IDENTITY(1,1) NOT NULL, StringValue text NOT NULL, CONSTRAINT PK_BigStrings PRIMARY KEY...

String splitting/tokenizing using T-SQL User Defined Function

by smirson

This is an alternative for "String splitting/tokenizing using T-SQL user defined function."

String splitting/tokenizing using T-SQL user defined function.

by Irfan Baig

String splitting/tokenizing using T-SQL user defined function.

by Hajo Messner

There is a little inconsistence in this function, because the @Tokens Elements and the @Delimiter parameter are of data type NVARCHAR (=Unicode) while the @String parameter is of data type VARCHAR (=ANSI). If the @String parameter can be Unicode, it must be declared as NVARCHAR. If the @String...

Stupid CTE tricks -- string concatenation

by PIEBALDconsult

Concatenating values from multiple rows into one string value via a Common Table Expression

Stupid CTE tricks -- string concatenation

by Jörgen Andersson

Pretty cool little trick you've made there.With the help of an analytic function, I've fixed the ordering, and as a byproduct it also scales better:WITH ranked AS ( SELECT make,model,Rank() over (PARTITION BY make ORDER BY model) Rnk FROM MakeModel ),cte...

Term Lookup Transformation in SSIS

by Vinay Haravi

Term Lookup is the one of the powerful Transformations in SQL Server Integration Service. This transformation basically helps to analyze the set of text records.

The Best Table Finder (The BestCube Problem)

by BharatRamV

Finds the Best Table for a given set of columns

The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()

by Arif H Shigri

This tip will discuss the difference between ROW_NUMBER(), RANK() and DENSE_RANK().

The Pain and Anguish of DTS Script Debugging

by #realJSOP

When all else fails, fire off some events and bask in the glow of the IDE's Progress tab.

The SQL Server Rand Function

by Pascal Ganaye

Reproducing the SQL RAND() function in C#

The Web Developer Checklist : A One-Stop-Shop for Improving the Web

by Rion Williams

A one-stop-shop for improving the web.

Tip for SQL Server: Find Text Used in SPs, Functions and Table

by Jatinath

This tip will help you to find a particular text used in SPs, Functions or tables

TIP: Managing SqlGeography class from C#

by Jaime Olivares

How to Insert or Update an SqlGeography object in SQL Server 2008 from a C# application

Tip: Run a Transact-SQL Script (.SQL File) From the Command Line

by Brian C Hart

Provides the quick-and-dirty syntax to connect to a given Microsoft SQL Server instance so you can then run a .sql query script against it, e.g., if you are calling this from code as part of a larger pipeline

To cause an error when updating a row with non-existing lookup reference

by Wendelius

The tip shows how to intentionally cause an error in a situation where a non-existing reference would be updated.

To find duplicate rows in table

by Gaushick

To find duplicate rows in table

by Member 7759061

Here is an alternative way to find duplicates in a table by making use of over clause http://msdn.microsoft.com/en-us/library/ms189461.aspx[^];with duplicates as (SELECT rowno=row_number() over (partition by colname order by colname), colname, colname from TableName)select * from...

To remove or replace multiple special character from string using sql queries.

by santosh poojari

To remove or replace multiple special character from string using sql queries.

To Replicate/Clone/Duplicate, selected data in SQL Server 2012, 2008

by Syed Moula Ali

Replicate/Clone/Duplicate selected data in a Table using merge statment

TOP WITH TIES clause in SELECT queries

by Talking Dotnet

The SELECT TOP N query always returns exactly N records, and randomly drops any record that have the same value as the last record in the group.SELECT TOP 5 price, Booktitle FROM BookTitles ORDER BY price DESCThis query will give 5 records from table BookTitles in descending order by price. ProblemS

Tracking SQL-Server DB Objects Versions

by Tamer J. Mehyar

A simple way to keep track of DB objects versions (DDL updates) in SQL Server

Trailing spaces are ignored in SQL Server 2008

by zvin

When comparing data stored in databases, you don't need to use RTRIM()

Translating ANSI Column into Unicode (Arabic Code Page)

by Emad Sammour

Mapping table is used in the translation - output in Unicode (nvarchar)

Traverse a MSSQL table without using CURSOR

by Abdul Quader Mamun

To traverse a table without cursor, you only need to add an additional field in the table with null value. Here is the code for traversing a table.

Traverse an MSSQL Table without using CURSOR

by John B Oliver

This is an alternative for "Traverse a MSSQL table without using CURSOR"

Tricky SQL Questions

by sukeshchand

Unexpected SQL Interview Questions

Triki Game Using SQL Triggers

by salchichurria

Triki game created in SQL Server with triggers.

TrimChar to Remove Desired Character

by Wendelius

TrimChar function which removes occurences of the desired character from both ends of a string.

Try Catch in SQL - Reporting stored procedure error in email and also log the error in a table

by Smitten Ediot

Reporting stored procedure error in email and also log the error in a table.

T-SQL Function: Add a space between all characters

by Paul M Simpson

Expanding a string by inserting alternating single spaces between characters

T-sql over clause to aggregating without groupby

by pankajupadhyay29

A nice way to get aggregated value without group by

TSQL reparent an entire branch with hierarchyid

by Mycroft Holmes

Reparenting a Tree branch is a PITA using the HierarchyID functions. This reparents a branch by reformating the string representation of the HierarchyID. Supports moving the branch to the root node. I use an ID as the primary key so the proc expects the ID's of the node recordsDECLARE @NodeID INT

T-SQL Way of Calculating Next n Business Days (Including Nominated Public Holidays and Weekends)

by Member 12574862

A new approach of calculating next N business days's date, provided we know the start date and the N number

T-SQL: Most Practical Split Function

by Brad Joss

A SQL String Split function for the real world

T-SQL: Search SQL Code

by Brad Joss

Search your SQL code with simple T-SQL statements.

Type Preference with UNION in SQL Server

by Amey A khedekar

A scenario with data type of the resultant select columns with UNION.

Types Don't Match between the Anchor and the Recursive Part in Column...

by Wendelius

How to overcome the error message "Types don't match between the anchor and the recursive part in column..." for varchar columns in a recursive CTE query

UniDAC - database's direct connection

by Thiago Gaidzinski

UniDAC allows you to connect your cross-platform application directly with many database's types.

Uniqueidentifier Field Problem

by hussain.attiya

Update Database using Stored Procedure and DataAdapter

by Naresh Patidar

Insert, update and delete operations using stored procedure and DataAdapter in C#

Updating a temp table in a Stored Proc

by Mel Padden

How to update the schema of a temp table in a Stored Procedure in SQL Server

Updating a temp table in a Stored Proc

by Jeff Balcerzak

You might also want to try using a table variable - like this:DECLARE @tableName TABLE ( Key1 INT, Key2 INT, Description NVARCHAR(255), AggAmount FLOAT,)Then you can refer to your table by @tableName. Granted this is SQL Server 2005 and above, but then...

Uploading an Image and Displaying It on the UI Page

by Mannava Siva Aditya

Uploading an image and displaying it on the UI page

Uploading Zip File in ASP.NET

by chetan virkar

Uploading Zip File in ASP.NET

US Federal Holidays (SQL)

by #realJSOP

A scalar function to determine if a given DateTime is one of the ten US federal holidays, using SQL Server.

Use of columns belonging to tables referenced on main query in a subquery

by John Gathogo

Catastrophic results associated with unqualified use of columns belonging to tables referenced on main query in a subquery

Use PowerShell to Query and Display Data

by Joe Leibowitz

Build a simple GUI in PowerShell that will display data queried from a SQL Server table.

Use SQL Server's Ranking Functions Wisely

by Tecfield

Beware of What We Ask Them to Do

Use SQL to generate large data sequence

by DrABELL

Generate large data series by using Cartesian Product and small data table

Use Union All & Select Queries Effectively

by rajeshitpro

Union All,Select

Useful DateTime Functions

by db_developer

Reminder.Sou...

Useful DateTime Functions

by db_developer

IF OBJECT_ID (N'dbo.GetIntervalStartDate', N'FN') IS NOT NULL DROP FUNCTION dbo.GetIntervalStartDate;GO--gets first day of interval date belongs toCREATE FUNCTION dbo.GetIntervalStartDate (@Date datetime,@IntervalType int = 0--0 - DAY, 1 - WEEK (Mon to Sun), 2 - MONTH, 3 -...

Useful SQL Snippets & Own Functions for SQL Server, SQL Server CE, MySQL, SQLite, PostgreSQL

by IAndreev93

Standard functions usage, and my own functions... Allowing you simpler process tables... Date and time... Strings...

User Responsive Web Query Builder using ASP.NET

by Reeshabh Choudhary

Web platform based query builder to play with your database :)

Using Coalesce() in sqlserver

by blnreddy

SQL Server built-in functions Coalesce()

Using Common Table Expressions and Recursion in SQL Server 2008

by Gordon Kushner

By using recursion (and CROSS JOINS), you can generate data without looping or hard-coding INSERT statements into a temporary table.In the example below, you can use recursion to generate a week of scheduling data for three employees. -- Count variable, set to 7 for a week's worth of...

Using Effort - Entity Framework Unit Testing Tool

by Roger C Moore

This tip is a guide for using the Codeplex project Effort for testing Entity Framework applications without requiring the unit tests to actually hit the SQL Server database.

Using one Isnull() function to remove all null values from all columns a table in SQL Server

by KapilDevTripathi

Write a sql query in which we use only one IsNull() function and it will remove all Null values(if any) from entire result set(from all rows)

Using Pipeline from Parallel Extension Extras

by Darek Danielewski

The combined use of Pipeline and NullObject patterns greatly simplifies data processing

Using Pivot In SQL for Reporting

by pankajupadhyay29

Nice and fast way for reporting

Using Pivot In SQL for Reporting

by Feleke Feyissa

select * from(SELECT l.Prepared_Date as Prepared_Date , m.Material_Code as Material_Code ,n.Name as Name,n.Description as Description , m.Quantity as Quantity,n.Unit_Of_Measure as Unit_Of_Measure , m.Status as Status...

Using Pivot In SQL for Reporting

by Kannan K R

select ExDate, SUM(case when Dept='A' then expense else 0 end)[Store A Expense],SUM(case when Dept='B' then expense else 0 end)[Store B Expense],SUM(case when Dept='C' then expense else 0 end)[Store C Expense]from expenseTable where Dept in ('A','B','C') group by ExDate

Using ROWGUIDCOL in SQL Server

by mravikiran

Using ROWGUIDCOL in SQL Server

Using SQL Bulk Copy with your LINQ-to-Entities datacontext - Part 1

by Måns Tånneryd

This tip shows how to significantly speed up inserts of large amounts of data using the entity framework.

Using SQL bulk copy with your LINQ-to-Entities datacontext - Part 2

by Måns Tånneryd

This tip shows how to speed up inserts using POCO objects and the entity framework version 5.

Using SQL bulk copy with your LINQ-to-SQL datacontext

by Måns Tånneryd

How to use bulk insert with your LINQ-to-SQL datacontext

Using SQL bulk copy with your LINQ-to-SQL datacontext

by RupertC

If the order of your columns is different in the LINQ to SQL class from that in the database, then you also need to add a column mapping to the bulk copier. Otherwise you get mysterious errors in some of the loaded columns!In my simple fix, I assume that the names match even if the order may...

Using SSRS - Matrix Control to Generate Columns Dynamically

by S.Kaur

This will help you generate columns dynamically in SSRS reports using Matrix control.

Using Table variable instead of Cursor Variable

by S. M. Ahasan Habib

I will explain here how to use table variable and over come cursor limitation

View for SQL Agent Job Histories

by Brad Joss

Views to make analyzing SQL Agent Job statistics easier

Way to Debug SQL Parameters in ASP.NET

by Indrajeet Sutar

While using stored procedures, we need to pass SQL parameteres to those stored procedures. When there are large numbers of parameters then it is become boring task or tedious job to debug and check what values passed with. Here I have created a function that will check the values passed to...

Ways to Compare and Find Differences for SQL Server Tables

by Michael Ecklin

Sometimes, we need to compare 2 tables to see what was changed. This tip shows you 2 different ways to compare data.

Who Logged In Using SA Credentials?

by Deepak A S

An ALL SERVER trigger to know who it is!

Why do I get a "Parameter is not valid." exception when I read an image from my database?

by OriginalGriff

Why? Normally, it's because you stored it wrong...

Why Use ASP.NET for Web Development

by Afzaal Ahmad Zeeshan

A slight comparison of ASP.NET upon other (web) programming languages in the market and a discussion of me and some other (web) programming language developers.

Working with durations in MSSQL server

by Ilka Guigova

In dealing with durations in MSSQL server, I have found the following three resources helpful: :thumbsup: If you need to report a duration in a certain granularity such as "Task runs for ## minutes ## seconds", then refer to this[^] post:/*CREATE TABLE Tasks( ID INT IDENTITY PRIMARY...

WPF and MVVM pattern

by Oussama_Kahoul

Introduction to WPF technology and the MVVM design pattern

Write error log in a text file for Custom Assembly being used in Reporting Services

by alifaraze

writing error in a text file is very easy but writing error from a custom assembly in reporting services requires permissions

Writeback and writethrough using C#

by rakesh1503

Writeback and writethrough using C# with SQL Server 2008 SSAS.

XML File to SQL Database

by Altaf Ansari

Dynamic creation of an SQL table by using an XML file, and importing data from an XML file to an SQL table.

Your SQL Wrapped in Generated Code. Interesting? Original?

by bbsimonbb

World first implementation of the Dominic Strauss Kahn data access method!