Resources

Table Naming Conventions

Table Conventions

  • Tables are usually entity you are modeling for persistence. So make sure the names are in proper English and carry natural meanings. These names should make sense and should be descriptive.
  • Avoid acronyms and abbreviations if at all possible. If acronyms are used, then make sure to capitalize them. Abbreviations used should be well known abbreviations and should be camel cased.
  • Avoid using plural names for tables, use singular forms. This will avoid errors due to pluralization of words and also when moving table design to objects or entities. The name should map to the entity it is modeling.

– DO THIS –

blog_user

blog_page

person

box

activity

 

– NOT THIS –

blog_users

blog_pages

people

boxes

activities

  • Use namespaces for tables whenever grouping is needed. This grouping is a clear separation when working with the tables and can also provide a good basis for security, as you could secure via the namespace. Namespaces should be separated by an underscore and then the name should be camel cased.

– DO THIS –

lookups_country

lookups_state

lookups_regionalOffice

hr_employee

hr_salary

is_employee

is_vacationDay

 

– NOT THIS –

LookupsCountry

lookupsState

LOOKUPS_RegionalOffice

HREmployee

ISEmployee

ISVacationDay

  • Do not use prefixes like tbl,db as they are EXTREMELY redundant and useless.
  • All tables should have at least ONE primary key.

Perhaps something like “rds_Users” (where “rds” is the name of your application) would be more appropriate.

 

To read more database naming conventions link is here: http://wiki.coldbox.org/wiki/DatabaseNamingConventions.cfm

 

tsql optimizations

Here are the links:

http://msdn.microsoft.com/en-us/library/ff650689.aspx

http://www.codeproject.com/Articles/35665/Top-10-steps-to-optimize-data-access-in-SQL-Server

http://www.codeproject.com/Articles/34142/Understanding-Set-based-and-Procedural-approaches

 

Here are admin queries that will help analyze longest running scripts, etc.

Get username and/or IP address responsible for a query

http://dba.stackexchange.com/questions/46009/get-username-and-or-ip-address-responsible-for-a-query

 

SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/

 

Find the Most Time Consuming Code in your SQL Server Database

http://www.sqlservercurry.com/2010/07/find-most-time-consuming-code-in-your.html

 

Find Last Run Query in SQL Server

http://www.sqlservercurry.com/2011/12/last-run-query-in-sql-server.html

 

HOW TO: Kill Processes (Sessions) on SQL Server Using T-SQL

http://www.willstrohl.com/Blog/PostId/78/HOW-TO-Kill-Processes-Sessions-on-SQL-Server-Usin

 

enjoy… :)

 

 

here are admin queries to for us to have a report on the following:

  1. Top 10 codes that takes maximum time
  2. Find Longest Running Query
  3. Find Last Run Query in SQL Server

 

–Top 10 codes that takes maximum time

select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,

last_execution_time from sys.dm_exec_query_stats as stats

cross apply

(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))AS query_text

order by total_elapsed_time desc

 

 

– Find Longest Running Query

SELECT DISTINCT TOP 10

t.TEXT QueryName,

s.execution_count AS ExecutionCount,

s.max_elapsed_time AS MaxElapsedTime,

ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

s.creation_time AS LogCreatedOn,

ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

ORDER BY

s.max_elapsed_time DESC

GO

 

 

Find Last Run Query in SQL Server

SELECT conn.session_id, sson.host_name, sson.login_name,

sqltxt.text, sson.login_time,  sson.status

FROM sys.dm_exec_connections conn

INNER JOIN sys.dm_exec_sessions sson

ON conn.session_id = sson.session_id

CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt

ORDER BY conn.session_id