SQL Server Execution Context and Security
What is Execution Context? Whenever a user connects to SQL Server, it will create the User Session called Execution Content which has the all the user access details and whenever that user execute…
Showing posts from 2011
Moving tempdb to a new location
The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physic…
SQL Server Service Accounts
Service account for Files and folders Access MSSQLServer Instid\MSSQL\ba…
What is Database Mirroring
In the proposed database mirroring solution there will be two groups of active-passive windows clusters deployed at each location (PRIMARY SITE and Secondary Site). The Primary Site will be the prima…
How do you specify a different port number in SQL Management Studio?
Specifying a port number on SSMS is pretty easy Under the server name , specify the server(or ip)\instanceName, ( comma) port eg: 10.10.10.151\SQL2008,1433 Another way is to setup an alias in…
Finding the Port Number for a particular SQL Server Instance
One of the developers recently asked me this question: “I have a SQL Server instance running – how can I tell what port it is running on?”. There are a couple of ways of finding this information. …
Table Size in SQL Server | Find Rows and Disk space Usage
There are two ways to find out the table size; first option will be to run the system stored procedure “sp_spaceUsed” 1: EXEC sp_SpaceUsed ' table_name ' Option 2 uses the dmv sys.dm_…
Generating Calendar in SQL Server 2008
Here is the simple query that generates a calendar. 1: DECLARE @startDay DATE = ' 20110101 ' 2: ,@endDay DATE = ' 20130101 ' 3: 4: ; WITH calendar ( [ Date ], [ Y…
Is TRUNCATE TABLE transactional ?
1: CREATE TABLE tTest ( i INT ) 2: INSERT tTest ( i ) VALUES ( 1 ) 3: INSERT tTest ( i ) VALUES ( 1 ) 4: INSERT tTest ( i ) VALUES ( 1 ) 5: INSERT tTest ( i ) VALUES ( 1 ) 6: 7: BEGIN …
How to remove blank line in SSMS “Find and Replace” dialogue
The answer is simple; in Management Studio go into Find and Replace (ctrl+H ) , in "Find Options" check "Use" and select "Regular Expressions". Now, in the Find what: …
How to find relationship between tables
As a DBA or even as a SQL Server Programmer, we may need a quick script to find relationship between tables. Here is the script which helped me a lot, hope this will be useful for you too 1: SEL…
TSQL Function to convert decimal to Hex, Octal or any other base
Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginal…
TSQL Function to encode HTML Text
While browsing through the SharePoint content database, I found a very useful TSQL utility function which can be used in any application. This function encodes the given html so that it can be safely…