31.3.12

Representing Infinity in c#

Today, I got some interesting apetite for my Blog readers,


Have you tried bringing infinity in C#.  It's really an interesting feature and a needy one if we are proceeding with mathematical calculation


Here is the small code snippet for the same,

float zero = 0;

float positive = 1 / zero;
textBox1.Text = positive.ToString(); // Outputs Infinity


zero = 0;
positive = -1 / zero;
textBox2.Text = positive.ToString();   //Outputs -Inifinity
 
 
Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

Issue in accessing the sdf file - sql server compact edition

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Access to the database file is not allowed. [ File name =  ] (SQL Server Mobile Edition ADO.NET Data Provider)

------------------------------
BUTTONS:

OK
------------------------------

Fix: The reason is due to improper access to the user on the sdf file. Solution is to provide necessary access to the user. For safer side, you can push the files to the users my documents folder. Your problem will be resolved.

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

Format of the initialization string does not conform to specification starting at index 0.

Format of the initialization string does not conform to specification starting at index 0.

Problem: The reason for this issue is improper connection string. 

Fix: Please recheck the connection string, it should conform to the standards specified.

Connection string for mobile application is 


        public string MobileConnection(string DBName,string DBPassword)
        {
            string conString = ("Data Source ="
                        + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
                        + ("\\"+DBName+";"
                        + ("Password =" + "\""+DBPassword+"\";"))));
            return conString;
        }

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

www.kaashivinfotech.com

28.3.12

Uncommitted new row cannot be deleted. C#

I got a peculiar issue while trying to delete the records from Datagrid. "Uncommitted new row cannot be deleted."

Reason: The issue is due to the transaction which is not closed.

Solution :  Change the porperty of the data gridview -> AllowUserToAddRows property to false


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

26.3.12

Chief Guest at Sri Venkateshwara College of Engineering Sriperumbudur

One of my remarkable and memorable day in my life - Last week, I've been invited as a chief guest for top rated Engineering institution (Sri Venkateshwara College of Engineering, Sriperumbudur). Am totally amazed on seeing the infrastructure and facilities provided to the students. Reached the college  at 9.30 AM to host their valedictory function and deliver a guest lecture on Latest Microsoft Technologies.


HOD's, Staffs and Students assembled in the hall(Almost 300 people)

 Introductory Note about me:

Latest microsoft products details:


About Windows 8 and Windows Server 8 - Explained about the two new types of authentications, Enhancements in Task manager, Metro Styple GUI etc..,


Dotnet Developer tools:


About Dotnet Framework 4.5:


M they are enjoying my session (Think so :-) )

About WCF:

Silverlight 5 and it's features:


Obviously, How can I miss my Denali:


Certificate distribution for the best candidates.. Oops, it's almost 100 certificates




Group snap with the hOD's, Staffs, Orgnaisers and My Vidhya mam.

It was a wonderful day in sharing the technology and watching the talents in young minds.

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

23.3.12

A potentially dangerous Request.Form value was detected

while trying to execute my script in the front end text box to find the possible script injection in ASP.net. I got the below error,

 

A potentially dangerous Request.Form value was detected from the client (Text="venkat here?").


Description: Request Validation has detected a potentially dangerous client input value, and processing of the request has been aborted. This value may indicate an attempt to compromise the security of your application, such as a cross-site scripting attack. To allow pages to override application request validation settings, set the requestValidationMode attribute in the httpRuntime configuration section to requestValidationMode="2.0". Example: . After setting this value, you can then disable request validation by setting validateRequest="false" in the Page directive or in the configuration section. However, it is strongly recommended that your application explicitly check all inputs in this case. For more information, see http://go.microsoft.com/fwlink/?LinkId=153133.






Exception Details: System.Web.HttpRequestValidationException: A potentially dangerous Request.Form value was detected from the client (Text="Venkat Here").
 
 
Solution:  
 
1. Add the below content in web.config file,
 

 
2. Disabling request validation on a page




 
Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

19.3.12

Issue in DB restore options in sql server


While working with data intensive application, I've face da typical issue with Backup and Restore operation.
I've took the backup, Now am trying to restore the backed up file.. Oops, it's strucked up in the mid.
Around 92%, DB restoration is strucked up.

Started analysing the issue, I've found a typical reason for this issue.

"The log file becomes too huge almost 65GB and while trying to take the backup, the backup file couldn't intake the entire data and log files due to memory constraints. While restoring, the backup file is trying to restore."

Solution: Trucate the log file and take backup and restore the file. Everything will be fine.

USE AA
GO

