One of variants of the agreement on names of objects MS SQL Server

Remember, that names of objects sql the server cannot exceed 30 symbols. Do not use the reserved words, special symbols, and do not begin names of objects with figure. Avoid use of reductions.

database devices: devices of a database should be called on a problem{task} and the purpose which they solve. If it - log device, the suffix "log" should be added in the end of a name. The name of the device for a data storage should terminate on "data". If the device sozdae:tsja for one database, in his{its} name is included a name of a database. Names of devices of a database should be typed{collected} by capital letters.

Example: customerlog, master, customerdata.

backup devices: devices of reservation should be named as this device. Names of devices of reservation should be typed{collected} in the bottom register.

Example: tapebackup, dbdiskdump

databases: the user databases are typed{collected} in the top register, for difference from standard (the bottom register).

Example: customer, accounting, master

logins: Names uche:tnykh recordings are under construction of the first symbol of a login name and a surname completely, priche:m typed in the bottom register.

Example: mhotek, swynkoop, dbadmin

tables: Names of tables should describe their purpose and are typed{collected} together in the bottom register. The prefix " zlk _ " should precede search tables. The symbol "z" allows to group these tables in the bottom of the list (in alphabetic order), and symbols " lk _ " (lookup) define{determine}, what is it the search or auxiliary table.

Example: customeraddress, customer, peoplemailinglist, zlk_countrycodes

columns: fields are called according to their purpose. The first symbol of a name should be typed by capital letters. The compound name is written together. After a name addition through a symbol podche:rkivanija the special prefix carrying the given field to a certain group or the module is possible. Groups or modules are defined{determined} by the technological documentation of the developer of a database.

Example: employee_id, firstname, lastname, velocity_mpm, mass_kg

indexes: the name of an index begins with a name of the table which is typed{collected} in the bottom register. The purpose of an index defines{determines} his{its} suffix. " primary key " should have a suffix "_pk." " foreign key " should have a suffix "_fkx", where x - a serial number. " clustered indexe " should have a suffix "_idx". All other indexes should have a suffix "_ndxx", where x - number{room} under the order. In a name one suffix should be used only. The choice of a suffix is defined{determined} by the following hierarchy: " clustered indexe ", " primary key ", " foreign key ", other indexes.

For example, the index which is " primary key " and " clustered indexe ", will have a suffix _idx.

Example: employee_pk, customer_idx, employee_fk1, employee_ndx1, employee_ndx2

triggers: the name of the trigger should is under construction of a name of his{its} table and a prefix of type. Vse: it is typed{collected} in the bottom register. The first symbol of a prefix of the trigger should be "t". Further in a prefix the symbol of type, a symbol of underlining{emphasis}, and a name of the table follows. The symbol of type can accept the following values: i = insert, u = update, d = delete.

Example: ti_employee, tiu_employee, td_employee, tid_customer

defaults: names of default values should begin with a prefix " d _ " after which the brief description of his{its} purpose follows. Vse: it is typed{collected} together and in the bottom register.

Example: d_zerovalue, d_username

rules: the name of a rule should begin with a prefix " r _ " after which the brief description of his{its} purpose follows. Vse: it is typed{collected} together and in the bottom register.

Example: r_numberlessthan10

user defined datatypes: the name of the user type given to begin with a prefix " ud _ " after which the brief description of follows that this type standardizes. Vse: it is typed{collected} together and in the bottom register.

Example: ud_city, ud_autoincrement

stored procedures: names of stored{kept} procedures of a system level to begin with a prefix " sp __ " (two symbols of underlining{emphasis}) after which the brief description of follows that this procedure does{makes}. All names of applied stored{kept} procedures follow after a prefix " xsp __ " (where kh - a descriptor which separates stored{kept} procedures of one application from another) after which the brief description of follows that this procedure does{makes}. Vse: it is typed{collected} together and in the bottom register.

Example: sp __ loaddata, csp_checktime, asp_employeetimereport

remote procedure calls: names of tasks of remote control to begin with a prefix " rpc _ " after which the brief description of follows that this rpc does{makes}. Vse: it is typed{collected} together and in the bottom register.

Example: rpc_getnextvalue

alerts: names of preventions{warnings} should begin with a descriptor of a database to which the information concerns, the level of this prevention{warning} further follows.

Example: customer fatal errors

