SQL server 开发技巧1

1. How to get the id of current inserted record?

using @@identity keyword. 

E.G.

create table table1 (id int identity(1,1) not null, name nvarchar(64))

go

insert into table1('Hello world')

go

select @@identity

2. How to cleanup SQL server plan cache?

DBCC DROPCLEANBUFFERS -- cleanup all cache data

DBCC FREEPROCCACHE -- cleanup  plan cache

 

3. Can I create an index on the view?

Yes, the notice is the unique clustered index  must be created first. And you should use With SchemaBinding flag when creating.

E.G,

Create View v1 With SchemaBinding As SQL statement

GO

Create Index Unique Clustered idx_1 On [columns...] 

 

4. What's the different between Clustered Index and Index?

The difference is Clustered Index try to store the relation data in the same page on disk as possible.  But Index not. So, almost performance of  clustered index is better than Index, but when you re-create the Index or order by result set, it is low performance.