Category DataBase

Important Date Function In Sql Server 2008

Here are the some of important date function which i have worked out last week in order to write some Sp’s for my project. Start and End of Today SELECT DATEADD(DD,DATEDIFF(DD,0, GETDATE()),0) as [Start Of Day] DATEADD(s,-1,DATEADD(dd, DATEDIFF(dd,0,GETDATE())+1,0)) as [End Of Day] Start and End of Week SELECT DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0) as [Start Of Week] DATEADD(s,-1,DATEADD(WK, […]

Import the data from Excel to Database in sql server 2008

Simple ways to import data from excel to database using import and export wizard of sql server. Step 1: Right Click on the respective database onto which you want to import the data from excel. In our case the name of the database is Demo. Step 2: Click on the Import Data option and you’ll […]

Generating Scripts in Sql Server 2008 R2

In this article i would like to share how to generate script file in sql server 2008 r2. Hope it will help mostly to freshers. In order to see the full and clear image click on the respected images. Step 1: Right click on the database from which you want to generate the script. You’ll […]

Difference between stored procedure and function

I find some of the difference between SP and function in sql server 1) Procedure can return zero or n values whereas function can return one value which is mandatory. 2) Procedures can have input, output parameters for it whereas functions can have only input parameters. 3) Procedure allows select as well as DML statement […]

Sql Server Database Objects

Many people think a lot of hell number of things when any interviewer asks the question what are the different objects of sql server Database. Well the answer is very simple no need to think about more on this. All the concepts that are there in the database is act like database objects and following […]

Query to get last modified procedures or table names in sql server

These are the some basic sql queries which helps during the development process. In order to get top 10 procedures names which are modified recently you can use SELECT top(10)OBJECT_NAME(object_id) as name,modify_date,type_desc,create_date FROM sys.objects WHERE type = ‘P’  Gives you last 10 modified procedure names SELECT top(10)OBJECT_NAME(object_id) as name,modify_date,type_desc,create_date FROM sys.objects WHERE type = ‘U’ […]

Difference between ISNULL and COALESCE

ISNULL ISNULL  function returns null value with replacement value. Syntax : ISNULL(expression_value,replacement_value) It will take only 2 parameters or arguments.First it will check the expression value if its null then it will return replacement value. Eg : Declare @value varchar(10) Set @value = 10 Select ISNULL(@value,’n/a’) as Demo In the above scenario the output is […]

Getting all the table names from Database

I was creating some POC on migrating the data from Sql Server to MongoDB that time i faced this problem how to get all table names from a particular database in sql server 2008 and i came up with this query SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ After executing this query you […]

How to increase the performance of Stored Procedure

To increase the performance of stored procedure we need follow certain steps Ref: 1. Include SET NOCOUNT ON statement CREATE PROC dbo.ProcName AS SET NOCOUNT ON; –Procedure code here SELECT column1 FROM dbo.TblTable1 — Reset SET NOCOUNT to OFF SET NOCOUNT OFF; GO 2. Use schema name with object name SELECT * FROM dbo.MyTable — […]

Bind Data to ASP.Net gridview using MongoDB

I have created a simple demonstration program to show how we can bind data from Mongo to gridview. Here we go through code… Create Database Connection /*create separate for connection object */ using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Configuration; using System.Data.SqlClient; using MongoDB.Driver; using MongoDB.Driver.Builders; using MongoDB.Bson; namespace Gridcontrol.Mongodb { public static […]