#1
  1. Unholy Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Location
    In hell, where did you think?
    Posts
    12,026
    Rep Power
    661

    Microsoft SQL Server Tips


    Avoid using NULL values in your SQL Server databases.
    Pertains to SQL Server 6.5 and higher

    Here's a short list of some of the problems you can expect to encounter if you use NULLs in your database.

    1) Client applications require extra programming logic to handle NULL values.

    2) Calculation, sorting, comparison and grouping operations can handle NULL values in unexpected and counterintuitive ways.

    3) Aggregates and join operations also exhibit unexpected or counterintuitive results (e.g., COUNT(*) and COUNT(MyField) produce different results).

    4) NULL values can lead to undesirable results for WITH CUBE, WITH ROLLUP, and other statistical operations.

    5) Nullable columns that contain NULL values cause a small but measurable sacrifice in performance since SQL Server has to perform an additional check to determine whether the column allows NULL values.

    Instead of allowing NULL values, you should set default values on your columns, such as 0 for numeric columns and an empty string ('') for text columns.
    -----------------------------------------------------------

    Return SQL Server resultsets in random order
    Pertains to SQL Server 2000 and higher

    There may be times when you want to return the records of a query in random order, such as return card values in a poker game application.
    A quick way to do this is to use the NewID() function.

    Example:
    Code:
    SELECT *
    FROM Cards
    ORDER BY NewID()
    -----------------------------------------------------------

    Examine details about your SQL Server programmatically
    Pertains to SQL Server 2000

    Code:
    SELECT SERVERPROPERTY('Edition')
    SELECT SERVERPROPERTY('IsSingleUser')
    SELECT SERVERPROPERTY('MachineName')
    SELECT SERVERPROPERTY('ProcessId')
    SELECT SERVERPROPERTY('ProductVersion')
    SELECT SERVERPROPERTY('ProductLevel')
    SELECT SERVERPROPERTY('ServerName')
    -----------------------------------------------------------

    Use DATEPART to display individual protions of dates and times.
    Pertains to SQL Server 7.0 and higher

    Example:
    Code:
    PRINT DATEPART(Mm, GETDATE())
    Constants that can be used
    Milliseconds = Ms
    Second = Ss
    Minute = Mi
    Hour = Hh
    Day of the Week = Dw
    Day of the Month = Dd
    Day of the Year = Dy
    Week = Wk
    Month = Mm
    Quarter of the Year = Qq
    Year = Yy
    If you know of any other tips or tricks pertaining to Microsoft SQL Server, feel free to post them here so that others may learn from them.
    Last edited by Memnoch; January 2nd, 2006 at 08:26 PM.
  2. #2
  3. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2006
    Posts
    2
    Rep Power
    0
    Originally Posted by Memnoch
    Avoid using NULL values in your SQL Server databases.
    Pertains to SQL Server 6.5 and higher


    Instead of allowing NULL values, you should set default values on your columns, such as 0 for numeric columns and an empty string ('') for text columns.
    What is the default value for a Date field that hasn't been filled?

    TIA,

    Brad Z.
  4. #3
  5. No Profile Picture
    ASPFree Know-It-All
    ASP Adventurer (500 - 999 posts)

    Join Date
    Aug 2004
    Posts
    932
    Rep Power
    334
    1/1/1900
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2007
    Posts
    13
    Rep Power
    0
    what will happen when I querry this from any dataset->tableadapter. Will this not force constraint error, whenever I will not use all non-null fields in my querry ....?
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2009
    Posts
    1
    Rep Power
    0
    Originally Posted by bzlomke
    What is the default value for a Date field that hasn't been filled?

    TIA,

    Brad Z.
    MySQL does not allow setting the default value of a column to an expression. TIMESTAMP columns are a limited exception. A simple trigger is something like this:

    CREATE TRIGGER triggerName
    BEFORE INSERT ON yourTable
    FOR EACH ROW
    SET NEW.date = curdate();
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2011
    Posts
    1
    Rep Power
    0
    Very useful tips for me...I am just newbie for MS SQL
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2011
    Posts
    7
    Rep Power
    0
    There are some tips which can help you:

    Use ALTER DATABASE to Move Databases.
    Play it Safe and Configure a Fail-Safe Operator .
    Configure SQL Server Agent to Restart Services Automatically .
    How to use Windows PowerShell to Manage SQL Server .
    Create Server Groups to Manage SQL Server .
    Learn About Manually Tweaking Parallel Processing Settings .
    Know when not to Maximize Data Throughput for Network Applications.
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2011
    Posts
    8
    Rep Power
    0
    I used to work in a location in which a common practice was to make use of Set Programming. I remember the number of little issues we could learn from one another whenever working together around the code. Picking up brand new cutting corners, code snippets and so on. with time considerably improved our effectiveness associated with composing signal.

    Since i have began dealing with SQL Host I have been remaining by myself. The very best routines I'd normally select from working with other people that we cannot do right now.

    So here is the question:

    What are a person tips on effectively writing TSQL signal using SQL Host Management Studio?
    Please keep the tips to 2 - 3 things/shortcuts that you simply believe enhance you pace associated with coding
    Please stay within the range of TSQL and SQL Server Management Studio 2005/2008 If the feature is actually specific to the edition associated with Administration Facilities make sure you indicate: at the.g. “Works with SQL Server 2008 only"
    Many thanks

    Modify:

    I am afraid that I might have been misinterpreted by a number of a person. I am not searching for tips for composing effective TSQL code but instead for suggestions about how to efficiently make use of Administration Facilities to hurry in the coding procedure itself.

    The kind of solutions that i'm looking for tend to be:

    utilization of templates,
    keyboard-shortcuts,
    utilization of IntelliSense plug ins and so on.
    Essentially individuals little things which make the actual html coding encounter a little more efficient and pleasant.
  16. #9
  17. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2011
    Posts
    8
    Rep Power
    0

    Microsoft SQL Server Tips


    I have no tip yet but share you all different domain wrkgroups, The remote server can be in the same domain, a different domain, or no domain at all (workgroup). How do we configure Log Shipping to work with a secondary server that is in a different domain or workgroup than our primary server? Check out this tip to learn more....
  18. #10
  19. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    Sql


    Really a very professional forum, will come back for more.

    Code 39, barcode, EAN-13, EAN-8

Similar Threads

  1. SQL 7 database hosted on sql 2000 server - problems
    By gurubatham in forum SQL Development
    Replies: 0
    Last Post: November 13th, 2004, 07:26 PM
  2. Replies: 1
    Last Post: November 9th, 2004, 08:17 PM
  3. Microsoft OLE DB Provider for SQL Server error '80004005'
    By yiming1 in forum ASP Development
    Replies: 1
    Last Post: October 3rd, 2004, 08:45 AM
  4. something wrong in the table section
    By gilgalbiblewhee in forum ASP Development
    Replies: 2
    Last Post: August 9th, 2004, 04:12 PM

IMN logo majestic logo threadwatch logo seochat tools logo