ALTER DATABASE aa SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(aa_log, 1)
ALTER DATABASE aa SET RECOVERY FULL WITH NO_WAIT

GO


Note: Please take utmost care in production environment. You can't set the recovery model as Simple. In that case, you need to take the backup of the log file. Move it to different location and afterwards try to create a new file.
Cheers,
Venkatesan Prabu .J

17.3.12

Handle the DataError event in datagridview

DataGridView Default Error Dialog

---------------------------
The following exception occurred in the DataGridView:


System.ArgumentException: Parameter is not valid.
at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData)
at System.Drawing.ImageConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.Windows.Forms.Formatter.FormatObjectInternal(Object value, Type targetType, TypeConverter sourceConverter, TypeConverter targetConverter, String formatString, IFormatProvider formatInfo, Object formattedNullValue)
at System.Windows.Forms.Formatter.FormatObject(Object value, Type targetType, TypeConverter sourceConverter, TypeConverter targetConverter, String formatString, IFormatProvider formatInfo, Object formattedNullValue, Object dataSourceNullValue)
at System.Windows.Forms.DataGridViewCell.GetFormattedValue(Object value, Int32 rowIndex, DataGridViewCellStyle& cellStyle, TypeConverter valueTypeConverter, TypeConverter formattedValueTypeConverter, DataGridViewDataErrorContexts context)


To replace this default dialog please handle the DataError event.
---------------------------

Solution: I had my data in varbinary and trying to bind to the data grid.. Thats why problem occured. Modified the data into binary. This issue is resolved.

For more references:

http://social.msdn.microsoft.com/Forums/en/Vsexpressvb/thread/8e7dceaa-ce69-48dd-85e4-b6c6f5f03bd9

16.3.12

System.IO.Path.Combine to merget file path in c#

Just come across an interesting method System.IO.Path.Combine.  Thought of sharing with my blog readers. This method is used to merge the path of the file with another string.

string fileName = "test.txt";
string sourcePath = @"C:\Users\Public\TestFolder";
string targetPath = @"C:\Users\Public\TestFolder\SubDir";
// Use Path class to manipulate file and directory paths.

string sourceFile = System.IO.Path.Combine(sourcePath, fileName);
string destFile = System.IO.Path.Combine(targetPath, fileName);
// To copy a folder's contents to a new location:
// Create a new target folder, if necessary.
if (!System.IO.Directory.Exists(targetPath))
{
System.IO.Directory.CreateDirectory(targetPath);
}
// To copy a file to another location and
// overwrite the destination file if it already exists.


System.IO.File.Copy(sourceFile, destFile, true);

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

15.3.12

Issue with Datetime in SQL Server and C# code

Error String:
SqlConnection _sc = new SqlConnection(conStr);



SqlCommand _scmd = new SqlCommand("Select b.ProductName,Region, Count(UserID) as UserCount from T_DeletedUsers a,T_Products b Where a.CreatedDate <='dxFromDate' and a.ModifiedDate >'dxFromDate' and FK_Products=b.ID Group by Region,b.ProductName union Select b.ProductName,Region, Count(UserID) as UserCount from dbo.T_Users a,T_Products b Where a.CreatedDate <='dxFromDate' and a.ModifiedDate >'dxFromDate' and FK_Products=b.ID Group by Region,b.ProductName", _sc);


SqlDataAdapter _sad = new SqlDataAdapter(_scmd);


_sad.Fill(dtUsers);-------------error raise (System.Data.SqlClient.SqlException was unhandled by user code)
return dtUsers;


}

error msg:
System.Data.SqlClient.SqlException was unhandled by user code
Message=Conversion failed when converting datetime from character string.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=241
Procedure=""
State=1
StackTrace:


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.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
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(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at PeriodMetrics.GetdatewiseUserMetrics(DateTime dxFromDate) in c:\Users\Joseph\Documents\Visual Studio 2010\WebSites\reprt\App_Code\PeriodMetrics.cs:line 41
at reprt2.ASPxButton1_Click(Object sender, EventArgs e) in c:\Users\Joseph\Documents\Visual Studio 2010\WebSites\reprt\reprt2.aspx.cs:line 24
at DevExpress.Web.ASPxEditors.ASPxButton.OnClick(EventArgs e)
at DevExpress.Web.ASPxEditors.ASPxButton.RaisePostBackEvent(String eventArgument)
at DevExpress.Web.ASPxClasses.ASPxWebControl.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
t System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
t System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:
Fix:
It's a straight forward issue and clearly indicates that the issue is resolving around date field.

You need to use convert operation in this area convert(datetime, 'dxFromDate',103) .

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

14.3.12

string to color in c#

Here is the C# code for dynamically assigning the color value for the control

string colorval="Red";
Color color = Color.FromName(colorval);
button1.BackColor = color;
 
Cheers,
Venkatesan Prabu .J

13.3.12

Invalid Object name issue in SQL Server

SSIS Error:

[Execute SQL Task] Error: Executing the query "DECLARE @firstname VARCHAR(1000),@lastname VARCHAR..." failed with the following error: "Invalid object name 'dbo.Geography'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Fix:

  Please check whether you are running in the correct database and whether the specified object is available in your database. I have changed the connection properties(database). It worked fine for me.

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

SSIS error during project loading

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?



TITLE: Microsoft Visual Studio


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

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).


