Over the years as a DBA i've developed a naming convention for my database objects. It certainly needs a little work but I do think it makes code a little easier to read and debug.
Top Level Objects:
udb_ - User DataBase - A database in Microsoft® SQL Server™ 2000 consists of a collection of tables with data, and other objects, such as views, indexes, stored procedures, and triggers, that are defined to support the activities performed with the data. Before objects within the database can be created, you must create the database and understand how to change the settings and the configuration of the database. This includes tasks such as expanding or shrinking the database, or specifying the files used to create the database.
In Database Objects:
apr_ - APplication Role - A SQL Server role created to support the security needs of an application.
def_ - User DEFault - Defaults specify what values are used in a column if you do not specify a value for the column when inserting a row. Defaults can be anything that evaluates to a constant.
dgm_ - User DiaGraM -
idc_ - InDex Clustered - An index in which the logical order of the key values determines the physical order of the corresponding rows in a table.
idx_ - InDeX - In a relational database, a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The primary key of a table is automatically indexed. In full-text search, a full-text index stores information about significant words and their location within a given column.
rul_ - User RULe - A database object that is bound to columns or user-defined data types, and specifies which data values are acceptable in a column. CHECK constraints provide the same functionality and are preferred because they are in the SQL-92 standard.
tbl_ - User TaBLe - A two-dimensional object, consisting of rows and columns, used to store data in a relational database. Each table stores information about one of the types of objects modeled by the database.
trg_ - User TRiGger - A stored procedure that executes when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.
udf_ - User Defined Function - In SQL Server, a Transact-SQL function defined by a user. Functions encapsulate frequently performed logic in a named entity that can be called by Transact-SQL statements instead of recoding the logic in each statement.
udt_ - User defined Data Type - A data type, based on a SQL Server data type, created by the user for custom data storage. Rules and defaults can be bound to user-defined data types (but not to system data types).
uro_ - User ROle - A SQL Server security account that is a collection of other security accounts that can be treated as a single unit when managing permissions. A role can contain SQL Server logins, other roles, and Windows logins or groups.
usp_ - User Stored Procedure - A precompiled collection of Transact-SQL statements stored under a name and processed as a unit. SQL Server supplies stored procedures for managing SQL Server and displaying information about databases and users. SQL Server-supplied stored procedures are called system stored procedures.
Data Types:
bin_ - BINary - Fixed-length data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.
bit_ - BIT - Integer data type 1, 0, or NULL.
chn_ - CHar uNsigned - Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character.
chr_ - CHaR - Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
dec_ - DECimal - Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).
dtm_ - DateTiMe - Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).
dts_ - DateTime Small - Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.
flt_ - FLoaT - Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
img_ - IMaGe - Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
inb_ - INt Big - Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.
ins_ - INt Small - Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.
int_ - INT - Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.
iny_ - INt tinY - Integer data from 0 through 255. Storage size is 1 byte.
mns_ - MoNey Small - Monetary data values from - 214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes.
mny_ - MoNeY - Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.
num_ - NUMeric - Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).
rel_ - REaL - Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
tst_ - TimeSTamp - timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
txn_ - TeXt uNsigned - Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text.
txt_ - TeXT - Variable-length non-Unicode data in the code page of the server and with a maximum length of 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
uid_ - Unique IDentifier - A globally unique identifier (GUID).
var_ - VARiant - A data type that stores values of various SQL Server-supported data types, except text, ntext, image, timestamp, and sql_variant. sql_variant may be used in columns, parameters, variables, and return values of user-defined functions. sql_variant allows these database objects to support values of other data types.
vby_ - VarBinarY - Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for varbinary is binary varying.
vch_ - VarCHar - Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
vcn_ - VarChar uNsigned - Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
Other Objects:
cur_ - CURsor - An entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. The most common operation is to fetch (retrieve) the current row or block of rows.
lsv_ - Linked SerVer - A definition of an OLE DB data source used by SQL Server 2000 distributed queries. The linked server definition specifies the OLE DB provider required to access the data, and includes enough addressing information for the OLE DB provider to connect to the data. Any rowsets exposed by the OLE DB data source can then be referenced as tables, called linked tables, in SQL Server 2000 distributed queries.
dts_ - Data Transformation Services package - An organized collection of connections, Data Transformation Services (DTS) tasks, DTS transformations, and workflow constraints defined by the DTS object model and assembled either with a DTS tool or programmatically.
job_ - sql server agent JOB - A specified series of operations, called steps, performed sequentially by SQL Server Agent.
*Object definitions copied from SQL 2000 books online ©1988-2000 Microsoft Corporation. All Rights Reserved.
No comments:
Post a Comment