tasks: names of problems{tasks} are under construction by a rule: the description of object of a problem{task}, periodicity of performance, a level of a problem{task}, sense of the given action.

The brief review of the some people vozmozhnostnej MySQL. Advice{councils} on optimization.

1) In all versions 3.khkh reference integrity and the enclosed searches (as it is the bypass-recipe{do without-recipe} 6) is not supported. On a primary key mysql automatically creates an index.

For other attributes it is possible to create additional indexes. The index is created together with the table-

create table [imja_tablicy] (..., index|key [imja_indeksa] (attribut1...))

And service fields key and index - synonyms. Since version 3.22 the index can be created and separately-

create index [imja_indeksa] on [imja_tablicy] (attribut1...).

Usually additional indexes, are created for acceleration of process of sample of a database. At his{its} creation the volume of the table increases, therefore if high-speed parameters of you arrange, an index to create it is not necessary. If speed nepriemlima, an index is added for attributes specified in search after a design where.

(the Example:

select uid, data, comments from test_table where uid = 90 and data = ' test ';

create index ind1 on test_table (data))

2). If from sample it is necessary to take some lines in a design select it is underlined limit x, y.

x-displacement,

The $y-as much as possible number of lines

The parameter is not obligatory.

Thus on vypolneie the reduced search leaves less time.

(the Example:

select * from table where d> 900 limit 100)

3). Sometimes it is required to save result of sample in a file. It is done{made} as follows-

select... into outfile '/path/to/dir/filename '.

The file should not exist.

Return operation of loading from a file in the table-

load data infile '/path/to/dir/filename ' into table table. Lines of a file should have a format of a kind (pole1tpole2t.., where t - a symbol of tabulation). More complex{difficult} format of the command in given clause{article} will not be considered{examined}.

Last variant can be applied and in case of increase of speed of loading of the data in the table. We shall assume, your program consistently carries out an insert of a plenty of lines in the table. This operation can borrow{occupy} some minutes, depending on volume of the data and quantity{amount} of indexes. With the help load data the author managed to raise{increase} speed of an insert in 7 times!

4). At often operations of an insert, removal{distance} and updating of the data of the table (with types - varchar, blob, text), there is a defragmentation. That her{it} to reduce there is an operation optimize table [imja_tablicy].

5). A convenient opportunity mysql is avto increase in value of a field. Creation of such field - create table.. pole int (7) auto_increment.. And this field should be declared, as a primary key and therefore in the table it should be the only thing.

At an insert in the table it is possible to not specify a name of such field. Operation auto increment in mysql is simplified. For example in oracle for this purpose it is necessary to create separately, so-called it is consecutive (sequence). And at an insert of her{it} obviously to specify.

Sometimes it happens it is necessary at addition of the data in tables to know values of a field with auto_increment for inserted lines, and superfluous searches too slow down job. For this purpose there is a function last_insert_id ().

create table table1 (id auto_increment, comm varchar (20), primary key (id));

insert into table1 (comm) values (' test ');

insert into table2 (id_table1) values (last_insert_id ());

Or, if you write on perl it is possible to use values of object.

$dbh-> {'mysql_insertid '};

6). In versions 3.xx the enclosed searches are not supported. But instead of them it is possible to use time tables. An example of creation of such table:

create temporary table table select...

And then to do{make} sample of it{her} with an additional condition. At a spelling of the interface to mysql on perl, at creation of time tables by the above described way, it is more preferable to use a method ' do ' instead of pair ' prepare-execute ' as this function works faster.

7). Some ways of storage of backup copies of tables or the data.

a) Reserve copying the data can be made with the help of the cgi-scripts written on shell. The manager with the certain periodicity starts the given script, the data are saved in a file. If a file already suhhestvet at start of a script the information in him is updated. The script can be started cron'om then echo commands need to be cleaned{removed}.

*!/bin/sh

db=test

user=user

password=pass

host=host.ru

export db user password host

echo " content-type: text/plain "

echo " "

cd/usr/local/mysql/bin

./mysqldump-h$host-u$user-p$password-t $db> db

echo "ok"

The file (db) is intstrukcii insert... The key-t specifies to not copy the information on tables - create table... Other script starts sql-commands from a file.

*!/bin/sh

db=test

user=user

password=pass

host=host.ru

export db user password host

cd/usr/local/mysql/bin

./mysql-d $db-h$host-u$user-p$password

