What is the professional way of writing query in MS SQL server ?
what is the professional way of writing query in MS SQL server ?Need Sample example ?
You must Sign In to post a response.
Category: SQL Server
Need Sample example ?
#767870
Hi,
Nothing it should be understandable to everyone, for beginners as well as for highly experienced people too that is called we call it as professional.
While writing any query the first step to follow is make it as stored procedure and call that procedure in your application, then give proper indexes and proper joining etc.. like all type of query optimization techniques you should follow.
I will share you one simple example
This is one sample which I follow, if you provide the creator and modified details then it's give the brief description when it's gets created and modified etc....
--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.
N@veen
Blog : http://naveens-dotnet.blogspot.in/
Nothing it should be understandable to everyone, for beginners as well as for highly experienced people too that is called we call it as professional.
While writing any query the first step to follow is make it as stored procedure and call that procedure in your application, then give proper indexes and proper joining etc.. like all type of query optimization techniques you should follow.
I will share you one simple example
CREATE PROCEDURE procname
(
--parameter1,
--parameter2....
)
AS
/*
Created By :
Created On :
Description :
Modified By :
Modified On :
*/
BEGIN
SELECT col1, col2, col3
FROM tablename
WHERE conditions
END
This is one sample which I follow, if you provide the creator and modified details then it's give the brief description when it's gets created and modified etc....
--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.
N@veen
Blog : http://naveens-dotnet.blogspot.in/
#767878
Hi,
Writing a query in a manner that it should be easily understood is professional way.
Below are some points which we should keep in mind while writing sql queries.
•capitalize reserved words
•main keywords on new line
•can't get used to commas before columns
•always use short meaningful table aliases
•prefix views with v
•prefix stored procedures with sp or usp (however don't use "sp_" which is reserved for built in procs)
•don't prefix tables
•table names singular
For e.g,
SELECT
column1
, column2
, column3
FROM
tablename
WHERE
column1 = 'bar'
ORDER BY
column1
, column2
it is easy to read and debug in my opinion.
Writing a query in a manner that it should be easily understood is professional way.
Below are some points which we should keep in mind while writing sql queries.
•capitalize reserved words
•main keywords on new line
•can't get used to commas before columns
•always use short meaningful table aliases
•prefix views with v
•prefix stored procedures with sp or usp (however don't use "sp_" which is reserved for built in procs)
•don't prefix tables
•table names singular
For e.g,
SELECT
column1
, column2
, column3
FROM
tablename
WHERE
column1 = 'bar'
ORDER BY
column1
, column2
it is easy to read and debug in my opinion.
#767885
There are lot of best practices to write an SQL query, see below
- Avoid multiple join on single query
- Eliminate cursor from query
- Avoid use of non-corrected scalar query
- Use indexes for searching columns
- Create a Highly Selective Index
- Do normalization to avoid duplication
- Drop Unused Indexes
- Statistic Creation and Updates
- Only Retrieve the Data You Really Need
- Avoid Functions on the Left Hand-Side of the Operator
- Consider Getting Rid of Correlated Subqueries
- Avoid Wildcard Characters at the Beginning of a LIKE Pattern
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
- Avoid multiple join on single query
- Eliminate cursor from query
- Avoid use of non-corrected scalar query
- Use indexes for searching columns
- Create a Highly Selective Index
- Do normalization to avoid duplication
- Drop Unused Indexes
- Statistic Creation and Updates
- Only Retrieve the Data You Really Need
- Avoid Functions on the Left Hand-Side of the Operator
- Consider Getting Rid of Correlated Subqueries
- Avoid Wildcard Characters at the Beginning of a LIKE Pattern
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
#767896
Hi,
Points to remember when writing Queries.
1. Retrieve data which is actually needed.
2. For Example, if we want to know data is there in the table, dont go for count(*) instead go for select 1
Ex: SET @CT = (SELECT COUNT(*) FROM table);
If @CT > 0
BEGIN
//your code
END
instead do like
If EXISTS (SELECT 1 FROM table)
BEGIN
//your code
END
3.Make use temp tables, as performing all operations on big table all the times will lead to performance degradation.
4. Write queries in a way that can be understand by everyone by giving appropriate names and comments.
5. We can write queries in Stored procedure as it compiles only once, Instead of using the queries in application.
Regards,
SonyShiva
Never lose hope..You never know what tomorrow will bring
Points to remember when writing Queries.
1. Retrieve data which is actually needed.
2. For Example, if we want to know data is there in the table, dont go for count(*) instead go for select 1
Ex: SET @CT = (SELECT COUNT(*) FROM table);
If @CT > 0
BEGIN
//your code
END
instead do like
If EXISTS (SELECT 1 FROM table)
BEGIN
//your code
END
3.Make use temp tables, as performing all operations on big table all the times will lead to performance degradation.
4. Write queries in a way that can be understand by everyone by giving appropriate names and comments.
5. We can write queries in Stored procedure as it compiles only once, Instead of using the queries in application.
Regards,
SonyShiva
Never lose hope..You never know what tomorrow will bring
#767911
Hai Ajit,
There are the slandered ways to write the queries in Sql Server or in SSIS.
1. The query should be very clear and if required or query is containing many joins, use the 'alias names'
2. Make the query in multi lines so it will be quite readable
3. The syntax an semantics must be clear and easily understood.
Hope it will be helpful to you.
Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com
There are the slandered ways to write the queries in Sql Server or in SSIS.
1. The query should be very clear and if required or query is containing many joins, use the 'alias names'
2. Make the query in multi lines so it will be quite readable
3. The syntax an semantics must be clear and easily understood.
SELECT S.StudentId, S.StudentName, C. ClassName, SUM(A. Amount)
FROM Student S, Class C, Library L, Amount A
WHERE S.SyudetnId = C.StudentID
AND L.StudentId = S.StudentId
AND A. StudentId = S.StudentID
GROUP BY S.StudentId, S.StudentName, C. ClassName
HAVING COUNT (DISTINCT C.ClassId)>=2
Hope it will be helpful to you.
Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com
#767917
We should take from One by one. It should be start with basics,
For Select query,
For Update,
For Deleting,
Thanks,
Mani
For Select query,
Select (*/Column Name)
FROM TableName
WHERE
ORDER BY
For Update,
Update TableName
SET Column1=Value,Column2=Value,.....
Where (Condition)
For Deleting,
Delete FROM
Tablename WHERE (condition)
Thanks,
Mani
Return to Return to Discussion Forum