How to upload and download Media to or from MSSQL Server Database

This code shows you how to upload media files to the SQL Server Database to store it and then how to download it from the Database in your ASP.NET website using C#. First you have to create a database named “MediaDB” in your SQL Server. Then create a table named “MediaTable” in your newly created database. Now create 4 columns in “MediaTable” as given below- ID (int, not null, IsIndentity: Yes) FileName (text, allow nulls) MediaData (varbinary(MAX), allow nulls) Extension (text, allow nulls) Then save all. Now create a new Visual C# type website in your Visual Studio. Here is … Click here to continue…..

Sql Server Interview Questions and Answers

1) SQL Server query to split the Field into 2 Column. This query the developer can use when the name consists of numeric value like Ram1, Ram2

Note – First 3 Character will be in First Column and the Last One in another Column 2) SQL Server query to returns the index of First numeric in the column

Note – The SQL Server (Transact-SQL) PATINDEX functions returns the location of a pattern in a string. The search is not case-sensitive. 3) SQL server query to remove special characters from a string value. Here all characters except 0-9, a-z … Click here to continue…..

Top Sql Server Interview Questions and their Answers

1)  How to create an empty table emp1 with same structure as emp?

2)  How to find the Max Salary from each department?

3) Retreive year part from the table ..

Similarly for MONTH

4) Get the difference of month of DateTime.

    Similarly for Days

5) Get all Employee details whose Joining Year is 2016.

6) Select all the Employees whose JoiningDate is JAN(1).

7) Split Date, month, Year from DateTime Here is query You can achieve this task in two ways. a) DATEPART b) Year,Mont,Day Let’s see the Query: … Click here to continue…..

Tips to improve performance of Database Operations

1) Return Multiple Resultsets The database code if has request paths that go to the database more than once then, these round-trips decreases the number of requests per second your application can serve. Solution: Return multiple resultsets in a single database request, so that you can cut the total time spent communicating with the database. You’ll be making your system more scalable, too, as you’ll cut down on the work the database server is doing managing requests. 2) Connection Pooling and Object Pooling Connection pooling is a useful way to reuse connections for multiple requests, rather than paying the overhead … Click here to continue…..

Major Points when Creating Indexes for a database

Best practices to follow when creating Indexes for a database. Examine the WHERE clause and JOIN criteria columns : SQL server Query optimizers looks for indexes on the columns specified in the WHERE clause and the JOIN criteria and if the indexes are available on these columns then optimizer considers Indexes when creating execution plan. Use Narrow Indexes : index on a column with narrow data type will improve performance by reducing storage space, reducing required IO and more effective caching. Prefer Column with high selectivity : Index on a column with a very low range of unique value will not yield any benefit in … Click here to continue…..