For increase of reliability this file can be downloaded on ftp. One of convenience of the given method is the opportunity portirovanija the data in any SUBD.

b) Since version 3.23.25 the opportunity of reserve copying of tables (without indexes) has appeared.

backup table [name] to '/dir ';

And restoration

restore table [name] from '/dir ';

v) Still an ode an opportunity is, an above mentioned way of recording of result of search in a file.

select... into outfile '/dir/file ';

And the subsequent restoration from a file.

load data...

8). Has pleasantly surprised an opportunity text-through ideksirovanija fields varchar and text (since version 3.23.23). In search ukazyvaetja podstroka and as a result those values of fields are deduced where she (podstroka) is found. For example you store{keep} in a DB of clause{article} or announcements of clauses{articles} and want to give search under clauses{articles} to the user. It is possible to establish certainly poiskovik (an example yandex.site), but for this purpose it is necessary to get{start} a separate DB and if requirements to the search engine are minimal, the given installation simply an excess.

mysql gives an opportunity of text-through search. I shall illustrate on an example:

create table data (did int (7) auto_increment, a text, fulltext (a), primary key (did));

insert into data (a) values (' test message '), (' test mysql fulltext '), (' test test test ');

select * from data where match (a) against (' mysql ');

In result the line = ' test mysql fulltext ' will be deduced{removed}. And on search

select data, match (a) against (' mysql ') as m from data;

The field m will contain numerical value. As them to use I leave on your discretion.

Thus the elementary search is organized.

9). Some advice{councils} on optimization:

* After filling with the data of the table, execute the command ' isamchk-a 'or' myisamchk-a ', depending on type of the table. This command will collect statistics under tables which can use optimizator.

* Remove unnecessary brackets in search.

* Always, where it probably use where instead of having

* Property of attribute not null saves 1bit and accelerates searches.

* Ispol`ehovanie such as char instead of varchar, will allow to speed up sample on these fields on 10-100 %. It is expedient to do{make} it if the quantity{amount} of symbols of a field is fixed, priehtom it accepts various values. In case of variable length, it is necessary to pay off with disk space. In general fields of the fixed length are processed faster. The choice of this or that type depends on a decided{solved} problem{task} and requirements to a DB.

* If samples of several tables it is slowed too down, and dopolnitel`ye indexes are to be created no, break one big search on some. An example from a life: sample of 3 tables, with "complex{difficult}" conditions last 4min.!!!. And all tables had a primary key. Having broken one search on 2 it was possible to speed up sample on 3min. 54sek. But the problem{task} of optimization is individual and depends on structure of a DB and the given method probably approaches not for all DB. So for own base it is necessary to carry out researches.

10). One of ways of a data structure is definition in one table and primary and external keys. I collided{faced} with such table, the truth in oracle. It byvet convenient in a case when, "podchinnennaja" the line has the same structure, we shall say hierarchy of departments and posts in the organization. For extraction of all hierarchy of the set object, it is necessary to build rekursiju, unfortunately in mysql there is no design - connect by prior.

For achievement of necessary productivity redundancy can help. There is some rule - in the master the table (the table with primary kljuchem) will be worn out last value of object. For example for the table of sellers, it can be last carried out transaction.

Some reasons which can it is useful at designing a DB in mysql. On segodnjashij day she has received the greatest distribution in internet, first of all due to the simplicity and minimal requirements to system resources. Certainly she cannot compete to "monsters" SUBD, such as, for example oracle and for large projects of her{it} will be not enough.

But on mysql completely other problems{tasks} with which, in my opinion, she fine consults are assigned.

How to force ORACLE to analyze all tables?

Certainly, it is possible to use dbms_sql, dbms_job...

And it is possible and so:

*!/bin/sh

*

* analyze all tables

*

sqlfile =/tmp/analyze.sql

logfile =/tmp/analyze.log

echo @connect dbo/passwd> $sqlfile

$oracle_home/bin/svrmgrl <> $sqlfile

connect dbo/passwd

select ' table ', table_name from all_tables where owner = ' dbo ';

eof

echo exit>> $sqlfile

cat $sqlfile> $logfile

cat $sqlfile | $oracle_home/bin/svrmgrl>> $logfile

cat $logfile |/usr/bin/mailx-s ' analyze tables' tlk@nbd.kis.ru

rm $sqlfile

rm $logfile