| SQL DB General Rules |
|
Share Dot Net Experience
SQL DB General Rules
Labels:
SQL Server
SQL SERVER – Guidelines and Coding Standards
Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.
Example: UserDetails
If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.
Example: Page_ UserDetails
Use following naming convention for Stored Procedure. sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb
Example:spApplicationName_GetUserDetails
Use following Naming pattern for triggers: TR_<TableName>_<action><description>
Example:TR_UserDetails_UpdateUserName
Indexes : IX_<tablename>_<columns separated by_>
Example:IX_UserDetails_UserID
Primary Key : PK_<tablename>
Example:PK_UserDetails
Foreign Key : FK_<tablename_1>_<tablename_2>
Example:FK_UserDetails_Emails
Default: DF_<table name>_<column name>
Example:DF_ UserDetails_UserName
Normalize Database structure based on 3rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here)
Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.
Example:SELECT Username, Password
FROM UserDetails
Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure. (Read More Here)
Properly format SQL queries using indents.
Example:SELECT Username, Password
FROM UserDetails ud
INNER JOIN Employee e ON e.EmpID = ud.UserID
Practice writing Upper Case for all SQL keywords.
Example:SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.
It is common practice to use Primary Key as IDENTITY column but it is not necessary. PK of your table should be selected very carefully.
If “One Table” references “Another Table” than the column name used in reference should use the following rule :
Column of Another Table : <OneTableName> ID
Example:
If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.
Columns with Default value constraint should not allow NULLs.
Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.
Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it next suggestion.
Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here)
INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....
Avoid using spaces within the name of database objects; this may create issues with front-end data access tools and applications. If you need spaces in your database object name then will accessing it surround the database object name with square brackets.
Example:[Order Details]
Do not use reserved words for naming database objects, as that can lead to some unpredictable situations. (Read More Here)
Practice writing comments in stored procedures, triggers and SQL batches, whenever something is not very obvious, as it won’t impact the performance.
Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
Indent code for better readability. (Example)
While using JOINs in your SQL query always prefix column name with the table name. (Example). If additionally require then prefix Table name with ServerName, DatabaseName, DatabaseOwner. (Example)
Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here)
Avoid using rules of database objects instead use constraints.
Do not use the RECOMPILE option for Stored Procedure unless there is specific requirements.
Practice to put the DECLARE statements at the starting of the code in the stored procedure for better readability (Example)
Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure.
Example: UserDetails
If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.
Example: Page_ UserDetails
Use following naming convention for Stored Procedure. sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb
Example:spApplicationName_GetUserDetails
Use following Naming pattern for triggers: TR_<TableName>_<action><description>
Example:TR_UserDetails_UpdateUserName
Indexes : IX_<tablename>_<columns separated by_>
Example:IX_UserDetails_UserID
Primary Key : PK_<tablename>
Example:PK_UserDetails
Foreign Key : FK_<tablename_1>_<tablename_2>
Example:FK_UserDetails_Emails
Default: DF_<table name>_<column name>
Example:DF_ UserDetails_UserName
Normalize Database structure based on 3rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here)
Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.
Example:SELECT Username, Password
FROM UserDetails
Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure. (Read More Here)
Properly format SQL queries using indents.
Example:SELECT Username, Password
FROM UserDetails ud
INNER JOIN Employee e ON e.EmpID = ud.UserID
Practice writing Upper Case for all SQL keywords.
Example:SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.
It is common practice to use Primary Key as IDENTITY column but it is not necessary. PK of your table should be selected very carefully.
If “One Table” references “Another Table” than the column name used in reference should use the following rule :
Column of Another Table : <OneTableName> ID
Example:
If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.
Columns with Default value constraint should not allow NULLs.
Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.
Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it next suggestion.
Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here)
INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....
Avoid using spaces within the name of database objects; this may create issues with front-end data access tools and applications. If you need spaces in your database object name then will accessing it surround the database object name with square brackets.
Example:[Order Details]
Do not use reserved words for naming database objects, as that can lead to some unpredictable situations. (Read More Here)
Practice writing comments in stored procedures, triggers and SQL batches, whenever something is not very obvious, as it won’t impact the performance.
Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
Indent code for better readability. (Example)
While using JOINs in your SQL query always prefix column name with the table name. (Example). If additionally require then prefix Table name with ServerName, DatabaseName, DatabaseOwner. (Example)
Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here)
Avoid using rules of database objects instead use constraints.
Do not use the RECOMPILE option for Stored Procedure unless there is specific requirements.
Practice to put the DECLARE statements at the starting of the code in the stored procedure for better readability (Example)
Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure.
Labels:
SQL Server
SQL SERVER – Stored Procedure Optimization Tips – Best Practices
- Include SET NOCOUNT ON statement
- Use schema name with object name
- Do not use the prefix “sp_” in the stored procedure name
- Use IF EXISTS (SELECT 1) instead of (SELECT *)
- Use the sp_executesql stored procedure instead of the EXECUTE statement
- Try to avoid using SQL Server cursors whenever possible
- Keep the Transaction as short as possible
- Use TRY-Catch for error handling
Labels:
SQL Server
Auto Increment in VB.Net
Today, We will discuss about "How to Auto Increment in Vb.Net?"
In this Code, I create customer Id like "CI00001"
Sub CustomerId()
Dim strID As String = ""
Dim St As String = ""
CON = ConOpen()
CMD = New SqlCommand("pass your sp or directquery", CON)
CMD.CommandType = CommandType.StoredProcedure
CON.Open()
DR = CMD.ExecuteReader
If DR.Read() Then
strID = DR(0).ToString
End If
DR.Close()
CON.Close()
Dim INNO As Integer
Dim IndID As String = ""
Dim Num As Integer = "0001"
Dim CPCode1 As String()
Dim CPComp As String
If strID = "" Then
txtCustomerId.Text = "CI0000" & Num
Else
CPCode1 = strID.Split(New Char() {"I"c})
For Each CPComp In CPCode1
Console.WriteLine(CPComp)
IndID = CPComp
Next
INNO = IndID + Num
If INNO < 10 Then
txtCustomerId.Text = "CI0000" & INNO
ElseIf INNO >= 10 And INNO <= 99 Then
txtCustomerId.Text = "CI000" & INNO
ElseIf INNO >= 100 And INNO <= 999 Then
txtCustomerId.Text = "CI00" & INNO
ElseIf INNO >= 1000 And INNO <= 9999 Then
txtCustomerId.Text = "CI0" & INNO
ElseIf INNO >= 10000 Then
txtCustomerId.Text = "CI" & INNO
End If
End If
End Sub
Dim strID As String = ""
Dim St As String = ""
CON = ConOpen()
CMD = New SqlCommand("pass your sp or directquery
strID = DR(0).ToString
End If
DR.Close()
CON.Close()
Dim INNO As Integer
Dim IndID As String = ""
Dim Num As Integer = "0001"
Dim CPCode1 As String()
Dim CPComp As String
If strID = "" Then
txtCustomerId.Text = "CI0000" & Num
Else
CPCode1 = strID.Split(New Char() {"I"c})
For Each CPComp In CPCode1
Console.WriteLine(CPComp)
IndID = CPComp
Next
INNO = IndID + Num
If INNO < 10 Then
txtCustomerId.Text = "CI0000" & INNO
ElseIf INNO >= 10 And INNO <= 99 Then
txtCustomerId.Text = "CI000" & INNO
ElseIf INNO >= 100 And INNO <= 999 Then
txtCustomerId.Text = "CI00" & INNO
ElseIf INNO >= 1000 And INNO <= 9999 Then
txtCustomerId.Text = "CI0" & INNO
ElseIf INNO >= 10000 Then
txtCustomerId.Text = "CI" & INNO
End If
End If
End Sub
In this Code, I create customer Id like "CI00001"
Labels:
VB.Net
Entity Framework 6.0 Namespace
| Entity Framework 6.0 |
|
Labels:
Entity FrameWork
| Entity Framework 6.0 Features |
|
Labels:
Entity FrameWork
Request Flow in MVC
| Request Flow in MVC |
| Answer:Request -->Routing --> Handler -->Controller --> Action --> View --> Response |
Labels:
MVC
Entity Framework 5.0 Features
| Entity Framework 5.0 Features |
|
Labels:
Entity FrameWork
Define ASP .NET MVC Routing
| Define ASP .NET MVC Routing |
|
Expression Blend
Welcome to Expression Blend:
| Expression Blend 2.0 |
|
| Advantages of Expression Blend |
|
Labels:
Expression Blend