----------------------------------------------------------------------------------------
 
To fix the problem do the following:



1. Open the command prompt
2. Navigate to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
3. Run C:\Program Files\Microsoft SQL Server\90\DTS\Binn>regsvr32 dts.dll
That should fix the problem.




9.3.12

Chieft guest at Meenakshi Engineering College, Chennai

I got an invitation to head their IT department symposium from Meenakshi Engineering College, Placement office. A very good respectable friend of mine.

Intro with the Principal, Dean and placement officer.





Lightning Kuthu Vizhakku


Board members
 Gift and honour by Principal

Releasing Souvenier book


Chief Guest speech

Addressed almost 250 people in the hall

 Awards and Certificates to the best students



My speech on latest trends


Gift from the HOD madam


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

Chief Guest at RVS Engineering College- Dindigul

It was a nice day and a very good experience to be a chief guest for a college symposium (RVS engineering college).  I've travelled a very long distance from chennai- Dindigul. A nice journey and a chit chat with former MS university registrar in pothigai express are  unforgottable moments.




Prayer Song:

Lightning Kuthu Vizhakku

A special momento from CEO of the college.


Chief Guest speech (Of course it's me) - Addressed almost 200 studens in the auditorium



Releasing the Souvenier


Paper presentation judge



Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

8.3.12

Errors in the metadata manager+SSAS error

Here is one of the SSAS error you will face,

Error 12 An error occurred while parsing the 'StorageMode' element at line 1, column 8368 ('http://schemas.microsoft.com/analysisservices/2003/engine' namespace) under Load/ObjectDefinition/Dimension/StorageMode. 0 0


Error 13 Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.SQLSERVER2008R2V\OLAP\Data\OLAP2.0.db\Fact Reseller Sales.5.dim.xml'. 0 0

Solution:
 
  The issue is due to change in the dimensional data holds some conflicts on the existing measures data.
 
In this case,
   1. Go to the specified location and delete this file... reprocess the dimensions and deploy the cube.
   2. Delete the dimension and recreate the dimension. Add it to the cube.
   3.  Final option, delete the Analysis database and try to process and deploy the cube into the server.
 
Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech, Chennai.
http://www.kaashivinfotech.com/

Writing multi dimensional MDX queries in SSAS

Sometimes while executing your MDX query, you will get the below error.

"results cannot be displayed for cellsets with more than two axes "

I want to display ProductID in the Y Axis and Date in the X Axis, sum of count values in the column values or Data values.

Oops, all the mdx books will tell you about rows and columns... am really amazed to see that no one or no sites are explaining the practical issues of writing mdx queries.

Anyway, some how I found the logic to obtain the MDX query. After my discovery, I found similar topic in the below article.

http://www.activeinterface.com/b2005_11_25.html

It may be useful to some one,

 Here is my MDX query,


     SELECT NON EMPTY [OrderDate]     DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,

NON EMPTY [ProductID] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS,

{[Measures].[Sum-Safetystocklevel], [Measures].[Safetystocklevel Count], [Measures].[Maximum Safetystocklevel], [Measures].[Minimum Safetystocklevel]} ON PAGES FROM [VenkatCube]

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

7.3.12

Insert data from other tables using select query

Here is the query to insert other tables data into the existing table in sql server
insert into AdventureWorksDW2008R2.dbo.SafetyStockLevel(productID,safetystocklevel,OrderDate) 
(
SELECT distinct p.ProductID, SafetyStockLevel,OrderDate
from dbo.Product p inner join
dbo.SalesOrderDetail SOD
on sod.ProductID = p.ProductID
inner join dbo.SalesOrderHeader SOH
on soh.SalesOrderID=sod.SalesOrderID
)

Cheers,
Venkatesan Prabu .J

5.3.12

Fix:XML for analysis parser: The localidentifier property is not overwritable and cannot be assigned a new value

SQL Server Analysis Services error:

When I try to browse a cube in either Visual Studio 2008 or the SQL Management Studio, I get the following error: Error HRESULT E_FAIL has been returned from a call to a COM component.Even before you do anything to get that error, the section which would normally say "Drop Totals or Detail Fields Here" instead has a message saying "The query could not be processed: o XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value."

Fix: 

1.  Here is the exact fix to solve this issue,
http://yahawana.wordpress.com/2011/07/06/ssas-localeidentifier-property-not-overwritable-and-cannot-be-assigned-new-value/

  2. Check your SQL Server Browser Services were running.
  3. Change the system locale as well (on Win7: Control panel > Region and Language > Administrative > Change system locale) to en-US, then it works.

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

4.3.12

Sorting Data in C# DataTable

I wish to sort my data in DataTable based on a particular column, before assigning that DataTable to a GridView control. Assume below is my table


Now, the table needs to be sorted in descending order, based on Total Column. Below are the steps to do the same:


  1. Create a clone of the above table.
  2. Specify the Data Type in clone table, for the sort column as needed. Eg. System.Int32 for integer column.
  3. Import each row from original table to clone table.
  4. Commit the changes in clone table.
  5. Create a DataView on clone table.
  6. Specify the sort column and sort order for the  DataView.
  7. Bind  DataView to  GridView.

     DataTable dtMarks1 = dtMarks.Clone();
            dtMarks1.Columns["Total"].DataType = Type.GetType("System.Int32");

            foreach (DataRow dr in dtMarks.Rows)
            {
                dtMarks1.ImportRow(dr);
            }
            dtMarks1.AcceptChanges();


            DataView dv = dtMarks1.DefaultView;
            dv.Sort = "Total DESC";

            GridView1.DataSource = dv;
            GridView1.DataBind();



Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

Operator cannot be applied to operands of type decimal' and 'double'

Once I tried to compare my string with the a decimal value. I got an error indicating the decimal value cannot be compared.


(Convert.ToInt(txt_LowerBound.Text) < 0.001))... Resolved by changing the conversion
(Convert.ToDecimal(txt_LowerBound.Text) < 0.001))
 
