SQL Server – Single Instance vs. Multiple Instances

Single Instance Pros Only one instance needs to be administered on the single machine. There is no duplication of components or processing overhead, such as having to run multiple database engines on the same computer. This means that the overall performance of a server with a single instance may be higher than a server running multiple instances. A single instance of SQL Server is capable of handling the processing growth requirements of the largest Web sites and enterprise data-processing systems,

» Read more

SQL Server – Understanding of SQL Server Role and Database Role

SQL Server supports the two authentication mode as Windows authentication mode and Mixed authentication mode. Once the user is authenticated, SQL server will allow access to the user based on the permission that user has. All the user permissions are based on Server Role and Database Role. So let try to user understand the meaning of each server role and database role. Server Role: Role Name Permission user can have bulkadmin Can run the BULK INSERT statement. dbcreator Can create,

» Read more

SQL Server – Like operator does not work with datetime

Today, my one of the friend shared a very interesting fact about like operator. With the help of this post I am sharing with you all. In SQL Server, like operator does not work with datetime. We need to convert it to character data while comparing with the like operator. Let‘s check with example Like operator with datetime Like Operator with datetime using convert function If anybody have any suggestion please share as comment.

» Read more

SQL Server – How to add column dynamically in where clause

In this post, we will learn with example how to add the column dynamically in where clause. Example: For demo we have table as shown in the snapshot Basically we want to execute following script (i.e. Script: 1) but column name (i.e. FIRST_NAME) added dynamically in where clause. Script: 1 SELECT * FROM EMPLOYEE WHERE FIRST_NAME = 'VARINDER' Created a table and insert the column name value (i.e. FIRST_NAME) as shown in the snapshot Now we will execute the Script:

» Read more

SQL Server – The multi-part identifier could not be bound State – Msg 4104 – Fix

Error Msg Server: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier could not be bound. Cause of this Error Msg: Case: 1 This error usually occurs when an alias is used when referencing a column in a SELECT statement and the alias used is not defined anywhere in the FROM clause. Case: 2 This error occurs if we are using the tables from the two different databases in the join statement and not using the table alias

» Read more

Best Way to Configure Firewall for SQL Server Access

Windows firewall is nothing but the software and hardware based network security. It generally controls the transaction traffic mixed up with data packets. Simultaneously it determines whether the upcoming signal is eligible for further routing or not. The threats coming from outside can be prevented with this protective shield. On the other hand, SQL server is a categorized section of relation database management system. Here the data are stored and retrieved to reduce the workload. Principal steps: When the basic

» Read more

SQL Server – Export the Data from SQL Server to Oracle Database – Import-Export Wizard

The SQL Server Import and Export Wizard provides the simplest method of copying data between data sources. In this article given an example to copy the data from SQL Server to Oracle database. Click here to download the full PDF version of this article. After this article,for more detail about SQL Server Import and Export Wizard. please check http://msdn.microsoft.com/en-us/library/ms141209

» Read more

SQL Server – Generate Row Number in Select and Select into

How to Generate the Row Number in Select and Select into script? Using IDENTITY for Select Into SELECT RowNumber=IDENTITY(int,1,1),FIRST_NAME,LAST_NAME into EMPLOYEE_backup FROM EMPLOYEE Using Row_Number() for Select SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) RowNumber, FIRST_NAME,LAST_NAME FROM EMPLOYEE Using Row_Number() for Select into SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) RowNumber , FIRST_NAME,LAST_NAME into EMPLOYEE_backup1 FROM EMPLOYEE

» Read more

SQL Server 2008 – How to copy a Table

Lets see how to copy an existing table to new table in SQL Server. There are two options. Copy the table with Data. Copy the table without data (i.e.Table Structure Only) — Create Table CREATE TABLE EMPLOYEE ( ID INT NOT NULL, FIRST_NAME VARCHAR(10), LAST_NAME VARCHAR(10) );   — INSERT SOME VALUES INTO TABLES INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (1,'VARINDER','SANDHU'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (2,'DINESH','SHARMA'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (3,'RANJOYT','SINGH'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (4,'VIKRAM','SINGH');   –CREATE

» Read more

SQL Server – NEWID () vs. NEWSEQUENTIALID ()

NEWID () NEWID () creates a unique value of type uniqueidentifier. NEWID () can be referenced in the queries. Example: NEWSEQUENTIALID () NEWSEQUENTIALID () creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. NEWSEQUENTIALID () cannot be referenced in queries. Example: Example: Using table Imp Note: Each GUID generated by

» Read more

SQL Server 2008 – Get the next Identity Value

Question: How to get the next Identity Value in SQL Server 2008? Answer: It is very simple to the get the identity value in SQL Server. Let’s see with the example below Lets‘s create some dummy data as mentioned below … In SQ Server, there are two functions IDENT_CURRENT and IDENT_INCR. With the help of these funtions, we can get next value Identity. IDENT_CURRENT: It returns the last identity value generated for a specified table or view. The last identity

» Read more

SQL SERVER – 2008 – Inline Variable Assignment – Declare and Assign Variable

Inline variable assignment is available in SQL Server 2008 or higher version. Beside this is very simple but very useful feature. Many of the programming languages have this feature. But now SQL Server 2008 or higher version has this feature. Example: —- SQL Server 2005 or earlier versions DECLARE @var1 INT SET @var1 = 100 SELECT @var1 AS myVar GO —- SQL Server 2008 or Higher Version (Inline Variable Assignment) DECLARE @var1 INT = 100 SELECT @var1 AS myVar GO

» Read more
1 2 3 6