Skip to main content

Easiest way to find row count of each sql tables using queries

Introduction
I was working in a project where I need to compare my Production database and Staging database each time I upload code to the production server.
Being developer it is tedious task to check manually for each table in source and target database as it is taking more time. Using script/query which can get the number of rows from all the tables in selected database can be really effective.

So I was thinkg to check it using query where we can run the query again each database and it returns name of the table and number of rows for each table.
I have prepared a script for that and now let me share that query with my blog readers in this article.

In SQL Server there are tables called sys.tables, sys.sysindexes and many others like this, which stores table related data and all the db object related data for the internal use. So we can write the query on that object to retrieve table rows as below.

MS-SQL Query

SELECT Tables.name AS [TABLE_NAME],
objects.rows AS [ROW_COUNT]
FROM sys.tables AS Tables
INNER JOIN sys.sysindexes AS objects
ON Tables.object_id = objects.id
AND objects.indid < 2
ORDER BY objects.rows DESC

Output
Please check the below image, it is sample output from AdventureWorks2014 database.

It is as simple as above query. In this system related tables there are more useful information stored so we will see those tips and tricks soon.

Please share you feedback using comment.

SQL Server Interview Question and answers

If you are going for an SQL Server Interview or .Net interview then you need to refer below list of questions. Below question and answer will give you a break through. Here I have prepare some simple and tricky questions for SQL Server which will really help you to go through the interview.
1. What is the difference between clustered and a non-clustered index?
– Clustered index is the physical index, It reorders the records physically in the table while storing the data.
– Non-Clustered index is the logical index, It does not reorders the data into table, it’s just set the logical order of the index.

2. What is the difference between UNION and UNIONALL in SQL Server?
– Union and Union All both are used in select statement, Union will remove the duplicate rows from the final result set of the select statement while UNIONALL will not remove duplicate row from the result set and will return all the row. So you can use UNION clause if you want to avoid data redundancy.

3. What’s the difference between a primary key and a unique key?
– Unique key and Primary key both are constraints in SQL Server but has little bit difference, Both keys are not allow to store duplicate value in the database, but Primary key is a cluster index and Unique key is a non clustered index. Another difference is Primary key can not contains any null value while Unique key contains only 1 null value.

4. Alter command to change the data type of a column
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Below is the query to change column datataype

[SQL]
ALTER TABLE tablename ALTER COLUMN columnname data type
[/SQL]

Let’s say we want to change the datatype from int to string of phone number column of employee table then below is query for it.

[SQL]
ALTER TABLE employee ALTER COLUMN phonenumber varchar(15)

[/SQL]

5. In which Files does SQL Server Actually Store Data?
On the disk SQL server has 2 data files associated with each database
1. LDF : Which stores generally transaction log
2. MDF : Which stores actual data

6. What is CHECK Constraint?
– CHECK Constraint used to limit the values that can be inserted in a table column

7. Do you know How many locks are exist in SQL Server ?
– We have these many locks exist here, see below
1. Intent
2. Shared
3. Update
4. Exclusive
5. Schema
6. Bulk Update

8. Can I insert value into a table which having just one IDENTITY column?
– Yes we can, There is a very simple query for that as below

[SQL]
INSERT INTO TABLE1 DEFAULT VALUES;
[/SQL]

9. How to drop primary key from a column using Query
– It’s very simple you can use Alter table command for that, see the below query to drop primary key constraint
ALTER TABLE tablename DROP CONSTRAINT PK_tablename_columnname
in above query PK_tablename_columnname is the name of primary key constraint.

10. What is a DDL trigger and a DML trigger?
– DDL commands are used to create, modify or delete the structre of the table, like creating table, alter table or drop table, so we can easily say those are the command which deal with the table structure. Those commands are CREATE, ALTER and DROP
– DMS commands are used to insert, update or delete the data of in to the table, like insert into, update and delete from, so those are the commands which deal with the table data, Those commands are INSERT, UPDATE and DELETE.

11. What is the difference between Trigger and Stored Procedure?

– Triggers and Stored procedure are generally same but the only single difference is that trigger cannot be called directly, it can be called on either insert,update or delete statement of the table based on trigger we define.

12. How can you delete duplicate records in a table where there is no primary key?
– You can take Use of the SET ROWCOUNT command. Here if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.

13. How to check the version of SQL server and operating system?
– With the help of the following query we can get it

[SQL]
SELECT SERVERPROPERTY (‘productversion’) as Version, SERVERPROPERTY (‘productlevel’) As Level, SERVERPROPERTY (‘edition’) as Edition

[/SQL]

