Data types in SQL Server

6. What's the difference between NUMERIC and DECIMAL data types and when do I use them?

There is no difference between NUMERIC and DECIMAL data types. They are synonymous to each other and either one can be used. DECIMAL/NUMERIC data types are numeric data types with fixed precision and scale. DECIMAL (p [, s ]) NUMERIC (p [, s ]) In declaring a DECIMAL or NUMERIC data type, p, which is the precision, specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The s is the scale and it specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale, which defaults to 0 if not specified, must be a value from 0 to the precision value. The following table specifies the storage size required based on the precision specified for the NUMERIC or DECIMAL data type: Precision Storage Size 1 - 9 5 bytes 10- 19 9 bytes 20-28 13 bytes 29-38 17 bytes

7. What's the difference between FLOAT and REAL data types and when do I use them?

FLOAT and REAL data types are both approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented. The differences between these 2 data types are in the minimum and maximum values each can hold as well as the storage size required, as specified in the following table: Data Type n Minimum Value Maximum Value Precision Storage Size float [(n)] 1-24 -1.79E + 308 1.79E + 308 7 digits 4 bytes 25-53 -1.79E + 308 1.79E + 308 15 digits 8 bytes real -3.40E + 38 3.40E + 38 7 digits 4 bytes For FLOAT data type, the n is the number of bits used to store the mantissa in scientific notation and thus dictates the precision and storage size and it must be a value from 1 through 53. If not specified, this defaults to 53. In SQL Server, the synonym for REAL data type is FLOAT(24). If your data requires only a maximum of 7 digits precision, you can either use the REAL data type or FLOAT data type with 24 as the parameter (FLOAT(24)).

SQL SERVER – 2005 Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types

TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommanded to use new data types which are VARHCAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).
Maximum size supported by TEXT and Varchar(max) are same.
Maximum size supported by NTEXT and NVarchar(max) are same.
Maximum size supported by Image and Varbinary(max) are same.

 

How to Use the Microsoft Batch File Language

 

Batch files are DOS command line commands batched together. In Linux they are known as shell scripts, and follow a completely different syntax.

I am aware of these batch files....

What is Index Position in Cache Transformation in SSIS

 
The Cache connection manager reads a reference dataset from the Cache transform or from a cache file (.caw), and can save the data to a cache file. The data is always stored in memory.

- The Cache connection manager does not support the Binary Large Object (BLOB) data types DT_TEXT, DT_NTEXT, and DT_IMAGE




Specify which columns are index columns by specifying the index position of each column. The index is a collection of one or more columns.

For non-index columns, the index position is 0.

For index columns, the index position is a sequential, positive number. This number indicates the order in which the Lookup transformation compares rows in the reference dataset to rows in the input data source. The column with the most unique values should have the lowest index position.


The column which we specified as index Position, those all should be used as lookup search.


If you configure the cache connection manager to use a cache file, the connection manager will do one of the following actions:

- Save data to the file when a Cache Transform transformation is configured to write data from a data source in the data flow to the Cache connection manager. For more information, see Cache Transform.
- Read data from the cache file.

What's the difference between installing a "Default Instance" vs a "Named Instance"?

You can install only one default instance but can install multiple named instances.

The underlying difference between a default and named instance is mostly a matter of network connectivity. Clients can connect to the default instance using only the host name over the well-known 1433 port. To connect to a named instance, clients specify the host and instance name (e.g. "MyHost\My_Instance").

If you're only going to install one version of SQL Server on this machine, go with a default. If you're planning on multiple instances on this machine, it's up to you whether you want one named and one default, or both named.

What is ip address to a computer..?

"IP" stands for Internet Protocol, so an IP address is an Internet Protocol address. What does that mean? An Internet Protocol is a set of rules that govern Internet activity and facilitate completion of a variety of actions on the World Wide Web. Therefore an Internet Protocol address is part of the systematically laid out interconnected grid that governs online communication by identifying both initiating devices and various Internet destinations, thereby making two-way communication possible.

An IP address consists of four numbers, each of which contains one to three digits, with a single dot (.) separating each number or set of digits. Each of the four numbers can range from 0 to 255. Here's an example of what an IP address might look like: 78.125.0.209. This innocuous-looking group of four numbers is the key that empowers you and me to send and retrieve data over our Internet connections, ensuring that our messages, as well as our requests for data and the data we've requested, will reach their correct Internet destinations. Without this numeric protocol, sending and receiving data over the World Wide Web would be impossible.

IP addresses can be either static or dynamic. Static IP addresses never change. They serve as a permanent Internet address and provide a simple and reliable way for remote computers to contact you. Static IP addresses reveal such information as the continent, country, region, and city in which a computer is located; the ISP (Internet Service Provider) that services that particular computer; and such technical information as the precise latitude and longitude of the country, as well as the locale, of the computer. Many websites provide IP address look-up services to their visitors, free of charge. If you're curious about your own IP address, you can locate these websites by performing a Google search.

Dynamic IP addresses are temporary and are assigned each time a computer accesses the Internet. They are, in effect, borrowed from a pool of IP addresses that are shared among various computers. Since a limited number of static IP addresses are available, many ISPs reserve a portion of their assigned addresses for sharing among their subscribers in this way. This lowers costs and allows them to service far more subscribers than they otherwise could.

