It is used to collect more information about database like its properties.
Like it can be used to check the recovery model, online/offline status, compatibility level, auto shrink, published/subscribed, version etc.,
SELECT name,
DATABASEPROPERTYEX(name, ‘Recovery’),
DATABASEPROPERTYEX(name, ‘Status’)
FROM master.dbo.sysdatabases
Most SQL installations have default collation. i.e case insensitive or SQL_Latin1_General_CP1_CI_AS in SQL Server glossary.
It can be changed at server, DB or column levels.
How to check current collation?
SELECT SERVERPROPERTY (‘Collation’)
Helps in evaluating how applications are running on SQL Server. It helps to track CPU, memory and RAM.
In 2000, it was done by using the command DBCC SQLPERF (‘WAITSTATS’)
In 2005, it is replaced with DMV known as sys.dm_os_wait_stats.
It gives information about the columns wait_type, waiting_time etc.,
Different waittypes that are captured are:
1. Latches/Locks
2. Backups
3. Tlogs
4. Checkpoints
5. Lazy writer
It helps in troubleshooting query performance issues .
Normally during the SSRS installation along with other components we give default service accounts by clicking on “Add current user” and then proceed with the installation steps.
After the installation is over, then we go to start –> all programs –> SQL Server –> configurattion tools –> reporting services configuration manager –> to set service account, web service URL, report manager URL etc.,
Finally when you go to reportmanager URL like http://localhost/reports/, you may see this error for the first time
“User ‘
To resolve this we have 2 solutions:
1) open IE in administrator mode and then click on URL to open the reports. But this will become tedious everytime to right-click and open as admin in IE.
2) To fix it permanently, open IE in admin mode, go to site settings and then security tab, click on “new role assignment”.
Here you will observe 2 role groups, “System Administrator” and “System User”.
Give your domain account as sysadmin and may be any another user who needs more privileges as system user.
Then click on home and then go to “folder settings” click on “new role assignment” and give the sysadmin you have chosen before as content manager and the system user as publisher or browser.
Now when you go to either reportmanager or reportserver URL, you will have not have Windows UAC restrictions issue anymore.
Some screenshots to follow:
Beware that Rowcount is deprecated as of 2012 and TOP is the keyword suggested by BOL.
We will try to see the differences between the 2 keywords:
SET ROWCOUNT
Limits the number of records to the specified number
[cc lang=”sql”]
SET ROWCOUNT 10
select * from master..spt_values
–or
select top 10 * from master..spt_values
[/cc]
How to reset the this setting
SET ROWCOUNT 0
TOP Distinctive Features
[cc lang=”sql”]
declare @rc int
set @rc=10
select top(@rc) * from master..spt_values
or
declare @rc int
set @rc=10
set rowcount @rc
select top(@rc) * from master..spt_values
set rowcount 0
[/cc]
Disclaimer: MS suggests to avoid using ROWCOUNT in conjunction with delete, insert and update statements