14. What is NOT NULL Constraint?
– NOT NULL is the constraint in the SQL Server. When ever we want to force someone to enter data into particular table column then we need to set it NOT NULL column so it will not accept NULL value.
15. Find the 3rd MAX Age of the employee in the emp table
– With the help of the following query we can get 3rd Max Age
Select distinct age from emp e1 where 3 = (select count(distinct age) from emp e2 where e1.age <= e2.age);

16. How to copy data from one table to another table?
– With the help of ‘INSERT INTO SELECT’ or ‘SELECT INTO’ queries you can copy data from a data-table to another

17.What is the Sixth normal form in SQL server?
– Actually Sixth normal form exist but use it when you want a relational system in conjunction with time. At this moment SQL Server does not support it directly.

18.What is SQL Profiler?
– It is SQL Server tool that facilitate administrator to monitor different events and transaction of particular SQL server instance, You can capture and save data about each event to a file or SQL Server table to analyze later.
It is not available with EXPRESS edition

19. What is PIVOT in SQL Server
– To sort, total and count the data stored in 1 table automatically we use PIVOT. It will also rotate table as a columns to rows and rows to column.
– To automatically sort, count, and total the data stored in one table we use PIVOT. It will also rotate table as rows to columns and vice versa

Please let us know your suggestion/queries

Interview Questions and Answers for ASP.NET MVC

Nova days ASP.NET MVC is getting more and more popular. ASP.NET MVC has a very good feature called Modal-View-Controller architecture so it is more popular.
In this article we will see the some Question and Answer for ASP.NET MVC which will help you to make your asp.net mvc concept clear in short time.
We will keep posting more and more article in this interview series so this will be your unique place to find interview related questions answer.
Now let’s start some important interview question answer related to ASP.NET MVC.

1. What is ASP.NET MVC ?
Most commonly asked question in the interview, ASP.NET MVC is an architectural pattern which separates the UI and code. Mainly it’s divided into 3 Parts Model, View and Controller.
-Model : Model is a real word object, (Database table) and it’s provides data to the View.
-View : View is presentation layer, means it is responsible for the look and feel, We can write html here.
-Controller : Controller is the key part of the architecture, it is mainly responsible for handling request and load the data into model and call the view by passing Model.

2. What is Routing in ASP.NET MVC?
In Classic WebForm development we were doing URL rewriting using web.config file or using third party library. But in ASP.NET MVC it is very easy. Using routing we can display user friendly URL in the browser.
ASP.NET MVC framework using routing engine, which maps URL to Controller/Action. We can define routing rules for URL, so using incoming URL routing engine can send request to proper controller and action method.
When first request come to the routing engine it will collect the URL and check for the routing table, if any proper route found for incoming URL it will redirect request to that route else it will redirect to default route.

3. What are the different ActionResult available in ASP.NET MVC?

There is a main 1 Class call ActionResult but it has many sub classed as below

1. ViewResult – Get the response and render specific view.
2. EmptyResult – Return an empty result
3. ContentResult – Return content(plain text) to the response
4. JsonResult – Return given ViewData object to JSON format
5. PartialViewResult – Renders a specific but partial view to the response
6. RedirectResult – Returns an HTTP redirection to a given URL
7. RedirectToRouteResult – Depending on the routing engine get the URL and redirect the result
8. JavaScriptResult – Return code piece of JavaScript that can later execute on client machine
9. FileContentResult – Returns a file with content to the client
10.FileStreamResult – Returns a file to the client but in the form of a Stream
11.FilePathResult – Returns a file with path to the client

4.What is Web API ‘s in ASP.NET MVC ?
– Web API is a new framework developed by Microsoft to build RESTFul services on HTTP, It is very compatible to call from multiple browsers like web browsers,desktop,mobile, smart phones, tablets etc.

5. What are the display Modes in Asp.Net MVC?
I am never used it till now but what I know about this is Modes are automatically selected, If we are viewing from desktop browser then application return URL
as ‘Views/Authentication/Register.cshtml‘ and for the same request in the mobile browser it will redirect to ‘Views/Authentication/Register.mobile.cshtml

6. What is Razor in ASP.NET MVC?
The word Razor is used to as a cleaner, so it is just a new style of coding to make it faster. In simple word Razor is a View Engine. In Asp.Net MVC there are major 2 view engine Razor and Asp.Net view.
It is recommended we use Razor view.
See the syntax for both view engine as below

// In Asp.Net View Engine if we want to display a variable
<div>
<% foreach (var item in Model)
{ %>
<span> <% item.Name %> </span>
<% }%>
</div>

//in ASP.MVC, Razor View if we want to display a variable
<div>
@foreach (var item in Model)
{
<span> @item.Name </span>
}
</div>

7. Is TempData can preserve values between multiple request?

