10.5.13

SQL Injection avoidance in SQL Server


Intro about SQL Injection :
             
         [ Content taken from my company documentations]

        The expansion of the Internet has made web applications become a part of everyday life. As a result the numbers of incidents which exploit web application vulnerabilities are increasing. A large percentage of these incidents are SQL Injection attacks which are a serious security threat to databases with potentially sensitive information. Therefore, much research has been done to detect and prevent these attacks and it resulted in a decline of SQL Injection attacks. However, there are still methods to bypass them and these methods are too complex to implement in real web applications. 

         [ Content taken from my company documentations]

A SQL Injection attack takes place when an attacker tries to gain access to a database by supplying special input to the web-site, which in turn sends the innocent input as an SQL-query to the Database Management System. The growing use of web-applications for business purposes has given motivation to attackers to explore the possibilities and exploit these type of attacks.

Am using  a user defined stored procedure which will take care of validating the input values provided by the users.

[code]



CREATE FUNCTION dbo.SQLInjectionCheck_UserDefinedFun
(@VenkatString varchar(max))

RETURNS BIT
AS
BEGIN
DECLARE @Suspect_ValBit bit

SET @VenkatString = ' ' + @VenkatString
IF (PATINDEX('% xp_%' , @VenkatString ) <> 0 OR
PATINDEX('% sp_%' , @VenkatString ) <> 0 OR
PATINDEX('% DROP %' , @VenkatString ) <> 0 OR
PATINDEX('% GO %' , @VenkatString ) <> 0 OR
PATINDEX('% INSERT %' , @VenkatString ) <> 0 OR
PATINDEX('% UPDATE %' , @VenkatString ) <> 0 OR
PATINDEX('% DBCC %' , @VenkatString ) <> 0 OR
PATINDEX('% SHUTDOWN %' , @VenkatString )<> 0 OR
PATINDEX('% ALTER %' , @VenkatString )<> 0 OR
PATINDEX('% CREATE %' , @VenkatString ) <> 0OR
PATINDEX('%;%' , @VenkatString )<> 0 OR
PATINDEX('% EXECUTE %' , @VenkatString )<> 0 OR
PATINDEX('% BREAK %' , @VenkatString )<> 0 OR
PATINDEX('% BEGIN %' , @VenkatString )<> 0 OR
PATINDEX('% CHECKPOINT %' , @VenkatString )<> 0 OR
PATINDEX('% BREAK %' , @VenkatString )<> 0 OR
PATINDEX('% COMMIT %' , @VenkatString )<> 0 OR
PATINDEX('% TRANSACTION %' , @VenkatString )<> 0 OR
PATINDEX('% CURSOR %' , @VenkatString )<> 0 OR
PATINDEX('% GRANT %' , @VenkatString )<> 0 OR
PATINDEX('% DENY %' , @VenkatString )<> 0 OR
PATINDEX('% ESCAPE %' , @VenkatString )<> 0 OR
PATINDEX('% WHILE %' , @VenkatString )<> 0 OR
PATINDEX('% OPENDATASOURCE %' , @VenkatString )<> 0 OR
PATINDEX('% OPENQUERY %' , @VenkatString )<> 0 OR
PATINDEX('% OPENROWSET %' , @VenkatString )<> 0 OR
PATINDEX('% EXEC %' , @VenkatString )<> 0)
BEGIN
SELECT @Suspect_ValBit = 1
END
ELSE
BEGIN
SELECT @Suspect_ValBit = 0
END
RETURN (@Suspect_ValBit)
END
GO

 -----------     -----------    
SELECT dbo.SQLInjectionCheck_UserDefinedFun
('SELECT * FROM HumanResources.Department')

--------------------- The result is "0"--------------------- 

SELECT dbo.SQLInjectionCheck_UserDefinedFun
(';SHUTDOWN')

--------------------- The result is "1"--------------------- 

SELECT dbo.SQLInjectionCheck_UserDefinedFun
('DROP HumanResources.Department')

--------------------- The result is "1"--------------------- 

[/code]


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech,
www.kaashivinfotech.com
A very best Inplant Training and Internship Providers in Chennai

