Click here to Skip to main content
15,868,016 members
Everything / Database Development / SQL Server

SQL Server

SQL-Server

Great Reads

by Wonde Tadesse
This tip/trick helps to set a default value for blob data type such as Image, VarBinary.
by Jovan Popovic(MSFT)
Mapping properties in EF model to database columns that contain JSON
by Steve Krile
Fully AJAX-enabled user control used to select names from a database in a drop-down format.
by Mubin M. Shaikh
Create time dimension with 24 hour plus values and time buckets in your data warehouse

Latest Articles

by yuvalsol
POCO generating application for SQL Server
by scastelli
SQL resolves special character challenges, streamlining NULL parameter handling
by Federico Di Marco
spSearchTables: a helper T-SQL stored procedure for digging into (large) databases
by Federico Di Marco
Query Excel or CSV files with T-SQL without importing them in a table first

All Articles

Sort by Title

SQL Server 

5 Jan 2011 by #realJSOP
You can also enclose your column name in brackets, like so: [Create Date]
12 Oct 2010 by Dave Hausler
Useful trick for handling numbers with variable length implied decimal points
3 Jan 2015 by Peter Piotti
A clean, simple alternative to using the Entity Framework for stored procedures data access in a .NET/SQL Server environment
1 Feb 2013 by S. M. Ahasan Habib
.NET TransactionScope and its default Transaction Isolation level issue.
14 Mar 2014 by Nikita D. Sinelnikov
HowTo: make the report background (elements) of Microsoft .NET Report (.rdlc) non-printable
28 Sep 2011 by devildx2050
SQL Server tips.
7 Sep 2012 by zippy1981
This is an alternative for "ODBCTracer"
8 Jul 2015 by Rodrick Kazembe
This is a step by step SQL Server Stored Procedures tutorial for beginners with insert, update, delete and select examples.
7 Jun 2010 by Lee Humphries
LINQ translates .count() to a SQL query that uses COUNT(*), but on large tables even count(*) can be too slow. Here's another way
8 Jun 2010 by basementman
Or, assuming your table has an index, you could do this "old-school" as:select MAX(rows) from sysindexes where id = Object_ID('{tablename}')
20 Oct 2016 by Bryan Kowalchuk
The two most common database lookup table patterns are examined
12 Jan 2016 by shijo joseph
An ADO.NET Layer for SQL Server and Oracle which makes it easier for interfacing with database
23 Dec 2015 by Md. Marufuzzaman
A simple tip/trick to get rows on n’th maximum number from a table
18 Dec 2015 by Md. Marufuzzaman
A simple example of Microsoft SQL Server Pad String
27 Mar 2012 by Marian Placko
This article shows a simple hack of a built-in MS SQL Server WITH ENCRYPTION mechanism
31 Oct 2016 by Chris Maunder
Timing SQL calls with millisecond accuracy
10 Nov 2013 by Rajat-Indiandotnet
A unique feature of SQL SERVER Loop with Go statement
17 Apr 2014 by gunjan k saxena
This tip will help to create an action filter to compress the contents like Json, partial view, etc.
11 Feb 2012 by Jobless Creature
HH:MM Time format addition
7 Jun 2011 by Shahriar Iqbal Chowdhury/Galib
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
10 Feb 2012 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
10 May 2015 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.
19 Feb 2015 by DrABELL
Technique to extend capability of standard SQL by adding the Aggregate Product Function
10 Aug 2011 by Wendelius
Or simply:exec msdb..sp_help_jobsp_help_job (Transact-SQL)[^]
13 Oct 2011 by Elina Blank
When cursors are inside transaction, it is sometimes easy to forget to close the cursor
13 Oct 2011 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...
18 Oct 2011 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.
18 Oct 2011 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...
19 Apr 2011 by Amit Kumar Tiwari
Boost application speed and performance
28 Apr 2011 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
18 Dec 2016 by NightWizzard
Convert amounts to their spoken equivalents
28 Aug 2015 by Bert O Neill
AngularJS and Web API Active Directory Security (Authorisation)
14 Nov 2012 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…”
10 Sep 2018 by Manjuke Fernando
How to give db_datareader access to a specific user across all the databases on a particular SQL Server
8 May 2012 by Chamila Nishantha
This tip describes how to add a running total column to a result set
14 May 2012 by Wendelius
This is an alternative for "Applying Running Total to a Result set"
4 Mar 2014 by Haydn Chapman
A method to update a table column with a random set of specified values
7 Dec 2015 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.
23 Sep 2014 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.
12 Dec 2016 by Suresh Dasari's
Auto Generate Code (CRUD) for 3 Layered architecture (Entity, Data Access & Business Layer) with Stored Procedures based on table design.
4 Jul 2022 by DiponRoy
How to auto increment column options in databases
12 Nov 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.
20 Sep 2016 by GhostHost
Automatic creation of non clustered indexes using system SQL entities
19 Apr 2012 by Deepak_Sharma_
This article will show how to create an auto-suggest TextBox that will suggest data from a SQL Server database column.
18 Sep 2012 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.
22 Nov 2011 by Ahmed Naqibul Arefin
How to backup and restore SQL Server database schema
29 Nov 2011 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...
26 Oct 2015 by Hernán Hegykozi
This tip will show how to perform a backup and restore database from an application in VB.NET 2010.
25 Jul 2012 by Dan Steuer
This article will help manage automatic database purges according to basic Document Management principles.
1 Oct 2014 by NaibedyaKar
How to check the query locking the table and to unlock it
18 Feb 2012 by Mahmud Hasan
Be Very Careful When You Write SQL Trigger
29 Jun 2011 by zvin
Use the SET FMTONLY ON and OFF option
2 Mar 2012 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 ...
5 Mar 2010 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...
28 Sep 2014 by Bhushan Mulmule
This is terribly unorganized document with mix of MVC, Entity Framework, LINQ, HTML 5 and JQuery how tos...
28 Mar 2012 by PIEBALDconsult
Performing a bitwise OR aggregate in SQL Server
31 Jan 2017 by #realJSOP
Using common table expressions to build data from thin air.
5 Jan 2011 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.
14 Jan 2016 by Bhishma Anil Kajaria
Browse and Copy data from your CSV file to SQL directly without any Windows application...
9 Jun 2019 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.
17 Jan 2013 by Wendelius
This tip describes how to calculate an aggregate for dynamically defined columns using UNPIVOT clause.
8 May 2017 by Nirav Prabtani
How to calculate distance between two geolocations or find out distance within a specific radius by SQL Server
29 Oct 2017 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.
7 Dec 2010 by Pranay Rana
CASE/WHEN return type
24 Nov 2010 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...
3 Oct 2011 by George Tryfonas
A (hopefully) simple way to implement a wildcard selection in combo box filters for Microsoft SQL Reporting Services
12 Jan 2016 by AgarwalPriyanka, ErGauravSharma
Challenges faced in data migration testing
6 May 2013 by Hernán Hegykozi
This tip explains how to calculate the Check Digit Vertical (CDV) and Horizontal (CDH) by SQL Server.
19 Jan 2013 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.
24 Jan 2011 by Sunasara Imdadhusen
Clear the transaction Log if you are facing a low disk space
4 Jan 2011 by thatraja
By this way tooHow to Shrink SQL Server database log files?[^]
23 Jan 2011 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...
3 Jan 2013 by Levente Kupás
CLR-addon for T-SQL DATE transformation and Split-String
5 Aug 2014 by Uppuluri Aditya
COALESCE and ISNULL in SQL Server - Part 2
8 Jan 2020 by Homero Ibarra Ramírez
Tool on T-SQL to code basic generator for SQL Server
12 Nov 2013 by Jobless Creature
CTE To find all the related nodes in a hierarcy
6 Dec 2012 by Ravi Tuvar
Concatenate rows with comma separated string in SQL.
8 Mar 2012 by Ramanujam Shankar
Using COALESCE function - concatenate many rows into a single text string output in SQL Server.
25 Feb 2012 by e-Lopes
How to concatenate many rows into a single text string using SQL Server 2008.
25 Feb 2012 by Sanjay K. Gupta
How to concatenate many rows into a single text string using SQL Server 2008
27 Jul 2015 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.
13 Nov 2012 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.
7 Jan 2015 by Masteramuk
JAVA - Connecting to SQL Server database using Windows Authentication or Active Directory
2 Dec 2012 by Bikash Karmokar
Connecting Windows Form Application With ADO.NET in C#
25 Jul 2013 by Kumar Pankaj Verma
How to convert a delimited string in SQL Server to a table column.
24 Jan 2017 by VINEET MISHRA 086
Convert column into rows without using pivot function in SQL
9 Apr 2016 by NightWizzard
Handling for hex expressions and the trailing '}'
2 Jan 2016 by RickZeeland
This console application converts an SQL Server database to PostgreSQL.
24 Feb 2014 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!
26 Sep 2013 by Ashkan.hosseini
Stored procedure that generates insert stored procedure for tables
16 Mar 2012 by Anurag Gandhi
A Query to copy an individual table data between databases
28 Sep 2015 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.