SQLServer refresh

T-SQL With Check view option

it's used to prevent the data from being modified in such a way that it falls out of the view. simply, it forbids update on the column used in the view's where clause.

Table Relationship

one-to-one,one-to-many,many-to-many

for one to one, they can always be merged into one table. they are divided into two or more tables under special concern such as efficient storage,security and temporary data management. to implement this relationship, make PK a FK to each other.

for one to many relationship, it's usually implemented by FK and PK.

For many-to-many relationship, no such constrain can be defined. they are implemented by defining a junction table.

When two tables need a many-to-many relationship, create a third table, also know as junction table. this junction table will have a composite PK created from PK from each of the two tables.

Normalization

the process of ensuring that your database complies with normal forms is referred to as normalizing a database.

1st normal form
1NF requires that all column values in a table be divided to their smallest element. on other wards, all non-pk fields are atomic(can not be divided further). additionally, it should have no repeating groups.

2NF applies to table that have composite PKs. a table is said to be in 2NF if it is in 1NF and every non-pk attribute is completely dependent on the whole pk. in other words, if an attribute within the table is dependent on only one column of the composite pk then it is not 2NF.

3NF means fields within the table are only dependent on pk and not dependent on non-pk fields. to ensure that a table complies with 3NF, review each column and ask "is this column dependent on the PK?"

De-normalization

do it more for better performance.

online transaction processing OLTP databases are mostly normalized because they are focused on inserts and modifications, which perform better in a normalized db.

online analytic processing OLAP databases are commonly normalized because they are focused on querying, which perform better in denormalized db.

Generalization

similar to concept in OOD, is a process of combing similar entities together to form a single entity. e.g. all kind of people can be organized in contact table.

Order By

used to guarantee the order of your result set. columns in order by clause don't have to be in the select column list. the exception is if we use distinct clause to eliminate duplicate rows. in this case, the column in order by clause must be in the select column list.

Aggregation Function

NULL value will not be counted in the calculation of aggregation function.
eg. count(*) will count all the rows. count(column) will only count on column with not null values.

Identity column

you can define only one identity column in a table. the position of it can be any where. it's not necessarily put it in the left most or right most of the column list. while insert statement has column list missed, sql server can figure out if there's a value provided for identity column automatically.

be aware that the identity is similar to sequence in Oracle, even an insert statement fails execution, as long as this is not identity column related problemm, the identity will still be increased 1 step forward, and that is irreversible.

N in Front of String

it means the following string is in uni-code

Coalesce vs case statement

Coalesce choose the first non-null value of the list. this is similar with case statement, which choose the first satisfied branch to use.

Case. when..else..End statement is fully fledged statement that evaluate express as condition. Coalesce is much simpler and only tells if the value is null.

Len
Be careful that len function does not count trailing blanks

Tracing the execution time
here's an example code I can use in everyday life while it comes to performance turning.

declare @start datetime,@end datetime
set @start=getdate()
.....do something
set @end=getdate()
print 'time:'+str(datediff(ms,@start,@end))+' ms'

Remove vs Alter
The reason why you do alter instead remove statement is to preserve the permissions on the original object.

Select record with max xxx
use exists that basically makes an inner join to itself.
select * from tbl a where not exists(select 1 from tbl b where a.increamentalCol <>