Ntile feature in SQL Server


I got a peculiar query from one of the user to fetch data from the tables.

 Here is the query, He needs to fetch 50% bottom data to the top and 50% top data to the bottom  of the table.      

  I've used Ntile option to manipulate the desired output.


create table #t(col1 varchar(6),col2 varchar(6))
--drop table #t
insert into #t values('a','s')
insert into #t values('a','g')
insert into #t values('b','h')
insert into #t values('b','r')
insert into #t values('s','j')
insert into #t values('s','k')
insert into #t values('e','o')
insert into #t values('e','p')
insert into #t values('q','x')
insert into #t values('q','c')
select * from #t

Select col1,col2 From (
    Select NTILE(2) Over (Order by col1 Desc) as HalfNumber, *
    From #t) as NtileTable
where HalfNumber = 1
union all
Select col1,col2 From (
    Select NTILE(2) Over (Order by col1 Desc) as HalfNumber, *
    From #t) as NtileTable
where HalfNumber = 2

Cheers,


Venkatesan Prabu .J
Head, KaaShiv InfoTech
A very best Inplant Training and Internship Providers in Chennai

Execute Stored Procedure inside another Stored Procedure



A small piece of code to execute procedure inside another procedure


create table venkat_Table( id int,
val varchar(1000) )

insert into venkat_table values (100,
'Venkat From KaaShiv InfoTech')

create procedure VenkatFirstProc
as begin
  select * from venkat_Table
end 


create Procedure VenkatSecondProc
as begin

  exec VenkatFirstProc
  
  end
  
  exec VenkatSecondProc

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

2.4.13

SQL Server division returns zero


While trying a small piece of code. I've happened to face a problem with division by any value on an integer is returning zero. 


Tried changing the destination value as decimal but still no effect.






print  @PrevEff  print @PrevCost print @PrevUsefulCost  
print @CurrEff
declare @finalcost decimal(10,2)
set @finalcost = @PrevEff  / @PrevUsefulCost  
print @finalcost


----------The above statements provided 0 as the output.....


Now, I've tried changing the manipulation part as Casting to float and process it... OOPS... it's working like a charm.....

print  @PrevEff  print @PrevCost print @PrevUsefulCost  
print @CurrEff
declare @finalcost decimal(10,2)
set @finalcost =  CAST(@PrevEff AS float) / CAST(@PrevUsefulCost  AS float)
print @finalcost


 Cheers,
Venkatesan Prabu .J

1.4.13

MDX Query to fetch the Measures available in the cube


Here is the query to fetch the possible measures available in the Knowledge Cubes 



SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
    [MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
 WHERE CUBE_NAME  ='MDXProjectCube'
 ORDER BY [MEASUREGROUP_NAME]






MDX Query to fetch the possible attributes in a Cube

Here is the MDX query to fetch all the possible attributes related to a cube in the database.


SELECT [CATALOG_NAME] as [DATABASE],

      CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
      LEVEL_CAPTION AS [ATTRIBUTE],
      [LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME],
      [LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME]
FROM $system.MDSchema_levels
WHERE CUBE_NAME  ='MDXProjectCube'  
AND LEVEL_NAME <> '(All)'
order by [DIMENSION_UNIQUE_NAME]





MDX Query to fetch all the Cubes in the database

Here is the query to fetch the available cubes in the SSAS database.


SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME
FROM $system.MDSchema_Cubes
WHERE CUBE_SOURCE=1



Cheers,
Venkatesan Prabu.J

Fetch tables from Knowledge Cubes in SQL Server Analysis Services

I've just found a new way of accessing the tables from the cube. Below is the query used to fetch the tables linked with a cube.



SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS 
WHERE [CUBE_NAME]='SourceDataView'

In addition, this view provides more information about the dimensions, table involved in the dimensions.


Cheers,
Venkatesan Prabu .J

23.2.13

Collation Conflicts and Settings in SQL Server


System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the UNION operation.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 m

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

Solution: The issue is with the collation settings of the database Right click on the database options and change the collation. Your query will work like a charm.

:-)

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

18.2.13

Error with MODI in C#


Here is one of the sample error received while using MODI library.

Retrieving the COM class factory for component with CLSID {40942A6C-1520-4132-BDF8-BDC1F71F547B} failed due to the following error: 80040154.

Solution:

  1. May be the libraries were not registered properly in the system (Interop.Modi)
  2. Microsoft document Imaging software is not installed properly in your system. Run your Microsoft office setup  -> go to add/remove features -> Microsoft office features- > Microsoft document imaging. Right click and set the options to install in the local machine.


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech


17.2.13

Cross join feature in SQL Server



One of the superb feature of sql server is application of cross joins. 

It provides an option of multiple select statement into a single insert statement. I've happened to work in sql server for getting lot of informations from different select statement and I need to insert into a single insert statement.. In this case, my cross join helps me a lot.

 Here is the syntax for the same.

SELECT
  f1.col1, f2.col2, f3.col3, f4.col4
FROM
  (select count(1)  AS col1
 from SystemBasedUser() where ID like '%MS_SQL%') AS F1
 CROSS JOIN
 (select count(1) AS col2
 from SystemBasedUser() where ID like '%MS_POLI%') AS F2
 CROSS JOIN
 (select count(1) AS col3
 from SystemBasedUser() where ID like '%MS_SMO%') AS F3
 CROSS JOIN
 (select count(1) AS col4
 from SystemBasedUser() where ID like '%MS_AGEN%') AS F4

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

Security related information - System logins in sql server

Here is the query to fetch security related information from the master database:

The below query will fetch and retrieve the security related information from the database. It will provide the list of users and their access nature. Execute it and see the magic :-)

SELECT
[name] as LoginName,
LOGINPROPERTY ([name] , 'DefaultDatabase') as DefaultDatabase,
LOGINPROPERTY ([name] , 'DaysUntilExpiration') as DaysUntilExpiration,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsExpired'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsExpired,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsLocked'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsLocked,
LOGINPROPERTY ([name] , 'PasswordLastSetTime') as PasswordLastSetTime,
LOGINPROPERTY ([name] , 'PasswordHashAlgorithm') as PasswordHashAlgorithm
FROM master..syslogins



Cheers,
Venkatesan Prabu .J 
Head, KaaShiv InfoTech  

14.2.13

Fix: f5 is not working in windows forms

Problem f5 is not working in windows forms - Unable to start the windows forms :

   Recently, I've faced this problem while trying to access my windows forms.

  1. Created windows forms
  2. Run the application
  3. Stopped
  4. Re-run the application.

Oops, I couldn't run my app once again. Wait for another 2 minutes. Now, it's running perfectly.

Possible Solution:

  1. May be, Ur windows forms exe is still in the running process. Go and kill the process. 
  2. Check for the Application Experience service. It should run.


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech
www.kaashivinfotech.com

7.2.13

Just for Fun

I've happened to see a joke series in facebook. Wish to share with my blog readers.

Smart Doctor!
Q: Doctor, I've heard that cardiovascular exercise can prolong life. Is this true?
A: Heart only good for so many beats, and that it... Don't waste on exercise. Everything wear out eventually. Speeding up heart not make you live longer; it like saying you extend life of car by driving faster. Want to live longer? Take nap.

Q: Should I reduce my alcohol intake?
A: Oh no. Wine made from fruit. Brandy distilled wine, that mean they take water out of fruity bit so you get even more of goodness that way. Beer also made of grain. Bottom up!

Q: How can I calculate my body/fat ratio?
A: Well, if you have body and you have fat, your ratio one to one. If you have two body, your ratio two to one.

Q: What are some of the advantages of participating in a regular exercise program?
A: Can't think of single one, sorry. My philosophy: No pain...good!

Q: Aren't fried foods bad for you?
A: YOU NOT LISTENING! Food fried in vegetable oil. How getting more vegetable be bad?

Q : Will sit-ups help prevent me from getting a little soft around the middle?
A: Oh no! When you exercise muscle, it get bigger. You should only be doing sit-up if you want bigger stomach.

Q: Is chocolate bad for me?
A: You crazy?!? HEL-LO-O!! Cocoa bean! Another vegetable! It best feel-good food around!

Q: Is swimming good for your figure?
A: If swimming good for figure, explain whale to me.

Q: Is getting in shape important for my lifestyle?
A: Hey! 'Round' is shape!

Well... I hope this has cleared up any misconceptions you may have had about food and diets.

And remember:
Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well-preserved body, but rather to skid in sideways - Chardonnay in one hand - chocolate in the other - body thoroughly used up, totally worn out and screaming "WOO-HOO, what a ride!!"

AND.....

For those of you who watch what you eat, here's the final word on nutrition and health. It's a relief to know the truth after all those conflicting nutritional studies.

1. The Japanese eat very little fat and suffer fewer heart attacks than Americans.

2. The Mexicans eat a lot of fat and suffer fewer heart attacks than Americans.

3. The Chinese drink very little red wine and suffer fewer heart attacks than Americans.

4. The Italians drink a lot of red wine and suffer fewer heart attacks than Americans...

5. The Germans drink a lot of beer and eat lots of sausages and fats and suffer fewer heart attacks than Americans.

CONCLUSION: Eat and drink what you like. Speaking English is apparently what kills you...............

6.2.13

[FIX]Current thread must be set to single thread apartment (STA) mode before OLE calls can be made. Ensure that your Main function has STAThreadAttribute marked on it. This exception is only raised if a debugger is attached to the process.

While working with Windows Application, I've come up with a small error while executing the code. Below is the error string

Current thread must be set to single thread apartment (STA) mode before OLE calls can be 
made. Ensure that your Main function has STAThreadAttribute marked on it. This exception is only raised if a debugger is attached to the process.

Solution 1: Add the key word [STAThread] before the main function
To over come the above specified error. 


   [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Login());
        }
