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

 

 

 

 

 

Why codecharge?

Codecharge is a code generating software, that converts any database into web application.

Situation:

-          Without codecharge it may take a lot of time to develop a simple registration program – considering fields to use and validations.

  • Most implemented programs are not in object-oriented
  • Risk for sql injections

 

Benefits:

Reduce Development Time
Eliminate time-consuming programming tasks and build scalable, robust Web Applications in a fraction of the time.

Minimize Errors
Avoid costly programming errors and misspellings by generating consistent, well-structured code.

Reduce Learning Curve
Analyze and modify generated code to learn web technologies and take on programming projects in any environment.

 

CodeCharge allows even the most novice of web developers to create feature-rich, dynamically driven web-based applications, websites, and web portals really really fast!

CodeCharge’s strengths come in the form of:

-          STEP-BY-STEP WIZARDS FOR BUILDING RECORD FORMS, DATA GRIDS, AND REPORTS

-          OBJECT-ORIENTED AND EVENT-DRIVEN PROGRAMMING IN ONE PACKAGE!

-          PRE-BUILT POINT AND CLICK ACTIONS CUT DEVELOPMENT TIME TEN-FOLD

-          SEPARATION OF THE HTML DESIGN LAYER FROM THE CODE LAYER – MVC (Model-Views-Controller)

-          BUILT-IN CONCURRENT VERSIONING SYSTEM (CVS) SUPPORT

 

More info about codecharge:

Official site: http://codecharge.com

Code charge product page (Companies / clients using CodeCharge Studio): http://www.yessoftware.com/products/product.php?product_id=1

CodeCharge Studio Feature List: http://www.yessoftware.com/products/features.php?product_id=1

Rapid development quality by Data X: http://www.datax.co.za/customizedSoftware/rad-codecharge.html

Why codecharge studio?: http://www.codechargesupport.com/why.php

Ultra apps success story: http://www.yessoftware.com/download/files/pdf/ultraapps.pdf