Browse by Tags

Tagged Content List
  • Wiki Page: How to Compare Two Tables Definition / Metadata in Different Databases

    This article is about the example on T-SQL Script to compare two tables definition / metadata in different databases . The T-SQL Script [used to compare two tables definition / metadata in different databases] in this article can be used from SQL Server 2012 and above version because I have used...
  • Wiki Page: How to Check the Syntax of Dynamic SQL Before Execution

    This article is about the system function sys.dm_exec_describe_first_result_set that can be used to check the syntax of dynamic SQL before execution. This system function sys.dm_exec_describe_first_result_set was introduced in SQL Server 2012. Create sample table and insert sample data : ...
  • Wiki Page: How to Put a Sequence in a Column Already filled

    Suppose we would like to create a sequence in a table already filled with data. I found out this solution to be pretty easy: CREATE TABLE SEQ (COK NVARCHAR( 10 )) insert into seq values ( 'York' ),( 'Boston' ),( 'Chicago' ) select * from seq --step 1 , I create a table...
  • Wiki Page: Microsoft SQL Server 2012 New Functions

    EOMONTH We had a problem whenever we wanted to identify the end date of a month. There was no built in function. But now that problem is solved in SQL Server 2012. The function EOMONTH returns the date of the month. SELECT EOMONTH (‘05/02/2012’) ‘EOM Processing Date Output: 2012-02-29...
  • Wiki Page: SharePoint 2010: SQL Script to Backup Project Server Databases

    Backing up a Project Server database can be easier by using a procedure that includes all the database that you want to include. I drop that backup files on a share drive that is isolated from the server. Be sure the Share has write permissions for the SQL Process: USE MASTER BACKUP...
  • Wiki Page: Migrating SharePoint Database Instance to Another Server

    The first line in this article must be written to thank to Todd Klindt , Nauplius Trevor and Sean McDonough – world renowned SharePoint MVPs – for quickly helping me on Twitter in migrating a SharePoint database instance to another server. Migration is surely a nightmare to those who are responsible...
  • Wiki Page: T-SQL: Display Horizontal Rows Vertically

    This article is an outcome of my answer to this question on MSDN forum. Consider this scenario : Table 1: DEPARTMENT EMPID ENAME SALARY A/C 1 TEST1 2000 SALES 2 TEST2 3000 Table...
  • Wiki Page: SQL Server Query Language - Transact-SQL

    This article lists known TechNet articles that are devoted to various aspects of the Transact-SQL language. Table of Contents SQL Server in General PIVOT CLR Dates Related APPLY operator String Functions Meta-Data XML Miscellaneous Bulk Data (bulk insert / bcp / openrowset) See Also SQL Server...
  • Wiki Page: T-SQL Script Detach, Move, and Attach Database

    One of my tasks recently involved moving the physical files from a set of MSSQL databases from one partition to another. These databases were part of a Sharepoint Farm. There were a number of databases so I created the following script in order to detach, move and re-attach the databases. This...
  • Wiki Page: Antes y después del Merge – Upsert SQL Server (es-ES)

    Hace poco tiempo un cliente me comentaba sobre la necesidad de ejecutar comandos de tipo UPSERT, típicamente este proceso lo venía haciendo de forma manual, hacia una revisión uno a uno de los campos y luego mediante estructuras en TSQL hacia la carga o la actualización a su base de datos, por eso...
  • Wiki Page: Fixing Missing Data Based on Prior Row Information

    One of the commonly asked problems in the Transact-SQL forum is how to provide missing information based on the information in the first prior row that has data (or alternatively in the next row (by date)). One of the examples where this problem was discussed is this thread . In this thread...
  • Wiki Page: Sort Letters in a Phrase using T-SQL

    Table of Contents Problem definition Introduction Solution Limitations 1. Data Type 2. Data Length See Also Problem definition This article comes up from this MSDN forum post. The problem is how can we sort the letters in a phrase just using T-SQL? To clarify the question, for instance...
  • Wiki Page: T-SQL Useful Links

    This article will share collection of links in regards to various aspects in Transact-SQL language I acquired over the years and saved in links.txt file. Many of these links come very handy answering various questions in SQL Server related forums. Table of Contents Select Top N Rows per Group Performance...
  • Wiki Page: SQL Server PIVOT

    Table of Contents Problem Definition Common Problem Other Blogs See also Problem Definition Recently in this thread I helped to solve a relatively simple problem. I will quote my solution and then I will explain the main problem people often encounter with PIVOT solutions ; WITH...
  • Wiki Page: T-SQL: Dynamic Pivot on Multiple Columns

    Table of Contents How to make a dynamic PIVOT on multiple columns See also Other Languages How to make a dynamic PIVOT on multiple columns The problem of transposing rows into columns is one of the most common problems discussed in MSDN Transact-SQL forum . Many times the problem of creating...
  • Wiki Page: T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL

    This script was developed to answer the question in this thread I need query find all the SPs that used dynamic SQL We can execute dynamic sql using sp_executesql or just with Exec / Execute . To find the names of the StoredProcedure that may have used dynamic SQL, this script can be used...
  • Wiki Page: Understanding NOLOCK Query Hint

    In our day to day T-SQL querying we use lot of query hints to modify the way a particular query will be executed. When we specify query hint SQL Server produces optimized plan using this query hint. This can be dangerous if it is not tested before in UAT as it is known fact that query plan which SQL...
  • Wiki Page: T-SQL: Date-Related Queries

    In this article I plan to add various interesting date related queries. This article will expand when new problems will present themselves in the Transact-SQL forum. Table of Contents Finding Day Number from the Beginning of the Year Finding Beginning and Ending of the Previous Month See Also ...
  • Wiki Page: T-SQL: Gaps and Islands Problem

    This article will consider a simple classical Gaps & Islands problem asked recently in Transact-SQL Forum at MSDN with non original title "Query Help" . Table of Contents Problem Definition Solution See Also Problem Definition The thread originator was kind enough...
  • Wiki Page: How to Generate Index Creation Scripts for all Tables in a Database using T-SQL

    The need often arises to create or recreate the indexes for all tables in a database, especially in development and testing scenarios. This article presents a script to generate Index Creation Scripts for all tables in a database using Transact-SQL (T-SQL). The code block below will generate Index...
  • Wiki Page: Custom Sort in Acyclic Digraph

    Table of Contents Problem definition Vocabulary Digraph (Directed Graph) Acyclic Graph Acyclic Digraph Topological Ordering Solution See Also Problem definition This article is derived from this MSDN forum post. This article addresses the task of how to present a Tree in a custom order ...
  • Wiki Page: T-SQL Script to update string NULL with default NULL

    Problem : It is common to have nullable columns in the table,but if we populate those nullable columns with string NULL instead of default NULL,there araises the problem. Effects of Problem : If we populate nullable columns with string column ,we cannot make use of NULL functions available...
  • Wiki Page: T-SQL: Remove Leading and Trailing Zeros

    In this post I have consolidated few of the methods to remove leading and trailing zeros in a string . Here is an example : DECLARE @BankAccount TABLE (AccNo VARCHAR (15)) INSERT @BankAccount SELECT '01010' INSERT @BankAccount SELECT '0010200' ...
  • Wiki Page: Generate XML with Same Node Names using FOR XML PATH

    In this post we are going to see how we can generate XML in the below mentioned format from the relational data. < row > < column >1</ column > < column >1</ column > </ row > < row > < column >2</ column > <...
  • Wiki Page: T-SQL Script to Get Detailed Information about Index Settings

    This article is about the script which I wrote to get detailed information about Index settings. The script in this article does not show any information about missing indexes or index usage details, the script will show only the information about settings made on an Index using CREATE /ALTER INDEX...
Page 1 of 4 (87 items) 1234
Can't find it? Write it!