Monday 30 December 2013

Wishing all visitors a very happy new year 2014




Wishing all visitors 

A Very Happy & Prosperous

 New Year 2014

 




Happy New Year 2014


Happy New Year! Thanks to visit queryinqsql.com.
Last year I started this Blog,and post  52 articles,scripts,basics of SQL Server that are based on SQL Server 2008.I hope  some of these articles were very helpful for you. In 2014, it will be more helpful and have some advanced post about SQL Server like 2012 and 2014.Please provide your feedback and suggestions to make this blog much helpful.
Thank you so much! I wish you and your family again a new year filled with peace, joy, and meaning. 


Saturday 30 November 2013

Understanding SQL Server related Windows Registries

SQL Server related information from Windows registries


The Windows Registry is a hierarchical database that stores configuration settings and options on Microsoft Windows operating systems. It contains settings for low-level operating system components and for applications running on the platform that have opted to use the registry. The kernel, device drivers, services, SAM, user interface and third party applications can all make use of the registry. The registry also provides a means to access counters for profiling system performance.
See Wikipedia Article Windows Registry.


To View SQL Server related registries.
Go to Run and type regedit
HKLU=>Software=>Microsoft=>Microsoft SQL Server



Microsoft SQL Server


To find valuable information about edition, version, patch label etc. Traverse as below pic

 
edition, version, patch label

To Change  Default authentication mode 1-window authentication 2-SQL server Authentication


Change  Default authentication mode

Edit Login Mode registry value.

Value= 1 for Window Authentication
Value=2 for SQL server Authentication

Restart SQL server and agent services your default SQL server authentication mode has been changed. You can change for each instance of SQL server.

Note: Serious problems might occur if you modify the registry incorrectly. So do not modify any registry if you do not understand it.

However you can achieve same using xp_instance_regwrite stored procedure to edit registries.as
The below query is used to change authentication mode from mixed to Windows only.

EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', 
REG_DWORD, 
1;

The below query is used to change authentication mode from windows only to Mixed back.

EXEC xp_instance_regwrite 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', 
REG_DWORD,
2;

Each time you execute query,restart the server and check.

To find installed instances on current machine, traverse as below pic

installed instances on current machine
















Saturday 23 November 2013

9 points before backing up SQL Server databases

Backing up SQL Server Databases


 Backing up your SQL Server database is essential for protecting your data. The word Backup refers to “Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.”

You should consider some below points also when making planning for database backups.

1.   Firstly set your database backup plan, In plan do not only include backup databases but also include backups of other things like reporting services, analysis services ,integration services and other third party tools. You should back up complete SQL server environment.you can use third party tools to achieve the same.

2.   After that you should include your physical backup storage location protection policy, since this storage has all data of your databases. It may be very dangerous if it is getting in wrong hands, because the can easily restore this and access your confidential data like Credit card nos. , passwords etc. Also it will be better policy if you restricts from being copied your database backups to another location. However there are some third party database backup tools that allow backup encryption.  

3.   Store database backup another location [some other drive] from database location.so that if in any chance to corruption of files on database drives your back will be secure.

4.   Do not write multiple backups in one physical file so create one backup for one database so that if a backup file for single database has been corrupted it does not affect other backups.

5.   Do not allow multiple users to access on creating backups but if you allow then monitor who is creating backups and where they are being created.

6.   Also backup system databases at least master and msdb since the master database stores security information as well as metadata about the other databases. Msdb stores information about jobs, operators, and alerts etc.

7.   Monitor backup failure, backup size and backup time regularly, if you scheduled backup using Job Scheduling then create an alert to your mail id if job fails.

8.   Testing the entire restore process to verify backup periodically.

9.    Remove older backups time to time to free disk space.



To create and automate backup using SSMS, follow below steps
1.       Go to databases in object explorer and select your database to backup.
        
    
2.       Right click on database =>Backups


3.       In destination, you must select backup device that can be any drive or tape.