This is very tough question to answer because the answer of this question is YES and NO as well,
Now let me explain how is that
Answer is Yes if TempData variable value is not accessed and you are on multiple request.
Answer is No if TempData variable value is accessed once then it will be null

8. What is TempData in ASP.NET MVC?
TempData is a dictionary type object but it used to access data between multiple redirect, from one controller to another controller or from 1 action to another action.

For example see the below code blog.

//Set TempData in first controller "Category"

public ActionResult Index()
{
var CategoryModel = new Category()
{
Name = "Electronics",
};
TempData["Cat"] = CategoryModel;
return RedirectToAction("Index","Product");
}

//Access TempData in second controller "Product"
public ActionResult Index()
{
var CategoryModel= TempData["Cat"];
return View(CategoryModel);
}

9. What is ViewBag in ASP.NET MVC?
ViewBag is very similar to ViewData and used to maintain state and transfer values between controller to view and next subsequent request, the main difference is
ViewBag doesn’t require a type casting for complex data type because it is dynamic type object so we can use it without type casting. ViewBag and ViewData has a very short life, so it will be null if we redirect to another view or page.
For example see the below code blog.

// Set ViewBag in Controller
Public ActionResult Index()
{
ViewBag._VarName = "This string is set from Controller";
Return View();
}

//Use ViewBag in View
@ViewBag._VarName

10. What is ViewData in ASP.NET MVC?

It is passed to data from controller to view and also in the next subsequent request. It is type of Dictionaty object, so it can save any kind of data with a key value pair including user defined data type. We can set the data in controller and use it in the view.
If it is other then string datatype we need to type case it before we use it to avoid error.
For example see the below code blog.

// Set ViewData in Controller
Public ActionResult Index()
{
ViewData["VarName"] = "This string is set from Controller";
Return View();
}

//Use ViewData in View
@ViewData["VarName"];

11. How to transfer values between View and Controller in ASP.NET MVC?
There are multiple ways using that we can transfer values between View and Controller in ASP.NET MVC as below.
1. TempData : Long term data storage as compare to ViewBag and ViewData and is of dictionary type.
2. ViewBag : Short term data storage as compare to TempData and is of dynamic types.
3. ViewData : Short term data storage as compare to TempData and is of Dictionary type.
4. Hidden Fields : It is used to transfer value from View to Controller.
5. Session : It is very power full and dynamic ways to transfer values in Asp.Net MVC, it supports value transfer between Controller to View, View to Controller and Controller to Controller

12. How to navigate request from One view to another ?
It’s very simple you can place a Hyperlink using ‘ActionLink’ Method of Html helper to navigate to destination action.
For example see the below code blog.

<%= Html.ActionLink("ActionName","ControllerName") %>

13. What is Model in Asp.Net MVC?
Model is the main object which retrieves the data into it from database and saved back to the database after setting value to it. In addition to that, if we are not using ViewModel then
it will be useful to validation part and also used to handle business logic if we are not using any business layer. But it is recommended to use separate business logic layer for that and use a ViewModel to handle validation part.

14. Can We use ASP.NET MVC in Windows application?
No. Model, View, controller framework is used to create web applications only.

15. What are the important features of ASP.NET MVC?

– ASP.NET MVC supports Caching, authentication and authorization, Membership and Roles.
– It supports Test driven development using test project.
– It clears the concept of separation of application concerns.

16. What is minification and how it is helpful?
Minification is the process of reducing the file size for CSS and JavaScript, It is removing blank space, comments etc. It also remove white space from CSS and JS
file. Nova days we are using lots of CSS and JS files into our project which increase the page-size thus it’s take time to load on the browser and reduce the performance, but using minification we can reduce the overall size of CSS and JS code so site will load faster.

See below code example how minification reduce the file size.

// Below is the code for what we are writing JavaScript
var firstName = "Tarun";
var lastName = "Dudhatra";
var fullName = firstName + " " + lastName;

//Once we minified above code it will looks like below code snippet

var firstName="Tarun";var lastName="Dudhatra";var fullName=firstName+" "+lastName;

Now with above code you can see that how our JS code reduced.
17. What is Partial View and how it will helpful?
In Asp.Net WebForms we were using User Control for common view or look and feel, in Asp.Net MVC it is replaced with a Partial View. We can call it multiple times or we can use same view for different different page.
There are some method using which we can render our partial view on the page like View.Partial and RenderPartial method.

For example see the below code block

<span>@Html.Partial("_partialViewName")</span>
<span>@{Html.RenderPartial("_partialViewName")}</span>

Summary
In the above article we have seen very basic and frequently asked Question & Answer, In the upcoming article we will see some more questions.

If you have any comments, suggestions or doubts, please feel free to leave as a comment

Best of Luck