A week ago, I was invited to meeting of programmers. Subject of meeting was “Good, Better and Best Programming Techniques”. I had made small note before I went to meeting, so if I have to talk about or discuss SQL Server it can come handy. Well, I did not get chance to talk on that as it was very causal and just meeting and greetings. Everybody just talked about what they think about their job. I talked very briefly about SQL Server, my current job and some funny incident at work.
Everybody laughed big when I talked about funny bug ticket I received which was about – Client does not receive Email sent by system. Well, at the end it was resolved without any programming as client did not have email address and needed to open one.
Well, here is my note which I prepared to discuss in meeting. This is not complete and is not in very details. This note contains what I think is best programming technique in SQL. There are lots to add here and many opinion are very generic to SQL and other programming languages.
Notes prepared for “Good, Better and Best Programming Techniques” meeting
Do not prefix stored procedure with SP_ prefix. As they are first searched in master database, before it is searched in any other database.
Always install latest server packs and security packs.
Make sure your SQL Server runs on optimal hardware. If your operating system supports 64 bit SQL Server, install 64 bit SQL Server on it. Raid 10 Array.
Reduce Network Traffic by using Stored Procedure. Return only required result set from database. If application needs paging it should have done in SQL Server instead of at application level.
After running query check Actual Execution Plan for cost of the query. Query can be analyzed in Database Engine Tuning Advisor.
Use User Defined Functions sparsely, use Stored Procedures instead.
Stored Procedure can achieve all the tasks UDF can do. SP provides much more features than UDFs.
Test system with realistic data rather than sample data. Realistic data provides better scenario for testing and reveals problems with real system before it goes to production.
Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.
Avoid Cursors as it results in performance degradation. Sub Query, derived tables, CTE can perform same operation.
Reduces the use of nullable columns.
NULL columns consumes an extra byte on each column used as well as adds overhead in queries. Also NULL is not good for logic development for programmers.
Reduce deadlocks using query hints and proper logic of order in columns.
Normalized database always increases scalability and stability of the system. Do not go over 3rd normal form as it will adversely affect performance.
Use WHERE clauses to compare assertive logic. Use IN rather than NOT IN even though IN will require more value to specify in clause.
BLOBS must be stored filesystem and database should have path to them only. If path is common stored them in application variable and append with filename from the BLOBColumnName.
Always perform referential integrity checks and data validations using constraints such as the foreign key and check constraints.
SQL Server optimizer will use an index scan if the ORDER BY clause is on an indexed column.
Stored Procedure should return same numbers of resultset and same columns in any input parameters. Result Set of Stored Procedure should be deterministic.
Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.
Format SQL Code. Make it readable. Wrap it.
Use Column name in ORDER BY clause instead of numbers.
Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).
Join tables in order that they always perform the most restrictive search first to filter out the maximum number of rows in the early phases of a multiple table join.
Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.
Do not use temp tables use CTE or Derived tables instead.
Always take backup of all the data.
Never ever work on production server.
Ask someone for help if you need it. We all need to learn.
Reference : Pinal Dave (https://blog.sqlauthority.com)
I tried to do DTS export from tool menu using sql 2000. when I click source data: it doesn’t have Microsoft OLE DB provider for sql server.
I didn’t install sp3 for sql server 2000, is this problem or other problems?
Just a question.
How can I compare 2 rows of a table? Just want to get a boolean value: 1 means the 2 rows are identical; 0 means they’re different.
I’ve tried using “group by”
select ps_id, q1_1,q1_2
group by ps_id, q1_1,q1_2
PS_ID Q1_1 Q1_2
1010101 1 19840101
1010101 2 19840101
1020232 1 19840409
1020256 1 19440103
2020225 2 19450604
2020232 1 19840409
2060354 2 19900303
The 2 firsts rows are identical except for Q1_1 column (1 vs 2). I want my query to return just 1 row like this:
1010101 0 (means rows are different)
And, the table really has around 200 columns, so is there any way to do this dynamically?
In reply to jhonatan,
Somehow I think jhonatan is lysdexic and really should be spelled Johnathon, but… what do I know? I’m just a stupid American.
I solved problem for jhonatan:
–because you don’t use northwind database I
–have to create table and populate
–first with your data which makes no implicit sense
CREATE TABLE patientsurveys (
INSERT INTO patientsurveys (ps_id, q1_1, q1_2) VALUES (1010101, 1, 19840101)
INSERT INTO patientsurveys (ps_id, q1_1, q1_2) VALUES (1010101, 2, 19840101)
INSERT INTO patientsurveys (ps_id, q1_1, q1_2) VALUES (1020232, 1, 19840409)
INSERT INTO patientsurveys (ps_id, q1_1, q1_2) VALUES (1020256, 1, 19440103)
INSERT INTO patientsurveys (ps_id, q1_1, q1_2) VALUES (2020225, 2, 19450604)
INSERT INTO patientsurveys (ps_id, q1_1, q1_2) VALUES (2020232, 1, 19840409)
INSERT INTO patientsurveys (ps_id, q1_1, q1_2) VALUES (2060354, 2, 19900303)
–this is the form of the query you are asking for, no?
CONVERT(BIT,CASE WHEN (COUNT(q1_1) > 1) THEN 0 ELSE 1 END) AS STATUS
ps_id IN (SELECT ps_id FROM patientsurveys GROUP BY ps_id HAVING COUNT(*) > 1)
–be sure to drop this table now, since it makes no sense and
–serves no usefulness either here now, or in the future
DROP TABLE patientsurveys
Thanks Perry Way for your suggestion.
I am a regular reader of your blog.I am facing one problem.When I try to open Database engine tuning adviser,I get an error “database engine tuning adviser do not support Sql express(DATA client)”.Can you please suggest me how can I solve this error.Also can you please told me how “Database engine tuning adviser” is used.
Thanks for the articles Pinal Dave.
vivek use other than express client to open DTA because express edition do not support DTA functnality.
may be your problem will be solved
very informative tips. Thanks
Can you elaborate on why not to use temp tables?