Determining if your IP is Static or Dynamic

The process for determining what type of IP address you have is relatively simple, but it varies amongst operating systems.

Windows

Execute the following command from a command prompt:

ipconfig /all

Find the line containing DHCP Enabled which is found in the below block

Physical Address. . . . . . . . . : 00-00-00-00-00-00-00-E0
DHCP Enabled. . . . . . . . . . . : No
Autoconfiguration Enabled . . . . : Yes

If that value is set to Yes then you have a static IP address and if it is No your IP address is dynamic.

Temporary Stored Procedures in SQL Server 2008

 Today I discovered a small feature of Temporary Stored Procedures in SQL Server.

Temporary Stored Procedures are similar to normal Stored Procedures, but as their name suggests, have a fleeting existence. There are two kinds of temporary Stored Procedures, local and global. Temporary Stored Procedures are created just like any other SP but the name must be prefixed with a hash (#) for a local temporary SP and two hashes (##) for a global temporary Stored Procedure.

A local temporary Stored Procedure is available only in the current session and is dropped when the session is closed or for a different session.

For Example: I have created a temporary Stored Procedure in a query window with session Id: (75).



When I execute the same session then the output is as below:



When I execute a different session the error is shown below:



A global temporary Stored Procedure is visible to all sessions and is dropped when the session of the user that created it is closed. 

For Example: I have created a temporary Stored Procedure in the query window with session Id: (68).




When I execute the same session the output is as shown below:



When I execute a different session the output is as shown below:



If there are any executing versions of the global Stored Procedure when the creator session is closed.

Apart from local and global temporary Stored Procedures, you can also create a regular stored process in the system database tempdb.

You should see this in Management Studio:



This picture displays a Stored Procedures in the tempdb database while the session is active. Notice the long postfix that SQL Server automatically adds to the local temporary Stored Procedure. SQL Server adds some postfixes to local temporary objects to differentiate objects with the same name from different sessions.
 

SQL Server 2012 SSIS Enhancements

SQL Server 2012 Undo and Redo Features

Parameters can be defined at 2 different levels:

Project. These type of parameters are available to all packages within the SSIS project. Think about them as global parameters. They come handy as it makes really easy to share a given value, such as the path to a file share or the name of a server, across all packages in a project.

         Package. These type of parameters are meant to affect only the package on which they were defined.

-------------------------------------------------------------

Very often I had difficult a time creating SSIS packages.  Unfortunately, when I did something wrong and wanted to revert the last change, I was unable to do so.  It was very frustrating.   As an example, sometimes I unintentionally deleted a task, which I wanted to get back. At times, I had to revert back to the SSIS package version in the VSTF version store. This was all because we did not have any way to undo or redo our changes. Now with SQL Server 2012, we have this feature in the SSIS designer.

As you can see in the image above, you can now use Undo or Redo commands using the menu bar, toolbar or by pressing CTRL+Z for "undo" and by pressing CTRL+Y for "redo".

The designer now tracks all the actions that you perform, which you can see in the image above and below.  You have the ability to Undo or Redo as many actions as you want.

---

SQL Server Integration Services Toolbox Enhancements

Now the components inside the toolbox have been placed in few more categories (Favorites and Common) for ease of use.  These categories are not fixed and you can customize it as per your need. For example, you can place all your frequently used components inside "Favorites" section which appears at the top of the Toolbox as shown below:

To move a component to some other location/category, right click on the component and click on the location where you want that component to be placed as shown below.

On a final note, one really good aspect of the enhanced toolbox is the information section on the bottom of the toolbox.  This area displays details about selected component and provides an option to directly jump to the documentation for that selected component. This is really useful for someone new to SSIS development and/or someone that does not have prior knowledge about the component.

SQL Server Integration Services Solution Explorer Enhancements

There are some noticeable changes in the SSIS Solution Explorer. Deployment of SSIS packages has been a challenge as we had to deploy each SSIS package individually. There was no way to create a deployment package containing all the SSIS packages similar to some of the SSRS and SSAS functionality to deploy all objects. SQL Server 2012 now supports a new project deployment model as well as the legacy deployment model. Discussion about new Project Deployment Model in itself is a big topic, so check out the SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2) and SSIS Package Deployment Model in SQL Server 2012 (Part 2 of 2) tips.  In a nutshell, the Legacy Deployment Model lets you deploy each individual package separately whereas the new Project Deployment Model lets you create deployment packet (which is an *.ispac file) from the SSIS project. Each SSIS project which you create in SQL Server 2012, by default gets created in Project Deployment Model which you can change if needed as shown below:

As previously indicated the new SSIS projects get created in the Project Deployment Model. When you right click on the project you will notice two more options. The "Project Parameters" let's you define the parameters in the project and the "Deploy" option let's you create the deployment package or deploy to the Integration Services Catalog database.

If you right click on the package in the Solution Explorer, you will notice one more option (Entry-Point Package). This option is helpful in the scenario when you have a parent package which calls child packages. You can set the Entry-Point Package option for the parent package.