Now, I got the next error message.

 
The final change to get this condition work is as below,
 

(Convert.ToDecimal(txt_LowerBound.Text) < 0.001M))

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech
Operator '<' cannot be applied to operands of type decimal' and 'double'

3.3.12

Control one item check in checked list box in c#

Here is a small code snippet for maintaining the checkedlist box control with only one checks.

Problem:

   I want to maintain only one checks in my checked list box in windows forms. But checked list box holds the property of allowing multiple checks. How can I control ,one item check in checked list box in c#

Solution:

private void checkedListBox1_ItemCheck(object sender, ItemCheckEventArgs e)
{

       CheckedListBox.CheckedIndexCollection checkedIndices = checkedListBox1.CheckedIndices;


             if (checkedIndices.Count > 0 && checkedIndices[0] != e.Index)

         {
                      checkedListBox1.SetItemChecked(checkedIndices[0], false);

           }
 }
 
Cheers,
Venkatesan Prabu. J

2.3.12

Convert String to ASCII in C#


Here is a small example to convert String or character to ASCII in c#
const string input = "Venkat";
byte[] array = Encoding.ASCII.GetBytes(input);


foreach (byte element in array)


{


Console.WriteLine("{0} = {1}", element, (char)element);


}


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

1.3.12

Pivoting in SQL Server

Here is a QUERY asked by one of my blog reader. Thought of resolving this issue for him.


respected sir,




tbl1 ID primary key


tbl2 FK_prodct foreign key



How do create for that query.i am very confused that ,plz help ..give me a idea or query...


Here is the sample query to retrieve the output,


CREATE TABLE PRODUCTS(ID INT , NAME VARCHAR(100))
INSERT INTO PRODUCTS VALUES (1,'CAD')

INSERT INTO PRODUCTS VALUES (2,'PTC')




CREATE TABLE USERS(ID INT, PRODUCT VARCHAR(100))

INSERT INTO USERS VALUES (1,'ASIA')

INSERT INTO USERS VALUES (1,'ASIA')

INSERT INTO USERS VALUES (2,'ASIA')

INSERT INTO USERS VALUES (2,'EUROPE')


select * from users



select * from

(

select * from

(
select p.id, name, PRODUCT
from dbo.PRODUCTS p inner join

USERS SOD

on sod.id = p.id

)p

pivot

(count(name) for PRODUCT in ([ASIA],[EUROPE]

))

as pvt1

)tbl1


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

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