What Does on [Primary] Mean

What does ON [PRIMARY] mean?

When you create a database in Microsoft SQL Server you can have multiple file groups, where storage is created in multiple places, directories or disks. Each file group can be named. The PRIMARY file group is the default one, which is always created, and so the SQL you've given creates your table ON the PRIMARY file group.

See MSDN for the full syntax.

What is TEXTIMAGE_ON [PRIMARY]?

From the MSDN

TEXTIMAGE_ON { filegroup | "default" }

Indicates that the text, ntext, image, xml, varchar(max),
nvarchar(max), varbinary(max), and CLR user-defined type columns
(including geometry and geography) are stored on the specified
filegroup.

TEXTIMAGE_ON is not allowed if there are no large value columns in the
table. TEXTIMAGE_ON cannot be specified if <partition_scheme> is
specified. If "default" is specified, or if TEXTIMAGE_ON is not
specified at all, the large value columns are stored in the default
filegroup. The storage of any large value column data specified in
CREATE TABLE cannot be subsequently altered.

NOTE: In this context, default is not a keyword. It is an identifier for the default filegroup and must be delimited, as in TEXTIMAGE_ON "default" or TEXTIMAGE_ON [default]. If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting.

SQL: What is does the UNIQUE mean when creating a primary key

The UNIQUE keyword creates a unique constraint on the columns that are mentioned in its argument list (in this case, email). It does not interfere with the primary key. It will enforce unique values on the email column, that is, fail with an exception when a row is about to be INSERTed (or UPDATEd) that would collide with an existing row.

A primary key (by default) implies a unique constraint. So as you designate student_id as your primary key, the RDBMS will also automatically maintain unique values in that column for you.

Further reading: http://www.w3schools.com/sql/sql_unique.asp

Add multiple primary keys to a table

I find one solution.

select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='avgEnt'
and type_desc LIKE '%CONSTRAINT'

Find constraint of table PK_avgent

After drop primary on table

-- drop current primary key constraint
ALTER TABLE dbo.avgEnt
DROP CONSTRAINT PK_avgent;
GO

After add two column

-- create new primary key constraint
ALTER TABLE dbo.avgEnt
ADD CONSTRAINT PK_avgent PRIMARY KEY NONCLUSTERED (SrNo, TrnDate);
GO

it is work form me.

Type of primary key index and what happens on modifying them in MySQL

I'll answer about MySQL's default storage engine, InnoDB. This is the storage engine you should use for all tables in MySQL, unless you have a very specific reason not to.

Answer to Question 1: The primary key is not a sparse index. I know that term to refer to an index that only has values for a subset of rows in the table. I.e. an index with a WHERE clause. But the primary key must account for all rows in the table. It is the column or columns you use if you need to reference any single row uniquely.

Even though the clustered index is stored in order by primary key values, it isn't necessarily stored in that order on disk. Each page in an InnoDB tablespace has a link to the "next" page, which may not be physically located immediately after. It may be much later in the file, or even earlier. The pages may be interspersed with pages for other indexes (even other table if you are using a shared tablespace). Is this what you meant by sparse index?

Answer to Question 2: If you change the column(s) of the table's primary key, the storage of the table must be restructured. InnoDB uses the primary key as the clustered index, which means the rest of the columns are stored along with leaf nodes of the B-tree data structure. Changing the primary key column(s) may change the order of storage, and also the size of each B-tree node (both internal nodes and leaf nodes).

This means while you are restructuring the primary key, your server temporarily needs a lot of extra storage space — up to double the size of the table. Once the restructuring is finished, the old version of the table is automatically dropped.

Clustered primary key insert performance

These clustered indexes are organized into "pages". Pages are linked together in elaborate ways which we application programmers don't have to worry about. Pages often have free space in them. If a new "in the middle" row fits in the appropriate page, the server puts it there. If it doesn't the server performs a so-called "page split" to make room for the new entry. The pages that result from the split will then have more free space in them. Page splitting isn't a terribly expensive operation. But tons and tons of page splits fragment the clustered index and make a table slower to access.

New rows "at the end" generate new pages.

If you want to experience this for yourself, try making and populating table where a guid is the primary key. Pretty much every row you insert goes "in the middle" and you'll get lots of page splits.

Read this for details of index maintenance.

Meaning of Primary Key to Microsoft SQL Server 2008

In general, a KEY is a column (or combination of columns) that uniquely identifies each row in the table. It is possible to have multiple KEYs in a table (for example, you might have a Person table where both the social security number as well as an auto-increasing number are both KEYs).

The database designer chooses one of theses KEYs to be the PRIMARY KEY. Conceptually, it does not matter which KEY is chosen as the PRIMARY KEY. However, since the PRIMARY KEY is usually used to refer to entries in this table from other tables (through FOREIGN KEYs), choosing a good PRIMARY KEY can be relevant w.r.t. (a) performance and (b) maintainability:

(a) Since the primary key will usually be used in JOINs, the index on the primary key (its size, its distribution, ...) is much more relevant to performance than other indexes.

(b) Since the primary key is used as a foreign key in other tables, changing the primary key value is always a hassle, since all the foreign key values in the other tables need to be modified as well.

---

Related Topics