FAQ's
*******************************************************************************
What is Microsoft SQL Server?
Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It runs on Windows systems and uses Transact-SQL as the query language.
Cache
Expensive memory, executes faster than RAM
DLL - Dynamic Link Library
A DLL is a library that contains code and data that can be used by more than one program at the same time. For example, in Windows operating systems, the Comdlg32 DLL performs common dialog box related functions. Therefore, each program can use the functionality that is contained in this DLL to implement an Open dialog box. This helps promote code reuse and efficient memory usage.
The following list describes some of the files that are implemented as DLLs in Windows operating systems:
* ActiveX Controls (.ocx) files
An example of an ActiveX control is a calendar control that lets you select a date from a calendar.
* Control Panel (.cpl) files
An example of a .cpl file is an item that is located in Control Panel. Each item is a specialized DLL.
* Device driver (.drv) files
An example of a device driver is a printer driver that controls the printing to a printer.
WMI, Windows Management Instrumentation or Windows Management Interface is a core Windows management technology; you can use WMI to manage both local and remote computers. WMI provides a consistent approach to carrying out day-to-day management tasks with programming or scripting languages.
For example, you can:
Start a process on a remote computer.
Schedule a process to run at specific times on specific days.
Reboot a computer remotely.
Get a list of applications installed on a local or remote computer.
Query the Windows event logs on a local or remote computer.
What's the difference between sp_who and sp_who2
* sp_who2 is an undocumented extension of sp_who and it is available from MS SQL Server 7
The sp_who internal procedure allows users to view current activity on the database. This command provides a view into several system tables (e.g., syslocks, sysprocesses, etc.). The sp_who command returns the following information:
* Spid—The system process ID.
* status—The status of the process (e.g., RUNNABLE, SLEEPING).
* loginame—Login name of the user.
* hostname—Machine name of the user.
* blk—If the process is getting blocked, this value is the SPID of the blocking process.
* dbname—Name of database the process is using.
* Cmd—The command currently being executed (e.g., SELECT, INSERT)
The sp_who2 internal procedure provides the above information, but also provides the following additional information:
* CPUTime—Total CPU time the process has taken.
* DiskIO—Total amount of disk reads for the process.
* LastBatch—Last time a client called a procedure or executed a query.
* ProgramName—Application that has initiated the connection (e.g., Visual Basic, MS SQL Query Analyzer)
Using sp_who2 enables SQL Server users to better analyze database processes and more easily identify which application is associated with each process.
About DBid's
Master -1
tempdb-2
Model-3
Msdb-4
Adventure works-5
AdventureDWworks-6
user db-7to32766
resource-32767
-> Consider if you have DBid’s 7, 8, 9 and 10 for user database’s A, B, C and D respectively. If you delete B database, the DBid will be kept idle [returns NULL if you query for DBid] until a new user database is created. SQLServer assigns automatically the unused DBid’s to new created database’s.
How to get the database name from dbid or how to get the dbid from database name.
SELECT DB_ID('Database_Name')
The above T-SQL query is using DB_ID( ) function to get the database id from database name.
SELECT DB_Name(1)
The above query will give you the database name, the DB_Name ( ) function takes database id as parameter and returns database name.
In both functions if no value is found i.e database name or database id does not exists then a NULL value is returned.
Stoping & Starting SQL Server DB’s from command prompt
Click Start >> Run >> type cmd to start command prompt.
-> Start/Stop default instance of SQL Server
net [start/stop] mssqlserver
-> Start/Stop named instance of SQL Server
net [start/stop] MSSQL$[named Instance name]>
BODMAS?
B
Brackets first
O
Orders (ie Powers and Square Roots, etc.)
DM
Division and Multiplication (left-to-right)
AS
Addition and Subtraction (left-to-right)
WYSIWYG - What You See Is What You Get
TCO - Total cost of ownership is a financial estimate. Its purpose is to help consumers and enterprise managers determine direct and indirect costs of a product or system.
*******************************************************************************
Shortcuts
*******************************************************************************
compmgmt.msc -> Opens Computer Management console - to create users, groups, to trace event viewer log, etc..
msinfo32 -> To retrieve System Information
appwiz.cpl -> Add or Remove Programs console
Windows + Pause/Break -> System Properties
eventvwr -> To open event viewer console
*******************************************************************************
General Information
*******************************************************************************
Some important points about instances
* Instance is a software copy of SQL Server
* During SQL Server Setup, an instance ID is generated for each server component. The server components in this SQL Server release are the Database Engine, Analysis Services, and Reporting Services. The instance ID is in the format MSSQL.n, where n is the ordinal number of the component being installed. The instance ID is used in the file directory and the registry root.
* The first instance ID generated is MSSQL.1; ID numbers are incremented for additional instances as MSSQL.2, MSSQL.3, and so on. If gaps occur in the ID sequence due to uninstalls, ID numbers are generated to fill them. The most recently installed instance may not always have the highest instance ID number.
* Server components are installed in directories with the format
*
*
*
Instead of
* SQL Server 2005 Integration Services, Notification Services, and client components are not instance aware and, therefore, are not assigned an instance ID. Non-instance-aware components are installed to the same directory by default:
* When adding features to an existing installation, you cannot change the location of a previously installed feature, nor can you specify the location for a new feature. You must either install additional features to the directories already established by Setup, or uninstall and reinstall the product.
* Do not delete any of the following directories or their contents: Binn, Data, Ftdata, HTML, or 1033. You may delete other directories, if necessary; however, you may not be able to retrieve any lost functionality or data without uninstalling and then reinstalling SQL Server 2005.
* Do not delete or modify any of the .htm files in the HTML directory. They are required for SQL Server tools to function properly.
* Program files and data files cannot be installed on a removable disk drive, cannot be installed on a file system that uses compression, and cannot be installed on shared drives on a failover cluster instance.
