Monday, April 18, 2011
sp_MSforeachtable
You can use the sp_MSforeachtable undocumented system stored procedure to accomplish tasks on all tables of a database such as rebuilding indexes or recomputing statistics.
This script rebuilds all indexes with a fillfactor of 80 in every table in the pubs database:
USE pubs
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
Friday, April 15, 2011
SQL Naming Conventions
Overall conventions
- Keep names short as possible, but long enough to be meaningful
- Reserve ALL CAPS only for SQL keywords such as SELECT, UPDATE, INSERT, WHERE, JOIN, AND, OR, LIKE
- Be generous in use of comments, but not distracting
- Do not use spaces in object names
Tables
- A 1-M or M-M named with the primary table name, an underscore, and the dependent table name (ie: user_permissions)
- A "logging" table can have a suffix of 'log' as in "userlog", whereas a an "archive" table with the same structure as a "live" table can be named with an "_archive" suffix as "user_archive".
- Name a primary key (or in the event of a compound PK create a surrogate identity or guid key) named after the table (ie: users.user_id). Makes it easier to match up foreign keys to primary keys.
- All tables should have some form of primary key, having a surrogate identity or guid key makes it easier for the application to reference specific rows.
- Except for the primary key, don't repeat the table name in the column name (ie: users.username -vs- users.name). This makes for annoying long and redundant column names.
- Table and column names should typically single words, be all lower case, and use underscores sparingly (with the exception of the primary key "_id" column). If a column name is multiple words then run the words together such as "lastname" or if it is an commonly understandable abbreviation "lname". Another possible convention is to begin with lowercase and use title case for remaining words (ie: lastName).
- Avoid using reserved words for table and column name
- For transaction databases (OLTP) target 3rd normal form for normalization. Is best to normalize as far as possible to best support transactions. If you don't need a full blown counterpart OLAP database, then can add some "rollup" or "aggregation" tables to support long running reports.
- Most "entity" and "lookup/list of value" Table names should end in plural 's' (ie: users, accounts, locations) as a table is a collection of items as opposed to an instance of an item.
Constraints/Indexes
- Primary key index should be "PK_tablename"
- Foreign key index should be "FK_dependenttable_ref_primarytable"
- Alternate key indexes should be "AK_tablename" (for surrogate identity or guid field in the event of a compound PK)
- For all other indexes start with "IX_tablename" or "IX_tablename_colname", if unique append a "_u" suffix, if clustered append a "_c" suffix, if clustered-unique append a "_cu" suffix
- Defaults are named "DF_tablename_columname"
Triggers
- Triggers should be used VERY sparingly
- Triggers should be named "TR_tablename" or "TR_tablename_columname", and append a suffix of "_i" for insert, "_u" for update, "_d" for delete or some combination thereof (ie: "_iu", "_iud")
- Perform referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validation that cannot be performed using constraints and would be impractical to enforce in stored procedures.
Other Object Names
- Use "Pascal" notation for objects with a meaningful prefix (ie: vUserDetails, fnUsersGetDetails)
- Recommend prefix of "v" for views
- Recommend prefix of "fn" for functions, or could even go with "ft" for table-valued functions, "fn" for scalar-valued functions, "fa" for aggregate functions
Stored Procedures
- DO NOT use "sp_" as a prefix for stored procedures, unless you are specifically creating a user defined system-wide stored procedure. This is a naming convention reserved for system procedures.
- Use SET NOCOUNT ON at the beginning of stored procedures and triggers
- If you use a prefix for a stored procedures (sp), recommend using something like "p" for process, "r" for report, "u" for utility, etc
- When supporting an application that relies entirely on stored procedures (a highly recommended configuration btw!), it is helpful to incorporate the name of the module into the name and what function the sp suports (ie: pUserGetDetails, pUserSetDetails, rManningStatus)
- Typical action descriptions might include Get, Delete, Update, Write, Archive, Insert, Log along with the primary entity being impacted by the action
- Depending on scale you may choose one of the following conventions to take advantage of the alphabetical sorting in the tree view: uAction
- utility procedures are typically run by database-side administrators or an automated process pModuleAction
- supporting a single application, module could, for example, be the name of the .aspx page the sp supports (ie: pAccountEditGetUser, pAccountEditAddUser, pAccountEditDeleteUser) pApplicationModuleAction
- if your database supports multiple interfaces, then you may want to include the application name
Jobs
- Jobs should be named with project mnemonic name in all caps and brackets and then something indicating in general what it does (ie: "[PROJECT] WeeklyDataPull")
General SQL Tips
- (NOLOCK) query optimizer hint can (and likely should) be used on most all SELECT statements
- In many cases EXCEPT or NOT EXIST clauses can be used more efficiently in place of LEFT JOIN or NOT IN
- In SQL Server Management Studio, turn on the "Include Actual Query Plan" and run the query. The query plan analyzer often suggests indexes.
- Do not call the same functions repeatedly, instead call them once and store the value in a variable
- Always access tables consistently in the same order in all stored procedures and triggers to avoid deadlocks.
- Use unicode data types like nchar, nvarchar, and ntext sparingly as they use twice as much space as non-unicode data types.
- Use a specific size instead of varchar(max) and nvarchar(max), as they are treated as out of row TEXT blobs and not as efficient in searching as using a specific size (except for instances where a text blob is truly appropriate)
- Minimize use of NULL where possible as they require additional complexity in queries to deal with
- Joins and common elements of complex queries that are used in multiple locations should be incorporated into a view.
- Avoid using '*' in queries, always use explicit column names in your permanent code
- Be sure to explicitly DROP #temp tables and DEALLOCATE cursors as soon as they are not needed
- Using Common Table Expressions (CTE) are often (but not always) more efficient than using #temp tables and recursive functions
- Using @table variables for small amounts of data are often (but not always) more efficient than using #temp tables and cursors
- If you do not need a result set for a query, use a constant (ie: IF EXISTS ( SELECT TOP 1 1 FROM users WHERE id = 10 ) )
- Indentation and capitalization is KEY to readability:
- Keep names short as possible, but long enough to be meaningful
- Reserve ALL CAPS only for SQL keywords such as SELECT, UPDATE, INSERT, WHERE, JOIN, AND, OR, LIKE
- Be generous in use of comments, but not distracting
- Do not use spaces in object names
Tables
- A 1-M or M-M named with the primary table name, an underscore, and the dependent table name (ie: user_permissions)
- A "logging" table can have a suffix of 'log' as in "userlog", whereas a an "archive" table with the same structure as a "live" table can be named with an "_archive" suffix as "user_archive".
- Name a primary key (or in the event of a compound PK create a surrogate identity or guid key) named after the table (ie: users.user_id). Makes it easier to match up foreign keys to primary keys.
- All tables should have some form of primary key, having a surrogate identity or guid key makes it easier for the application to reference specific rows.
- Except for the primary key, don't repeat the table name in the column name (ie: users.username -vs- users.name). This makes for annoying long and redundant column names.
- Table and column names should typically single words, be all lower case, and use underscores sparingly (with the exception of the primary key "_id" column). If a column name is multiple words then run the words together such as "lastname" or if it is an commonly understandable abbreviation "lname". Another possible convention is to begin with lowercase and use title case for remaining words (ie: lastName).
- Avoid using reserved words for table and column name
- For transaction databases (OLTP) target 3rd normal form for normalization. Is best to normalize as far as possible to best support transactions. If you don't need a full blown counterpart OLAP database, then can add some "rollup" or "aggregation" tables to support long running reports.
- Most "entity" and "lookup/list of value" Table names should end in plural 's' (ie: users, accounts, locations) as a table is a collection of items as opposed to an instance of an item.
Constraints/Indexes
- Primary key index should be "PK_tablename"
- Foreign key index should be "FK_dependenttable_ref_primarytable"
- Alternate key indexes should be "AK_tablename" (for surrogate identity or guid field in the event of a compound PK)
- For all other indexes start with "IX_tablename" or "IX_tablename_colname", if unique append a "_u" suffix, if clustered append a "_c" suffix, if clustered-unique append a "_cu" suffix
- Defaults are named "DF_tablename_columname"
Triggers
- Triggers should be used VERY sparingly
- Triggers should be named "TR_tablename" or "TR_tablename_columname", and append a suffix of "_i" for insert, "_u" for update, "_d" for delete or some combination thereof (ie: "_iu", "_iud")
- Perform referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validation that cannot be performed using constraints and would be impractical to enforce in stored procedures.
Other Object Names
- Use "Pascal" notation for objects with a meaningful prefix (ie: vUserDetails, fnUsersGetDetails)
- Recommend prefix of "v" for views
- Recommend prefix of "fn" for functions, or could even go with "ft" for table-valued functions, "fn" for scalar-valued functions, "fa" for aggregate functions
Stored Procedures
- DO NOT use "sp_" as a prefix for stored procedures, unless you are specifically creating a user defined system-wide stored procedure. This is a naming convention reserved for system procedures.
- Use SET NOCOUNT ON at the beginning of stored procedures and triggers
- If you use a prefix for a stored procedures (sp), recommend using something like "p" for process, "r" for report, "u" for utility, etc
- When supporting an application that relies entirely on stored procedures (a highly recommended configuration btw!), it is helpful to incorporate the name of the module into the name and what function the sp suports (ie: pUserGetDetails, pUserSetDetails, rManningStatus)
- Typical action descriptions might include Get, Delete, Update, Write, Archive, Insert, Log along with the primary entity being impacted by the action
- Depending on scale you may choose one of the following conventions to take advantage of the alphabetical sorting in the tree view: uAction
- utility procedures are typically run by database-side administrators or an automated process pModuleAction
- supporting a single application, module could, for example, be the name of the .aspx page the sp supports (ie: pAccountEditGetUser, pAccountEditAddUser, pAccountEditDeleteUser) pApplicationModuleAction
- if your database supports multiple interfaces, then you may want to include the application name
Jobs
- Jobs should be named with project mnemonic name in all caps and brackets and then something indicating in general what it does (ie: "[PROJECT] WeeklyDataPull")
General SQL Tips
- (NOLOCK) query optimizer hint can (and likely should) be used on most all SELECT statements
- In many cases EXCEPT or NOT EXIST clauses can be used more efficiently in place of LEFT JOIN or NOT IN
- In SQL Server Management Studio, turn on the "Include Actual Query Plan" and run the query. The query plan analyzer often suggests indexes.
- Do not call the same functions repeatedly, instead call them once and store the value in a variable
- Always access tables consistently in the same order in all stored procedures and triggers to avoid deadlocks.
- Use unicode data types like nchar, nvarchar, and ntext sparingly as they use twice as much space as non-unicode data types.
- Use a specific size instead of varchar(max) and nvarchar(max), as they are treated as out of row TEXT blobs and not as efficient in searching as using a specific size (except for instances where a text blob is truly appropriate)
- Minimize use of NULL where possible as they require additional complexity in queries to deal with
- Joins and common elements of complex queries that are used in multiple locations should be incorporated into a view.
- Avoid using '*' in queries, always use explicit column names in your permanent code
- Be sure to explicitly DROP #temp tables and DEALLOCATE cursors as soon as they are not needed
- Using Common Table Expressions (CTE) are often (but not always) more efficient than using #temp tables and recursive functions
- Using @table variables for small amounts of data are often (but not always) more efficient than using #temp tables and cursors
- If you do not need a result set for a query, use a constant (ie: IF EXISTS ( SELECT TOP 1 1 FROM users WHERE id = 10 ) )
- Indentation and capitalization is KEY to readability:
--
SELECT A.col1
, A.col2
, B.col3
FROM tabname1 A (NOLOCK)
INNER JOIN tabname2 B (NOLOCK)
ON A.col1 = B.col1
WHERE A.col3 = 1
ORDER BY A.col2
Thursday, February 10, 2011
Database Collation Change
Swiped from DrSQL
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1333.entry
use [database]
go
Declare @fromCollation sysname
, @toCollation sysname
SET @fromCollation = 'SQL_Latin1_General_CP1_CI_AS' --or whatever
SET @toCollation = 'SQL_Latin1_General_CP1_CI_AS' --or whatever
SELECT 'ALTER TABLE ' + quotename(TABLE_NAME)
+ ' ALTER COLUMN ' + quotename(COLUMN_NAME) + ' ' + quotename(DATA_TYPE)
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+ ' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND COLLATION_NAME not like @toCollation
Wednesday, July 14, 2010
Example MS Access Datasource
1. Log into Coldfusion Administrator
https://myserver/CFIDE/administrator/index.cfm
2. Expand Data & Services
3. Select Data Sources
4. Enter a value for Data Source Name, select Microsoft Access as Driver, Click [Add] Button
5. Fill in blanks similar to example below:
CF Data Source Name: radteammetrics
Database File: D:\WEB\~~BACKED_UP~~\Development\db\MYADB.mdb
ColdFusion User Name: Admin
Uncheck "Use Default User name"
Leave "System Database File", and other fields not referenced above, blank.
6. Click [Submit]
Monday, March 22, 2010
StopRestart Coldfusion Service
1. Open up the services control panel:
Start -> Programs -> Administrative Tools -> Services
2. Find and entry named something like: Coldfusion Application Server
3. Right-Click, select "Stop"
4. Right-Click, select "Start"
Reset Coldfusion Admin Password
To Reset a Lost Administrator Password:
1. Find the the file "neo-security.xml".
This is in the lib directory of you ColdFusion MX install. For example in Windows it would be something like C:\CFusionMX\lib\ and on UNIX /opt/coldfusionmx/lib
2. Change the admin.security.enabled variable to false.
Before:
<var name='admin.security.enabled'>
<boolean value='true'/>
</var>
After:
<var name='admin.security.enabled'>
<boolean value='false'/>
</var>
3. Restart the ColdFusion process.
4. Access the coldfusion control panel. Depending on how you have it installed, the url should be something like: https://127.0.0.1/CFIDE/administrator/index.cfm
5. Change the administrator password and re-enable security.
5.1. In the control panel, scroll the menu down to and expand "Security"
5.2. Select "CF Admin Password".
5.3. Check "Use a ColdFusion Administrative password"
5.4. Enter new password and confirm
5.5. Click "Submit Changes"
Tuesday, October 6, 2009
Example OAS App deployment instructions
Installation Instructions
Prerequisites:
1. Platform:
Oracle Application Server 10.1.3
Oracle RDBMS 10.2.0.4
Database Deployment
1. Open Database folder on CD and follow instructions:
a. If deploying to Testing Environment read:
\Database\TestingInstructions.txt
b. If deploying to Production Environment read:
\Database\ProductionInstructions.txt
Application Deployment
1. Log in to Oracle Server web interface.
2. If the group "{PROJECT_NAME}_Group" does not exist, create it.
a. Go to Cluster Topology and scroll down to Groups list
b. Create a new group
c. Name the group "{PROJECT_NAME}_Group"
d. Click Create
3. If the OC4J Instance for "{PROJECT_NAME}_Home" does not exist, create it.
a. Go to Cluster Topology and scroll to Members list
b. Click the top level Application Server
c. Click Create OC4J Instance
d. Set OC4J Instance Name: "{PROJECT_NAME}_Home"
e. Add to an existing group "{PROJECT_NAME}_Group"
f. Check the box marked "Start instance after creation"
g. Click Create
h. Ensure the instance has started
4. Create Database Connection
a. Select the OC4J instance "{PROJECT_NAME}_Home"
b. Select Administration tab, then JDBC Resources from list
c. Create Connection Pool
i. Under Connection Pool heading click Create
ii. Select "default" application from drop down
iii. Select New Connection Pool
iv. Click Continue
v. NAME: {PROJECT_NAME}_CONNECTION_POOL
vi. Use default connection factory class
vii. JDBC URL: jdbc:oracle:thin:@//{SERVER_NAME}:1521/{SID}
viii. USERNAME: {PROJECT_NAME}_APPLICATION_USER
ix. PASSWORD: {APPLICATION_USER_PASSWORD}
x. Click Finish
d. Create Data Source
i. Under Data Sources heading click Create
ii. Select default application from drop down
iii. Select Managed Data Source
iv. Click Continue
v. NAME: {PROJECT_NAME}_JNDI_CONNECTION
vi. JNDI LOCATION: java:/comp/env/jdbc/{PROJECT_NAME}Datasource
vii. CONNECTION POOL: {PROJECT_NAME}_CONNECTION_POOL
viii. Click Finish
5. Deploy the application
a. Go to Cluster Topology and scroll down to Groups list
b. Select "{PROJECT_NAME}_Group"
c. Select Applications tab
d. Select Deploy
e. Set Archive Location to path to file on CD at \Deploy\{PROJECT_NAME}_{VERSION}.war
f. Click on next
g. Ensure that Root context and application name fields are the same
h. Click next
i. Select Configure Class Loading (the pencil)
j. Under Import Shared Libraries ensure the following are unchecked:
{Note: list libraries not needed for specific application }
{apache.commons.logging}
{oracle.toplink}
k. Click Ok
l. Click Deploy
6. Configure memory allocation
a. Go to Cluster Topology
b. Select "{PROJECT_NAME}_Home" in the Members list
c. Go to the Administration tab
d. Select Server Properties
e. Set Initial heap size to {MINIMUM_MEMORY}m
f. Set Maximum heap size to {MAXIMUM_MEMORY}m
g. Click Apply
h. Go to Cluster Topology
i. Select checkbox next to "{PROJECT_NAME}_Home" in the Members list
g. Click Restart
7. Test deployment of application and database connection
a. Bring up application login page:
b. Attempt to log in. Contact {poc} for user name and password.
Subscribe to:
Posts (Atom)