SQL Server 2008 Implementation and Maintenance (3)

Schema , Table , View and Index

1.exec sp_spaceused 'tableName' -- show the disk space which data occupied

2. rename

sp_rename 'oldTableName' , 'NewTableName' ;

3. view all indexes

select * from sys.indexes ;

4. sp_who -- view all user session

SQL Server 2008 Implementation and Maintenance (2)

SQL SERVER 2008 T-SQL Management Commands(system procedures)

1. sp_help -- show all sys proc names , object type , owner name ...

2.select name from sys.databases ; -- show all databases in one instance

3.sp_helpdb 'database name' ; -- show the information and properties of a database

4.sp_dboption ; -- settable database options == alter database clause

5. function DB_Id("database Name") -- return database ID

6. Fulltext Search

select * from sys.fulltext_languages -- show the languages which are supported

select * from sys.fulltext_document_types -- show document format supported

7. Security

select * from sys.fn_builtin_permissions(default) ; ( view all permissions which can be granted for objects)

e.g. select * from sys.fn_builtin_permissions('login') (or ' database' )

select has_perms_by_name( 'databasename' , 'database ' , 'any' ) ; -- to view if the current user is granted any permission of using database )

execute as user='someone'

go

select has_perms_by_name('databaseName' ,'database' , 'any') ;

go

( check user someone has permissions )

Visual Studio 2005/2008/2010 Notes

1. VS 2008 does not support connecting to SQL Server 2008 initially .

VS 2008 Service pack 1 is needed .

Windows Server 2003 Notes

In win server 03 , some functions and services are not available after the system is installed, such as Internet Access , remote desktop , telnet and so on.

1. startup Telnet service( must be running Windows Server 2003 SP1. )

run-> cmd -> input : tlntsvr /service ( to install telnet service utility)

to do some settings: go->start -> administrative tools -> services -> found Telnet

( startup : automatic ; start service ; input telnet username and password ; OK)

SQL Server 2008 Implementation and Maintenance (1)

1. Command-line utilities

Windows PowerShell -> SQL Server PowerShell

( Running cmdlet commands)

c:\ sqlps

PS SQLSERVER:\ get-command ( view all commands under the shell)

/*******************************************************/

use Tab key to input the complete command automatically

/*******************************************************/

PS SQLSERVER:\ get-help cmdName -detailed ( view the information about every command)

e.g. PS SQLSERVER:\get-help get-host -detailed

PS SQLSERVER:\get-help sqlserver | more ( view sql server information , more is UNIX-like command . Pipeline process is used , so that you view information page by page)

2. use stored procedure to configure SSMS(Master database)

sp_configure -> to config standard configuration parameters and advanced ones.

e.g. :

use master

go

exec sp_configure 'show advanced options' , 1 -- so that you can set advanced system parameters)

go

reconfigure

go

exec sp_configure -- to view the information of all parameters

3. View the information of database in a instance of SQL Server 08

Select name , database_id from sys.databases ;

ADO.Net 2.0 Notes(part four)

Using Class DataSet to process data from database( offline data)

1. DataSet contains Class DataTable and Class DataRelation.

DataSet object can include more than one table . There is one relationship at least if two tables are contained in DataSet object .

2. DataTable contains DataRow , DataColumn and Constraint.

ADO.Net 2.0 Notes(part three)

Query of Database with SqlCommand class and SqlDataReader Class

1. create command

a. SqlCommand cmd = new SqlCommand()

cmd.Connection = cn; // cn is the instance of SqlConnection

cmd.CommandText = "SQL ... " ;

b. SqlCommand cmd ;

cmd = cn.createCommand() ;

cmd.CommandText = "SQL ..." ;

c. SqlCommand cmd = new SqlCommand( SQL , cn ) ;

2. SqlDataReader reader = cmd.ExecuteReader() ; ( cmd is the instance of SqlCommand class)

reader.read() ;

ADO.Net 2.0 Notes(part two)

Connection of database

SqlConnection class

-> connection string -> ConnectionStringBuilder class

1. connection pool ( about connection close() issues)

2. SqlConnection class -> create SqlCommand instance ( class SqlCommand)

SqlConnection class -> SqlTransaction

SqlConnection class -> getSchema information ( about architecture of the database )

e.g. cn.GetSchema()

2. methods of Class SqlConnection

BeginTransaction() ;

ChangeDatabase() ; --> like " use databasename" in SSMS

ADO.Net 2.0 Notes(part one)

the whole view:( Only for SQL Server 200X)

Section A :( Request of Connection )

1. Connection class ( SqlConnection, used to connect with database or data source)

Before connection , connection string is needed.

Class ConnectionStringBuilder -> connection string -> Class SqlConnection

e.g.

ConnectionStringBuilder strcon = new ConnectionStringBuilder() ;

strcon.DataSource = ...

strcon.IntegratedSecurity=True;

strcon.InitialCatalog= ...

SqlConnection cn = new SqlConnection( strcon ) ;

cn.open() ; ( or using(sqlConnection cn = new SqlConnection(strcon)){} )

...

cn.close() ;

==============

SqlConnection Class

1. ConnectionStringBuilder (create connectionString)

2. SqlTransaction Class

3. SqlDataAdapter Class

4. SqlCommand Class

===============

||

||

\/

2. Database Query( SqlCommand class and SqlDataReader , to query database or data source)

===================

SqlCommand class :

1. SqlParameter

2.SqlDataReader

===================

||

||

\/

3. Class SqlDataAdapter ( a bridge of online data and offline data )

connection -> SqlCommand->SqlDataReader ( an active connection with database )

/\

||

Class SqlDataAdapter

||

\/

Class DataSet ( Offline data manipulation )

Section B:( Offline data manipulation and processing)

1. Class DataSet

A=> Class DataTable(Class DataView) B=> Class DataColumn (Class DataRowView)

B=> Class DataRow

B=> Class Constraint

A=> Class DataRelation

SQL Server 2008 internals notes (CHAPTER ONE)

1. select serverproperty( 'Edition') ------------ e.g. Enterprise Edition ( or others)

select serverproperty('EngineEdition') ---------- e.g. 3 ( means edtion is either Enterprise , enterprise Evaluation or Developer. These three have exactly the same features and functionality.

2. Exec SP_help ( to view all system base tables with a system administrator privilege)

3. execute sys.objects to see the names of all system tables

use master ;

select name from sys.objects where type_desc = 'System_table' ;

4. Catalog Views in SQL Server 2008

( all are in sys schema)

sys.bojects , sys.database , sys.indexes , sys.databases

5. View the description of a table ( columns)

to use stored procedure (system procedures) sp_columns

e.g. exec sp_columns @table_name=N'tablename' , @table_owner=N'tableschema' ;

6. We can use function object_definition(object_id( 'some_object') or system procedure sp_helptext 'some_object' to view the definitions of views , procedures , triggers and user-defined functions(UDFs)

e.g. select object_definition(object_id('some_object')) ; --- unavailable for table definitions

exec sp_helptext 'some_object'

7. View the schema of a database in one of instances in SQL 2008

checking Information_schema

e.g. select table_name from information_schema.tables ; ( all tables and views returned)

select table_name from information_schema.tables where table_type='Base Table'; ( only tables returned)

8. View Server Version -> select @@version ;

In ADO.net , Sqlconnection cn = ... ; cn.ServerVersion;(property)