4.       Go to options
5.       In options you can set over all existing back up options to overwrite however ignore this rule because if backup corrupted you lost all of backups.
Choose Verify backup when finished.
You also can select Backup compression option from here.
6.       Click ok.The backup file is created on your defined destination.
7.       You can also schedule this task using Script



8.       Set all option as your requirement in job scheduling




9.       Test your job by running once. If it successfully runs, you have successfully automated your backup.

SQL Server Database Backup Resources.
Backup SQL Server









Thursday 14 November 2013

Querying Microsoft SQL Server: T-SQL

Querying Microsoft SQL Server: T-SQL: T -SQL is advancement of SQL. It  include    procedural  programming,  local variables , various support functions for string processing, d...

Tuesday 29 October 2013

Interview FAQ on Interface in C#

What is Interface?

 An interface is collection of methods, properties, indexers and events with only signatures. Means In interface Methods, properties, indexers and events do not have their definition. So an Interface allows you to define behavioral characteristics and apply those behaviours to Class that implements this Interface. Example
public interface IHoliDay
    {

        List<DateTime> GetListOfHoliday();

    }
Or

interface IHoliDay
    {
          List<DateTime> GetListOfHoliday();

    }

If you use public List<DateTime> GetListOfHoliday() an error occurred.

Error  1      The modifier 'public' is not valid for this item       .

So there is no need of modifiers for Interface members. Interface members are automatically public and cannot be static.

Why Interface is came in existence?

 Interface is mainly used for two reasons
·         C# does not support multiple inheritances, so by using Interface, A class can implement multiple interfaces to achieve multiple inheritances.
public class HoliDay : Class1, Interface1,Interface2,Interface3
    {

    }

·         Data Hiding
You can call class members using Interface.
class Program
    {
        static void Main(string[] args)
        {
            //Hiding the Holiday class object
            IHoliday ii = new Holiday();
            ii.GetHoliDayList();
            Console.WriteLine(ii.val);
        }

    }

    public interface IHoliday
    {
        string val{get; set;}
        List<DateTime> GetHoliDayList();
       
    }

    public class Holiday : IHoliday
    {
        public String val{ get; set;}

        List<DateTime> hlist = new List<DateTime>();
        public  List<DateTime> GetHoliDayList()
        {
            //code
            val = "Class Members";
            Console.WriteLine("Calling by interface");
            return hlist;

        }
    }


Interface also used to make your code more reusable, maintainable, and scalable and provides the concept of component based programming.

Suppose you have two interfaces with method with same name. How to implement both in same class? 

User Intafacename.MethodName()
{
}
Example:
class Program
    {
        static void Main(string[] args)
        {
            //Hiding the Holiday class object
            IOfficeHoliday iioffice = new Holiday();
            iioffice.GetHoliDayList();
            IGuzzatedHoliday iiGuzzated = new Holiday();
            iiGuzzated.GetHoliDayList();
            //Console.WriteLine(ii.val);
        }

    }

    //first interface
    public interface IOfficeHoliday
    {
     
        List<DateTime> GetHoliDayList();
       
    }

    //second interface
    public interface IGuzzatedHoliday
    {
      
        List<DateTime> GetHoliDayList();

    }

    public class Holiday : IOfficeHoliday, IGuzzatedHoliday
    {
      

        List<DateTime> hlist = new List<DateTime>();

        //Method definintion for first interface
         List<DateTime> IGuzzatedHoliday.GetHoliDayList()
        {
            //code

            Console.WriteLine("IGuzzatedHoliday Method");
            return hlist;

        }

        //Method definintion for second interface
         List<DateTime> IOfficeHoliday.GetHoliDayList()
        {
            //code

            Console.WriteLine("IOfficeHoliday Method");
            return hlist;

        }
    }

//here if you use public with method definition in class that implements both of interfaces, an error occurred.

What is the use of is and as operator with respect to Interface concept?

 The is and as operators can be used to determine whether an interface is implemented by an object.
Holiday hh = new Holiday();
            IHoliday iholiday = hh as IHoliday;

            if (hh is IHoliday)
            {
                Console.WriteLine("Holiday class implemented IHoilday");
            }



