Wednesday, May 16, 2018

Difference between #temptable and ##TempTable and table variable?

Local Temporary tables

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign. scope of Local Temporary table is only bounded with the current connection of current user.

CREATE TABLE #TempTable(
 ID int,
 Name varchar(50))

Global Temporary tables

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

CREATE TABLE ##GlobalTempTable(
 ID int,
 Name varchar(50))

Table variables in T-SQL

Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. In many cases a table variable can outperform a solution using a temporary table
Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example:

DECLARE @UserTable TABLE
( ID int,
  Name varchar(50))

Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter. In fact, a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.

Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.
Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit.

Configure the remote query timeout Server Configuration Option

Use below query to increase default time out or disable time out time.

USE ps;  
GO  
EXEC sp_configure 'remote query timeout', 0 ;  
GO  
RECONFIGURE ;  
GO 

The remote query timeout option specifies how long, in seconds, a remote operation can take before SQL Server times out. The default value for this option is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine. To disable the time-out, set the value to 0. A query will wait until it completes.

For more information, see Server Configuration Options (SQL Server).

Thursday, May 10, 2018

Quick Note: How to Enable USB Debugging on Android Phone

For the newer Android operating system like Android 4.2 or later,
You can enable USB debugging by the following steps:

Step 1. Enter "Settings", then, click "About Phone".
Step 2. Tap "Build number" for 7 times until you get a note "You are under developer mode".
Step 3. Then back to "Setting", then, click "Developer options".
Step 4. The last step, tick "USB debugging"

Tuesday, May 01, 2018

TLS 1.2 and .NET Support: How to Avoid Connection Errors

I recently ran into an interesting issue when developing we are connecting to a third-party Carrier API. When trying to connect to the API endpoint, I received the following error message:

“An error occurred while making the HTTP request to https://<API endpoint>. This could be due to the fact that the server certificate is not configured properly with HTTP.SYS in the HTTPS case. This could also be caused by a mismatch of the security binding between the client and the server.” Inner exception was “Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.”

With data security in place lot of API providers are ending TLS 1.0 support. This can be overcomed by adding a line of code in .NET by setting TLS 1.1 or above
The default System.Net.ServicePointManager.SecurityProtocol in both .NET 4.0/4.5 is SecurityProtocolType.Tls|SecurityProtocolType.Ssl3.

.NET 4.0 supports up to TLS 1.0 while .NET 4.5 supports up to TLS 1.2

However, an application targeting .NET 4.0 can still support up to TLS 1.2 if .NET 4.5 is installed in the same environment. .NET 4.5 installs on top of .NET 4.0, replacing System.dll

Adding below one line of code will help you to avoid above error.

ServicePointManager.SecurityProtocol = (SecurityProtocolType)192 |
(SecurityProtocolType)768 | (SecurityProtocolType)3072;

Reference:

namespace System.Net
{
    [System.Flags]
    public enum SecurityProtocolType
    {
       Ssl3 = 48,
       Tls = 192,
       Tls11 = 768,
       Tls12 = 3072,
    }
}

SQL Server 2016 Service Pack 2 (SP2) released !!!

The SQL Server 2016 Service Pack 2 (SP2) contains a rollup of released hotfixes as well as multiple improvements centered around performance, scalability, and supportability based on feedback from customers and the SQL community. These improvements enable SQL Server 2016 to perform faster and with expanded supportability and diagnostics. 

SQL Server 2016 Service Pack 2 includes:

  • Performance and scale improvements for SQL Server, such as improved database backup performance on large memory machines and added backup compression support, which helps performance of almost all databases.
  • Supportability and diagnostics enhancements, such as improved troubleshooting and additional information about statistics used during query plan optimization.