Category: SQL

Deleting Data in SQL Server with TRUNCATE vs DELETE commands

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement, it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.

TRUNCATE TABLE cannot used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the foreign key and then recreate it.

Source: Deleting Data in SQL Server with TRUNCATE vs DELETE commands

IEnumerable VS IQueryable

 

While query data from database, IQueryable execute select query on server side with all filters (e.g SQL select statement on database server). IEnumerable filters the data on client side. (in memory in application)

IEnumerable Example

  1. MyDataContext dc = new MyDataContext ();
  2. IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith(“S”));
  3. list = list.Take<Employee>(10);

Generated SQL statements of above query will be :

  1. SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
  2. WHERE [t0].[EmpName] LIKE @p0

Notice that in this query “top 10” is missing since IEnumerable filters records on client side

IQueryable Example

  1. MyDataContext dc = new MyDataContext ();
  2. IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith(“S”));
  3. list = list.Take<Employee>(10);

Generated SQL statements of above query will be :

  1. SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
  2. WHERE [t0].[EmpName] LIKE @p0

Notice that in this query “top 10” is exist since IQueryable executes query in SQL server with all filters.

 

Source: IEnumerable VS IQueryable

ExpressProfiler (aka SqlExpress Profiler) – Home

ExpressProfiler (aka SqlExpress Profiler) is a simple and fast replacement for SQL Server Profiler with basic GUI and integration with Red Gate Ecosystem project.

Can be used with both Express and non-Express editions of SQL Server 2005/2008/2008r2/2012/2014 (including LocalDB)
Distribution package contains both standalone version of ExpressProfiler (can be used without installation) and installation package.

ExpressProfiler – Home.

Set up SQL Server on home network – Stack Overflow

Step by step from Stack overflow.
Trouble finding SQL Server Configuration Manager?
Its at “C:\Windows\System32\SQLServerManager11.msc” (2012)
or “C:\Windows\System32\SQLServerManager10.msc” (2008)
———————————————

Test Open Port The network on your LAN, can you ping SQL Server remotely on the default port 1433 on the specific IP Address (you can use PuTTY or Telnet to check this)

SQL Configuration Manager Check SQL Configuration Manager and see if the Network Protocol for SQL is enabled for TCP/IP, Named Pipes or Shared Memory

Firewall and Default Port Check the Windows Firewall make sure its allowing 1433. Since you are testing, best thing to do is to disable the Firewall in Windows Services to confirm whether its a firewall issue or not.

SQL Server Browser SQL Server Instances (instances have the form SERVER\SQLEXPRESS or SERVERNAME\SQL1 for example). Check in services and makes sure the “SQL Server Browser” is running. Also, you must allow a Firewall rule for port 1434, which is the default port of the “SQL Server Browser”. This is necessary because the SQL Server maps and forwards the traffic based on the Instance Name, so this is the service that resolves the instance name.

SQL Server Instance Port Varies SQL Server Instances does not necessarily run on port 1433, in this case you will have to have the SQL Browser Running which maps the name to the port and directs traffic to the correct instance. In your firewall, instead of allowing port 1433, you will have to allow sqlservr.exe Executeable Program. The port also can be determined by looking at the SQL Server log File in the MSSQL\LOG folder in Program Files. There will be an entry for “Server is listening on port …”

My recommendation for you is to install SQL Server Express Management Studio (SSMS) which is the GUI interface to manage the SQL Server Express instance. The link is below for both SQL Express and SQL Management Studio. And see if you can connect to the servers that way first. Another suggestion is for you to install both on a Local Computer and simply familarize yourself with SQL Server first before exposing it on the Network.

http://www.microsoft.com/en-us/download/details.aspx?id=29062

via webserver – Set up SQL Server on home network – Stack Overflow.

Restoring SQL Server Database simple summary steps

I always tend to forget the order of this. A short summary for restoring a Sql server  .bak file:

1. Restore database. (Run Sql Server Manager as admin, make sure you window user have access rights to appropiate folders etc).

2. Delete the accompanied database user that is on the “database”. (user should be on database server scope not on the database)

3. If there isn’t already a user on the database server user level, create one, and set the user mapping for user -> database owner of -> newly restored db.

Show all tables where table name contains ‘log’

Select * From INFORMATION_SCHEMA.TABLES
Where
table_name LIKE '%log%'
AND
table_name NOT LIKE '%catalog%'

Show table columns with a specified name:

Use DatabaseName
Select*From INFORMATION_SCHEMA.COLUMNS Where column_name ='ColName'

sql – I want to show all tables that have specified column name – Stack Overflow.