Comments if you have other possible interview FAQ for Interface in C#.

Thursday 24 October 2013

SQL Server Inetrview FAQ 3

1.       How to find the maximum no of connection allowed in SQL Server?

 Select @@MAX_Connections

2.       How to select top 2 rows without using top?

 It can be achieved by using rowcount. For example
SET ROWCOUNT 2
SELECT *from tblName

3.       What is the purpose of SET ANSI NULLS ON?

SET ANSI NULLS ON is used to follow ANSI standerds.So if you are working with distibuted queries running across multiple server,You need to SET ANSI NULLS ON,to maintain compatibility for all servers.
For example: We should not use <> or != for checking NULL condition,It should be is NULL or is NOT NULL as per ANSI standerds.

4.       How to insert Multiple Rows in single query?

We can use Row Constructor as an example
INSERT INTO TABLENAME(COL1,COL2,COL3)
VALUES
('VAL1','VAL2','VAL3'),
('VAL11','VAL22','VAL33'),
('VAL111','VAL222','VAL333')

5.       Which type of column we can’t update using UPDATE?

TIMESTAMP type of column can’t be updated.

6.       How can you apply restrictions on database objects?

We can create constraints, triggers or rules and defaults to apply restrictions. Constraints are better than triggers and rules. Triggers and rules should only be used if constraints are not an option because triggers make overhead on system.

7.       CAST vs. Convert

Convert does everything that CAST does. The only difference is that CAST is ANSI/ISO compliant while CONVERT is not.

8.       What is the default port no of SQL server

SQL Server listen TCP port 1433 by default.

9.       What are DMVs?

DMV: Dynamic Management Views are used to monitor server state information as health of server instance, performance, connections.
For example:
SELECT * FROM sys.dm_os_wait_stats;
It will return operating system wait states.
SELECT * FROM sys.dm_exec_sessions;
It will return cureent sessions. Some other DMVs are
·         dm_broker_connections
·         dm_broker_forwarded_messages
·         dm_broker_queue_monitors
·         dm_cdc_errors
·         dm_cdc_log_scan_sessions
·         dm_clr_appdomains
·         dm_clr_loaded_assemblies
·         dm_clr_properties
·         dm_clr_tasks

10.   OLTP vs OLAP

OLTP: Online Transaction Processing (It is used for usual applications).Most of applications are OLTP based. It emphasizes on Update.

OLAP: (Online Analytic Processing)It is used for multidimensional queries and better approach for MIS and decision making systems. In Business Intelligence OLAP used. It emphasizes on Retrieval.

Saturday 19 October 2013

Cumulative SUM in SQL Server


Sometimes we need to find sum of first and next row in cumulative way.

Create table and insert data:

CREATE TABLE [dbo].[testsum](
      [name] [varchar](10) NULL,
      [val] [int] NULL,
      [ID] [int] NULL
) ON [PRIMARY]

insert into [testsum] (id,name,val)
values(1,'A',10),
(2,'B',20),
(3,'C',30)

Required Output:
ID    name  val   cumSum
1     A     10    10
2     B     20    30
3     C     30    60

To find cumulative sum first you need to self join on condition >=

select t1.*,t2.* from testsum t1 inner join testsum t2 on t1.ID>=t2.ID

output after join.
t1
t2
ID
name
val
ID
name
val
1
A
10
1
A
10
2
B
20
1
A
10
3
C
30
1
A
10
2
B
20
2
B
20
3
C
30
2
B
20
3
C
30
3
C
30

Group by ID and SUM.

select t1.id, t1.val, SUM(t2.val) as cumSum
from testsum t1
inner join testsum t2 on t1.id >= t2.id
group by t1.id, t1.val
order by t1.id

t1
t2
ID
name
val
ID
name
val
1
A
10
1
A
10
2
B
20
1
A
10
3
C
30
1
A
10
2
B
20
2
B
20
3
C
30
2
B
20
3
C
30
3
C
30

We reach to output:

id
val
cumSum
1
10
10
2
20
30
3
30
60