You must Sign In to post a response.
  • Category: SQL Server

    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 ?
  • #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


    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.

  • #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]

  • #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

  • #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.

    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,


    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


Sign In to post your comments