Solution 2: Delete the files available in the bin\debug folder and rebuild the solution.

Solution 3: Check the task manager for the exe running in the background. Kill it. Your application will work as usual.

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech 
www.kaashivinfotech.com

Get File Extension from C#


While working in C#, I came to know about finding the file extension and wish to share the same with my blog readers.....

///// Sample code to get the file extension.
Add the namespace using system.io;
        string filename = @"C:\Users\venkat\Documents\venkat.doc";

 string extension = Path.GetExtension(p);
 if (extension  == ".doc")
 {
      messagebox.show ( " It's a word document ");
 }
Note: Please check the case sensitiveness
Cheers,
Venkatesan Prabu .J
Director - Kaashiv InfoTech - www.kaashivinfotech.com

27.1.13

Dynamic controls using C# in windows forms


Here is the small piece of coding to do some Inheritance concept in C#.

Here is the problem,

  1. I want to write custom code to create controls on my windows forms page.
  2. Instead of Button class, I need to use inheritance to create my own button class and create objects.



  public class MyButton : Button
        {
            public MyButton()
                : base()
            {
                // set whatever styling properties needed here
                ForeColor = Color.Red;
                BackColor = Color.Green;
                 Location = new System.Drawing.Point(269, 96);
            }
        }

        public class MyButton1 : MyButton
        {
            public MyButton1()
                : base()
            {
                // set whatever styling properties needed here
                ForeColor = Color.Red;
                BackColor = Color.Green;
                Location = new System.Drawing.Point(269, 96);
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            MyButton1 btn = new MyButton1();
            btn.Name = "MyButton";
            this.Controls.Add(btn);
        }
    }


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

