How To Reset Auto-Increment in MS SQL Server

After deleting all the entries in the table. The increment will not reset by it self. To reset the auto-Increment to 1, use the SQL Query below: DBCC CHECKIDENT ([Table Name], RESEED, 1)

Create a Basic SQL Database Objects Using the Transact-SQL Statement

The first database object that has to be created is a database itself. Database Engine manages both system and user databases. An authorized user can create user databases, while system databases are generated during the installation of the database system. System databases are: master, tempdb, model, msdb, and resource. Two basic methods are used to create a database. The first method involves using Object Explorer in SQL Server Management Studio. The second method involves using the Transact-SQL statement CREATE DATABASE. This statement has the following general form: CREATE DATABASE db_name [ON [PRIMARY] { file_spec1} ,...] [LOG ON {file_spec2} ,...] [COLLATE collation_name] [FOR {ATTACH | ATTACH_REBUILD_LOG }] db_name is the name of the database. The maximum size of a database name is 128 characters. The maximum number of databases managed by a single system is 32,767. All databases are stored in files. These files can be explicitly specified by the system administrator or implicitly provided by the system. If the ON option exists in the CREATE DATABASE statement, all files containing the data of a database are explicitly specified. file_spec1 represents a file specification, which includes further options such as the logical name of the file, the physical name, and the size. The PRIMARY option specifies the first (and most important) file that contains system tables and other important internal information concerning the database. If the PRIMARY option is omitted, the first file listed in the specification is used as the primary file. A login account of Database Engine that is used to create a database is called a database owner. A database can have one owner, who always corresponds to a login account name. The login account, which is the database owner, has the special name dbo. This name is always used in relation to a database it owns. dbo uses the LOG ON option to define one or more files as the physical destination of the transaction log of the database. If the LOG ON option is not specified, the transaction log of the database will still be created because every database must have at least one transaction log file.  With the COLLATE option, you can specify the default collation for the database. If the COLLATE option is not specified, the database is assigned the default collation of the model database, which is the same as the default collation of the database system. The FOR ATTACH option specifies that the database is created by attaching an existing set of operating system files. If this option is used, you have to explicitly specify the first primary file. The FOR ATTACH_REBUILD_LOG option specifies that the database is created by attaching an existing set of operating system files.  During the creation of a new database, Database Engine uses the model database as a template. The properties of the model database can be changed to suit the personal conception of the system administrator. Example: USE master; CREATE DATABASE PhiBui; An example above creates a database named PhiBui for master database. This concise form of the CREATE DATABASE statement is possible, because almost all options of that statement have default values. The system creates by default, two files. The logical name of the data file is PhiBui and its original size is 2MB. Similarly, the logical name of the transaction log is PhiBui_log and its original size is 1MB. Reference: http://msdn.microsoft.com/en-us/library/84b1se47.aspx

How to create a basic SQL table?

The CREATE TABLE statement creates a new base table with all corresponding columns and their data types. The basic form of the CREATE TABLE statement is: CREATE TABLE table_name (col_name1 type1 [NOT NULL| NULL] [{, col_name2 type2 [NOT NULL| NULL]} …]) table_name is the name of the created base table. The maximum number of tables per database is limited by the number of objects in the database (there can be more than 2 billion objects in a database, including tables, views, stored procedures, triggers, and constraints). col_name1, col_name2,… are the names of the table columns. type1, type2,… are data types of corresponding columns. If NOT NULL is specified, the assignment of NULL values for the column is not allowed. (In that case, the column may not contain nulls, and if there is a NULL value to be inserted, the system returns an error message.) As already stated, a database object (in this case, a table) is always created within a schema of a database. A user can create a table only in a schema for which she has ALTER permissions. Any user in the sysadmin, db_ddladmin, or db_owner role can create a table in any schema. The creator of a table must not be its owner. This means that you can create a table that belongs to someone else. Similarly, a table created with the CREATE TABLE statement must not belong to the current database if some other (existing) database name, together with the schema name, is specified as the prefix of the table name. Example SQL Query: USE PhiBui; CREATE TABLE employee (emp_no INTEGER NOT NULL, emp_fname CHAR(20) NOT NULL, emp_lname CHAR(20) NOT NULL, dept_no CHAR(4) NULL); CREATE TABLE department(dept_no CHAR(4) NOT NULL, dept_name CHAR(25) NOT NULL, location CHAR(30) NULL); CREATE TABLE project (project_no CHAR(4) NOT NULL, project_name CHAR(15) NOT NULL, budget FLOAT NULL); CREATE TABLE works_on (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL, job CHAR (15) NULL, enter_date DATE NULL); The DEFAULT clause in the column definition specifies the default value of the column that is, whenever a new row is inserted into the table, the default value for the particular column will be used if there is no value specified for it. A constant value, such as the system functions USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP, and NULL, among others, can be used as the default values. A column with the IDENTITY property allows only integer values, which are usually implicitly assigned by the system. Each value, which should be inserted in the column, is calculated by incrementing the last inserted value of the column. Therefore, the definition of a column with the IDENTITY property contains (implicitly or explicitly) an initial value and an increment. Learn more: http://www.1keydata.com/sql/sqlcreate.html


<<  July 2021  >>

View posts in large calendar