SQL Server has 3 types of User defined functions
- Scalar functions
- Inline table-valued functions
- Multistatement table-valued functions
SQL Server has 3 types of User defined functions
View is the virtual table which is defined by the query.
The select statement can not include ODER BY clause, unless we specify the TOP or FOR XML. otherwise the CREATE VIEW will fail with
Msg 1033, Level 15, State 1, Procedure vHRdepartment, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
From SQL Server 2008, the fulltext index is always enable on the user database and can not be disable. However, to make it effective, we must create the fulltext index.
Also, the MSSQLFDLauncher service must be installed on the host. The service is instance specific. The actual executable is fdlauncher.exe.
We can user SERVERPROPERTY to determine whether the full text search feature is install for the SQL Server.
TVF full name is User-defined functions that return a table data. It is also alternative of the view. While views are limited to a single SELECT statement,TVF can contain additional statements that allow more powerful logic than is possible in views.
Create View sample Code
Table Value Function
We can use the tablesample to limit the return result sets. The difference using tablesample and top is top would always retrieve top N rows but tablesample would get the sampling of the data.
TABLESAMPLE would go to each 8K page and get the sample data. That being said if the table has 20 pages but only one page has all table’s data. It is possible it wont get the data back.
Limiting Result Sets by Using TABLESAMPLE http://msdn.microsoft.com/en-us/library/ms189108.aspx
Recently, I come across the need for simulate the user browser web behavior. I need to simulate the user behavior by going to particular URL, filling the form and submit the request and even download the file.
There defiantly will be more then one way can do it. I intend to focus on the solution which can use the language I familiar with: Perl and PowerShell.
Perl: LWP::UserAgent seems to be the way to go. This seems to be able to send the HTTP POST request. During my very short period time of testing. It does not work for me. Probably is because I do not have the proxy server set up. ( My environment has some proxy server/firewall restriction).
Since SQL Server 2005 release, MSFT has discontinue the traditional system tables such as sysdatabase which original inherit from Sybase. It is being replace by the System view. The document here illustrate the map between the old style system table and new system view.
Along with the system view changes, Microsoft also said, there will be a new hidden database being ship with the SQL Server. Of course, for some of us, this statement is not enough to satisfy us. We like to poke around to see what it is. The initial default databases upon SQL server installation are Master, msdb, tempdb, model and resource database.
Resource database is used to store the system objects. Whenever, SQL server upgrade, apply fix pack, service pack, it will upgrade the resource database. Some says, we can roll back the fix pack by replacing the resource database file using the older fix pack upgrade. However, I personally have not try it it myself and not found much more information from internet if anyone have try it.
This resource database can not be view from SSMS, sp_helpdb and even the sys.databases has been explicitly design to exclude this.
There are few ways we can get our hands on it.
One of common technical to trouble shooting the permission issue is looking up the query plan. As a system DBA, I may want to give my developer permission to check the query plan instead of doing every thing for them. If they do not possess proper permission, they may get the 262 error.
The simple grant statement can fix the issue.
After SQL Server installation, MSSQL create below 5 endpoints by default.
These endpoints is bases on TDS (Tabular Data Stream). It is an application layer protocol, used to transfer data between a database server and a client. It was initially designed and developed by Sybase. When MSFT adapt the Sybase server code. It is been port over to MS SQL Server. Since then MSFT has made many changes on it. You can also see other open source implementation base on the TDS spec such as FreeTDS and jTDS.
TDE provide the encryption on the database and log file itself. Even other get hands on your database file or backup file. Without the key, they still can not get the information store in the database.
Upon enable the TDE, the SQL server engine performs real-time I/O encryption and decryption of the data and log files. The application does not require changes.
MSFT does not ship the store procedure to move the logins from one SQL Server instance to another. However, the steps has been describe in the KB 918992 here http://support.microsoft.com/kb/918992.
The store procedure is call sp_help_revlogin.
The steps describes in the KB article will create 2 store procedure. sp_hexadecimal and sp_help_revlogin.
sp_hexadecimal is used to convert numeric value to hexadecimal. It is called inside sp_help_revlogin.
We can find out Backup & Restore History from below 2 tables in MSDB.
Below Query list all the database backup history
As big as it needs to be. Here are some guild line:
Due to the number of VLF could affect the performance, we should set up the initial log size as reasonable size and set the extend size in the fix number so each VLF would be in the difference size.
For example, set the initial size as 4GB ( 256MB per VLF) and extend another 4GB after that .
To see the log file space, we can use DBCC SQLPERF. example as below
Or you can use below query to check the space usage for single database
Database compression is the SQL Server 2008 Enterprise ONLY features. MSSQL offers 2 type of the data compression. Row compression and Page compression.
To estimate how much space you can save by running sp_estimate_data_compression_savings. The store procedure takes 5 parameters: Schema, object name, index name, partition name, compression type( Null, Page or row)
Ranking function syntax are similar for all 4 different type of ranking functions.
Partition: use for group the result set.
Prepare the sample database for testing.
In real world, you would like to put the partition in the different file group and drive to get more performance and flexibility.
In the example, I am going to create 2 tables. TransactionHistory and TransactionHistoryArchive. TransactionHistory will have 13 partitions and TransactionHistoryArchive will have 2 partitions. After the data load to the tables. I am going to move one of the TransactionHistory partition to TransactionHistoryArchive.
Tempdb is playing more heavily role since SQL Server 2005. if we did not control or monitor it well, it may grow and eventually will fill up the disk.
Recently I come across the need to shrink the database file to avoid the file system being filling up.
The first thoughts come to the mind is using DBCC SHRINKFILE
DBCC SHRINKFILE with TRUNCATEONLY option only remove the free pages after the last allocate extend. It does not perform any page moving movement. Hence it is generally no harm and perform very quick.
If that is not enough, we can use DBCC SHRINKFILE(FILENAME,TARGETSIZE).
This will perform the page movement and it will be slower and take much longer time. This may possible shrink the file smaller then its initialize size as long as there is free space within the file.
To check the free space within the file, we can run
This information is different from what you get from sp_spaceused. sp_spaceused is given you the total size combine with log and data. the sys.database file is break down by each database file.
The DBCC SHRINKFILE process can be stop at any time. Any work has been complete will retain.
In many case, you may wonder how long does it take to complete,we can query from sys.dm_exec_requests
DBCC Shrinkfile does not retain the index fragmentation and could possible make it worse. It is suggest to check the index fragmentation after you shrink the file and decide whether it is need to rebuild or recognize.
Below query can be used to check the index fragmentation on the given database and given file group.
DMV store in the memory, it only capture the runtime information and does not keep the historical data. Once the server bounce, the information will lost and start over.
Evaluate whether it is need to have the index
To get the current running spid query plan as XML format
To get the Query plan for past query from cache
Get the file size for the giving database
The number return is in the Page, it needs to times 8192 to covert to bytes.
Get the pending IO session
sys.dm_os_windows_info will show the OS information such as service pack. It is only available after 2008 R2 SP1.
Show the current spid wait stats.
The wait stats is accumulate since last server bounce or manually clean the state. we can use DBCC SQLPER to clean the wait state
Show the current worker process and how long they have being running
Show the any OS feed back information
SQL Server use 8K page(8192 Bytes). Only 8060 bytes can be used to store the row.
Each table can either have Clustered index page or Heap page.
Clustered index page id is 1. Heap Page id is 0.
DBCC IND( ‘DB NAME’,’TABLE NAME’,OPTION)
The option can be one of the following
IAM INFO is NULL meaning it is the IAM page. Page type 10 also means it is IAM page.
To exam detail what is inside of the page. Use DBCC PAGE
DBCC PAGE(‘DB NAME| DB ID’, FILE NUMBER,PAGE NUMBER, PRINT OPTION)
There are 4 print options:
Option 3 is the most human readable format. DBCC trace on 3604 is for output the information to the console instead of writing in the error log.
Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx
Database must be in FULL Recovery mode and can not have the file stream file group.
Database mirroring is the SQL Server Enterprise Edition feature. The Principal and Mirror database must use Enterprise Edition. The Witness server can be Express.
The default port for the SQL Server mirroring endpoint is 5022 but it can be changed to any unused port.
Below example is setting up the database mirror on the high safety with automatic failover or called Synchronous Database Mirroring (High-Safety Mode)
Restore Database from principal to Mirror database with non recovery and restore Log from Principal to Mirror database with non recovery
You will need to change the port number if they are on the same server. However, it is recommend you put in the different server ( for High availability of course )
Create end point on both Principal and Mirror server
Create the end point on the witness server
Run below on the Mirroring database server
Run below on the principal server.
The SQL Agent job will be created on the mirroring database server, we can use msdb.dbo.sp_help_job to review the job detail.
Use below tables to view the Mirror database and endpoint configuration. This can be run on either Principal, mirror and witness server.
Since the mirroring database is under the recovery and mirroring state. We can not directly access the data. As alternative, we can create the snapshot and access the snapshot data.
In the Synchronous Database Mirroring (High-Safety Mode), the failover will automatically happen in the event the principal database is not available. If you set the mirror mode as high performance mode, this will require the manually failover and use FORCE_SERVICE_ALLOW_DATA_LOSS
To manually failover in the High-Safety mode, we can run below on the principal server.
Once it is done, when we select from sys.database_mirroring, we can see the original principal database will become “Mirror” and the old mirror will become ‘Principal’.
Anytime during the mirroring, we can disable the witness by running below command.
Run on any of the partner server
If any snapshots were created, the needs to be drop before we can recover the database.
We can use this to monitor the database mirroring status.
A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Database Mirroring Overview http://msdn.microsoft.com/en-us/library/ms189852.aspx
Log shipping high level over view
Log shipping will involves 4 jobs
SSMS can be used to generate the Script.
Below example is setting up the log backup job for every 5mins
We can use msdb.dbo.sp_help_job to review the job detail
Before the log shipping job actually start working, we will need to restore the database from primary to secondary with NONRECOVERY option.
msdb.log_shipping_primary_databases or sp_help_log_shipping_primary_database
More store procedure or system table can be found here http://msdn.microsoft.com/en-us/library/ms175106.aspx
Use On the primary server, sp_help_log_shipping_primary_secondary to exam the log shipping secondary db setup and execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server.
Use sp_help_log_shipping_primary_database to exame the primary database setup and use sp_delete_log_shipping_primary_database to remove the primary database being as log shipping database. this will remove the backup log job too.
On the secondary server, use sp_help_log_shipping_secondary_database to exam the secondary information and use sp_help_log_shipping_secondary_database to remove it. this will remove the restore job and copy job too.
Why I can not find the log shipping option in the database property within the SSMS?
The log shipping and database mirror is only support in the non express version. You wont be able to find this option in the SQL Express SSMS.
From SQL Express SSMS
Log Shipping Overview http://msdn.microsoft.com/en-us/library/ms187103.aspx