15.1.13

GridView1_RowCommand event is not firing



Here is a small problem, which I've faced in C# windows forms.



GridView1_RowCommand is not firing

Solution: 

 In this case, you need to go with disabling the view state property of the form.


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech, Chennai

Make 3d chart in C#

Here is the simple steps to enable or include 3d chart in C#

1. Add your chart control in the windows forms.
2. Enable the chart control.
3. As usual, add the code which i have posted in my previous post.
4. Add the below line to modify your 2d chart into 3d chart.


chart1.ChartAreas[0].Area3DStyle.Enable3D = true;

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech
www.kaashivinfotech.com

Error in chart control in C#


Error:
A chart element with the name 'Default' could not be found in the 'SeriesCollection'.

Solution: 

You need to create a series element for a chart before accessing it.

          Series series1 = this.chart3.Series.Add("Default");
            chart3.Series["Default"].Points.DataBindY(myReader1, "OrderQty");
         

Complete coding to draw a chart in C#


  string myConnectionString = @"Data Source=XXXX;Integrated Security=true;Initial Catalog=DBName";

            // Define the database query    
            string mySelectQuery = "SELECT Top 5 OrderQty,ProductID,SpecialOfferID, UnitPrice   FROM [SalesOrderDetail]";

            // Create a database connection object using the connection string    
            SqlConnection myConnection = new SqlConnection(myConnectionString);

            // Create a database command on the connection using query    
            SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);

            // Open the connection    
            myCommand.Connection.Open();

            // Create a database reader    
            SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            Series series = this.chart1.Series.Add("Default");

            chart1.Series["Default"].Points.DataBind(myReader, "OrderQty", "ProductID", "Tooltip=SpecialOfferID, Label=UnitPrice{C2}");
            chart1.ChartAreas[0].Area3DStyle.Enable3D = true;

            // Close the reader and the connection

