Category Archives: SQL

What are the difference between DDL, DML and DCL commands?

What are the difference between DDL, DML and DCL commands? DDL – Data Definition Language: statements used to define the database structure or schema. Some examples: CREATE – to create objects in the database ALTER – alters the structure of the database DROP – delete objects from the database TRUNCATE – remove all records from [...]

Charindex, small functionality but usefull

CHARINDEX (Transact-SQL) Searches expression2 for expression1 and returns its starting position if found. The search starts at start_location. Few good scripts with charindex select CHARINDEX(‘a’, ‘india’) –result will be 5 select CHARINDEX( ‘,1,’ ,’,1,2,3,’) – result would be 3 DECLARE @selectedID VARCHAR(10) SET @selectedID = ’1,2′ –SET @selectedID = ’1,5′ Some times we have to [...]

basics of SQL Server Transaction Isolation Levels

I read one good article about sql server – transaction isolation level.. You might like it Articl Summary: This post discusses the basics of SQL Server Transaction Isolation Levels. http://www.sqlserverandxml.com/2008/08/introduction-to-sql-server-transaction.html

Basics of query process

i have read one good blog which explains basics of query process. Here is the order in which query operators are evaluated. There are 11 levels. 1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. WITH{CUBE | ROLLUP} 7. HAVING 8. SELECT 9. DISTINCT 10. HAVING 11. TOP First the query processor [...]

Correlated Joins Using ‘Apply’

SOURCE: http://www.sqlservercentral.com/articles/T-SQL/63115/ One of my cohorts came to me a while back with a SQL Query challenge. He was creating a report for our Live Voice call center application in which he needed to list all Live Voice Transactions within a given date range (easy stuff so far). The business rules, however, dictate that a [...]

Data Type Mapping

Mapping data types into different environments like .Net and SQL server. ASP.NET Control DNN KickStart Field type .NET Framework SQL Server ADO DataType Enum ADO DataType Enum Value Size Access Oracle Visual Basic 6.0 Int64 SqlDbType.BigInt OleDbType.BigInt bigint adBigInt 20 8 Variant Byte[] SqlDbType.VarBinary OleDbType.Binary binary timestamp adBinary 128 50 8 Raw Variant Yes/No Boolean [...]

Changing from Non-IDENTITY to IDENTITY and vice versa

http://www.sqlservercentral.com/articles/T-SQL/61979/ Printed 2008/02/25 03:23AM Changing a Non-IDENTITY column to IDENTITY and vice versa By Thomas Pieries, 2008/01/16 Changing from Non-IDENTITY to IDENTITY and vice versa In my career I have come across many situations where it was required to include/remove the Identity Property to/from a column at a latter stage of the development cycle due [...]

Get Table Schema from MS SQL

You can get table schema or columns of table from query using system object. Try this select * from information_schema.tables select * from information_schema.columns where column_name = ‘StudentID’ select substring(o.name,1,50) as “Table Name”, c.colid, substring(c.name,1,30) as “Column Name”, substring(t.name,1,30) as “DataType”, c.length from sysobjects o left join syscolumns c on (o.id=c.id) left join systypes t [...]

SQL Server Transact-SQL General Tips

from SQL-Server-Performance.com SQL Server Transact-SQL General Tips By : Brad McGehee   Don’t include code, variable, or parameters that don’t do anything. This may sound obvious, but I have seen this in some off-the-shelf SQL Server-based applications. For example, you may see code like this: SELECT column_name FROM table_name WHERE 1 = 0 When this [...]

Select/Find object with specific word in SQL

Using below query , you can fetch all objects SP/UDF/Views from db which uses specific word. SELECT DISTINCT o.[name] FROM sysobjects o INNER JOIN syscomments c ON (c.id = o.id) WHERE xtype in (‘P’,’V’) and category = 0 AND c.[text] LIKE ‘%wordd%’

Follow

Get every new post delivered to your Inbox.