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 [...]
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 pass comma seprated value to sp and have [...]
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
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 reads all the rows from the FROM the left table and [...]
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 Live Voice [...]
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
SqlDbType.Bit
OleDbType.Boolean
bit
adBoolean
11
1
2
YesNo
Boolean
String
OleDbType.BSTR
adBSTR
8
(DataReader)
adChapter
136
Text
Document
String
SqlDbType.Char
OleDbType.Char
char
adChar
129
X
Char
String
Money
Decimal
SqlDbType.Money
OleDbType.Currency
money
smallmoney
adCurrency
6
8
4
0
Currency
Currency
DateTime
OleDbType.DBDate
adDate
7
0
DateTime
Date
DateTime
OleDbType.DBDate
adDBDate
133
DBFileTime
adDBFileTime
137
DateTime
OleDbType.DBTime
adDBTime
134
Date
Time
DateTime
SqlDbType.DateTime
OleDbType.DBTimeStamp
datetime
smalldatetime
adDBTimeStamp
135
8
4
0
DateTime
Date
Date
Decimal
OleDbType.Decimal
adDecimal
14
Decimal
Variant 6
Double
SqlDbType.Float
OleDbType.Double
float
adDouble
5
8
0
Double
Float
Double
Empty
adEmpty
0
External-Exception
OleDbType.Error
adError
10
DateTime
OleDbType.Filetime
adFileTime
64
Guid
SqlDbType.UniqueIdentifier
OleDbType.Guid
uniqueidentifier
adGUID
72
16
ReplicationID
Variant
Object
OleDbType.IDispatch
adIDispatch
9
Identifier
Number
Int32
SqlDbType.Int
OleDbType.Integer
identity
int
adInteger
3
4
4
AutoNumber
Long Integer
Int
Long
Object
OleDbType.IUnknown
adIUnknown
13
Byte[]
SqlDbType.VarBinary
OleDbType.LongVarBinary
image
adLongVarBinary
205
2147483647
OLEObject
Long Raw
Blob
Variant
String
SqlDbType.VarChar
OleDbType.LongVarChar
text
adLongVarChar
201
2147483647
Memo
Hyperlink
Long
Clob
String
String
SqlDbType.NText
OleDbType.VarWChar
ntext
adLongVarWChar
203
1073741823
Memo
Hyperlink
NClob
String
Decimal
SqlDbType.Decimal
OleDbType.Decimal
decimal
numeric
adNumeric
131
9
Decimal
Decimal
Integer
Number
SmallInt
Variant
Object
OleDbType.PropVariant
adPropVariant
138
Single
SqlDbType.Real
OleDbType.Single
real
adSingle
4
4
Single
Single
Int16,
SqlDbType.SmallInt
OleDbType.SmallInt
smallInt
adSmallInt
2
2
Integer
Integer
Byte
OleDbType.TinyInt
adTinyInt
16
UInt64
OleDbType.UnsignedBigInt
adUnsignedBigInt
21
UInt32
OleDbType.UnsignedInt
adUnsignedInt
19
UInt16
OleDbType.UnsignedSmallInt
adUnsignedSmallInt
18
Byte
SqlDbType.TinyInt
OleDbType.UnsignedTinyInt
tinyInt
adUnsignedTinyInt
17
1
Byte
Byte
adUserDefined
132
Byte[]
SqlDbType.VarBinary
OleDbType.VarBinary
varbinary
adVarBinary
204
50
ReplicationID
Variant
String
SqlDbType.VarChar
OleDbType.VarChar
varchar
adVarChar
200
X
Text
VarChar
String
Object
SqlDbType.Variant
OleDbType.Variant
sql_variant
adVariant
12
8016
VarChar2
Variant
OleDbType.VarNumeric
adVarNumeric
139
String
SqlDbType.NVarChar
OleDbType.VarWChar
nvarchar
adVarWChar
202
X
Text
NVarChar2
String
String
SqlDbType.NChar
OleDbType.WChar
nchar
adWChar
130
X
String
and the Managed Extensions for C++. The table also includes entries for the Object and String classes, for which many languages have corresponding keywords.
Category
Class name
Description
Visual Basic data type
C# data type
Managed Extensions for C++ data type
JScript data type
Integer
Byte
An [...]
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 to [...]
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 on (c.xusertype=t.xusertype)
where substring(o.name,1,250) like ‘%tblStudentMaster%’
order by 1 ASC
See order by here, [...]
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 [...]
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%’