Cheers,
Venkatesan Prabu. J
Head, KaaShiv InfoTech, Chennai.


14.1.13

get specified format of the date from datetime picker

Here is a minor syntax to extract the date information from datetimepicker in windows forms c#


string VenkatDate = dateTimePicker1.Value.ToShortDateString();
To get the date in specified format:
string VenkatDate = dateTimePicker1.Value.ToString("yyyy-MM-dd");
Cheers,
Venkatesan Prabu. J
Head, KaaShiv InfoTech
www.kaashivinfotech.com

13.1.13

Image Height and width in C#


Code to get the height and width of an image.


System.Drawing.Image objImage = System.Drawing.Image.FromFile("C:\venkat.gif");
width = objImage.Width;
height = objImage.Height;  


Cheers,
Venkatesan prabu .J
http://www.facebook.com/KaaShivInfoTech

Remove drawn object in C# windows forms

Questions:

How can I delete (clear) lines that were made useing Graphics.DrawLine() ?
 
Drawn a circle in windows form. I want the remove the drawn object in C# windows forms.
Pen pen = new Pen(Color.Black, 3); Graphics gr = this.CreateGraphics(); gr.DrawEllipse(pen, 5,5,20,20);


Quick Answer:

this.Invalidate();
 
Cheers,
Venkatesan Prabu . J
https://www.facebook.com/KaaShivInfoTech

My T-SQL Gallery @code.msdn.microsoft


Created my own T-SQL Gallery in Microsoft site. Do visit the same and share your feedback,

http://code.msdn.microsoft.com/VenkatSQLSample/Thread/List.aspx

Thanks and Regards,
Venkatesan Prabu .J

SQL Server Interview questions - Part 1

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.


The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables


What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.


What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

Select Statement in SQL Server

Select Statement in SQL Server

String Functions in sql server

String Functions in sql server
Substring/Len/replace/Ltrim/Rtrim

SQL Server Interview Question - Part 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.


What is a stored procedure:
Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.


What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database


What is a trigger?

Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.


What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.


What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.


What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL ServerClustered -

1. It will format the entire table, inturn physically sort the table.

2. Only one clustered index can be created for a table.

3. Data will be located in the leaf level.

4. By default, primary key will create clustered index on the table.

Non-Clustered Index

1. It wont touch the structure of the table.

2. It forms an index table as reference to the exact data.

3. A reference to the data will be located in the leaf level.

4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Interview question

Extent Vs Page?

Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.The size of the page is 8KB.

Eight consecutive pages will form an extent 8 * 8KB = 64KB.

Thus I/O level operation will be happening at pages level.The pages will hold a template information at the start of each page (header of the page).

They are,

1. page number,

2. page type,

3. the amount of free space on the page,

4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents

2. Mixed extents

Uniform Extents:It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.Mixed

Extents:Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.

Herecomes, our fragmentation and reindexing concepts.



Best Joke - Enjoy it

Best Joke - Enjoy it