Posts Tagged ‘SQL Server’

  • 0
    Percentage of Work completed – SQL Server
    Today, I was taking a manual backup of a database through a scheduled Job. Normally this backup job takes about 1 hour to complete, but today it was still executing for almost 3 hours. I started to look up Activity monitor, dbcc commands for any lock against the database, but of no avail. After searching on internet i came across a DMV(Dynamic Management View) provided...
    by Nauman at August 24th, 2010 at 09:08 am
  • 0
    Get indexes on all tables – SQL Server
    An important ingredient in database designing is creating, maintaining and rebuilding indexes after some specified intervals. One main  aspect of database tuning is to apply indexes. Index must exist in databases otherwise their would be a lot of performance issues. While searching across internet, i came across a script that will list all the tables in my database and...
    by Nauman at August 24th, 2010 at 09:08 am
  • 0
    Migrate from MySQL to SQL Server
    Migrate from MySQL to SQL Server with ease. Microsoft has launched a small utility to migrate a database from MySQL to SQL Server. Name of the utility is SQL Server Migration Assistant for MySQL. More details can be found here
    by Nauman at August 23rd, 2010 at 08:08 am
  • 0
    Remove a Stored Procedure in Production Environment
    How to remove a Stored Procedure in SQL Server production environment? How can we be so sure that is not being used any more? You need to know every single path from where this procedure is initiating before you can actually remove it from production. Following are some ways to determine the usage of a procedure in SQL Server. Set up a trace and filter through SQL Server...
    by Nauman at August 23rd, 2010 at 08:08 am
  • 0
    Retrieve permissions on SQL Server database
      SQL Server permissions can be granted to on an object level (schema, database, database objects). In newer versions of SQL Server permissions can be given to user at a more granular level. To retrieve all the permissions granted to a specific user SQL server has a function named: fn_my_permission.  This function is only accessible to system administrator. e.g following...
    by Nauman at August 22nd, 2010 at 10:08 am
  • 0
    How to Improve I/O performance for SQL Server
      If underline I/O architecture system is working properly then every time SQL Server do some transaction (Read/Write) it can do it without waiting. But if load on the system is up, then SQL transaction have to wait for their turns for processing. This can significantly reduce SQL Server performance. There is a much greater issue when you are not in charge of the underlying...
    by Nauman at August 22nd, 2010 at 06:08 am
  • 0
    Import CSV file in SQL Server
    Importing a CSV(Comma Separated File) in SQL server is a very common task, and most of us use the Import /Export feature of SQL server to do this. Import/Export feature has SSIS (SQL Server Integration Services) as it backbone and works great with most of the Databases, file types etc. But today we are going to import a CSV file in SQL server through simple SQL queries....
    by Nauman at May 27th, 2010 at 10:05 pm
  • 1
    Removing Duplicates through SQL Query
    Suppose we have a table in SQL Server, that has duplicates in it. CREATE TABLE T1 ( ProductName varchar(50) ) INSERT INTO T1 VALUES (‘Computer’), (‘Computer’), (‘Printer’), (‘Printer’), (‘Printer’), (‘Scanner’), (‘Scanner’), (‘Scanner’), (‘Scanner’), (‘Camera’), (‘Flash...
    by Nauman at February 14th, 2010 at 09:02 am
  • 0
    SqlBulkCopy – Bulk Insertion in SQL Server from .NET
    Many a times there come a scenario when you have to insert large number of records in sql server. There are number of ways to implement this scenario: through looping techniques to call insert statements for one record at a time. Serialize the data in CSV or XML format and send them as a parameter to a stored procedure and then through parsing execute insertion statements. generate...
    by Nauman at November 5th, 2009 at 06:11 am
  • 1
    Write a Custom SQL Scripter (Generating bulk insert, update statements)
    There are always some static tables in our database that needs to always have some default values in them, for our custom application development. Usually a huge amount of data resides in these tables because they are slowly changing dimension fields. So when it is time to transfer this data the question arises how to work this out. There are many custom tools available...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    Validating string for numeric values in SQL Server
    What to do when you have a large string and you have to validate whether this string only contain digits or not. A straight thing that comes in our mind is to use a looping technique to traverse all the characters in the string and validate each character to be a digit. But how could you implement this in SQL Server? There is an ISNUMERIC function that checks whether a...
    by Nauman at November 5th, 2009 at 12:11 am
  • 0
    SQL Server permissions
    Permissions on data are one of the most critical aspects of database administration. If you’re too strict as a database administrator then your users will not be able to do their jobs. If you’re not lenient, then data can be compromised or even leaked. It is a very fine balance to control. The ability to determine these permissions on your database systems...
    by Nauman at November 1st, 2009 at 09:11 am
  • 1
    How To Calculate the Number of Week Days Between two Dates
    If the start date and end date are both week days, then the total number of week days in between is simply: (total difference in days) – (total difference in weeks) * 2 DateDiff(dd, @start, @end) – DateDiff(ww, @start, @end)*2 … since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number...
    by Nauman at November 1st, 2009 at 09:11 am
  • 0
    SQL Server Version
    If you need to know the version you are currently running of SQL Server you can easily get it by running the following query. SELECT ‘ SQL Server ‘ + CONVERT ( varchar ( 100 ),SERVERPROPERTY( ‘ productversion ‘ )) + ‘ – ‘ + CONVERT ( varchar ( 100 ),SERVERPROPERTY( ‘ productlevel ‘ )) + ‘ – ‘ + CONVERT...
    by Nauman at November 1st, 2009 at 08:11 am
  • 0
    Simple script to backup all SQL Server databases
    Problem Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks. One of these tasks may be the need to backup all databases on your server. This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance...
    by Nauman at November 1st, 2009 at 08:11 am
  • 0
    Demand Paging (Custom Paging) in ASP.NET with SQL Server
    Many a times we need to present bulk data to the user in our day to day application development. Loading all data at one shot is okay when we have few hundreads data but when it comes to thousands of thousands records, it hardly work. In scenario like this we need to go for custom paging in our application. Why Custom Paging? Custom paging allows you to get limited...
    by Nauman at November 1st, 2009 at 08:11 am
  • 0
    Tracking object (Table, SP etc) changes in SQL Server 2005
    DBA’s often need to track the changes being made to database objects such as tables, user defined functions and store procedures etc. Following article aims at providing the readers on how the changes being made to the database can be tracked with minimal effort. To get into details, i would first give preferrance to the "Summary Report" that can be generated...
    by Nauman at November 1st, 2009 at 06:11 am
  • 0
    Debugging Stored Procedures in SQL Serrver 2005
    Pre-requisites 1. Find the .exe file under the directory, C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe or similar path where the SQL Server was installed. The file rdbgsetup.exe stands for ‘RemoteDeBuGsetup’. Look for the emphasis on letters. The filename reads rdbgsetup because, we are going to debug a stored procedures of a database...
    by Nauman at November 1st, 2009 at 06:11 am
  • 1
    Delete duplicate rows from table
    In the above line we are inserting all the distinct row of the "EmployeeTable" to another table "Emp_Temp_Table" (Emp_Temp_Table will create automatically when you use the above query.) Actuall the above query create clone of EmployeeTable and insert all the distinct row inside the Clone Table (Emp_Temp_Table). drop table EmployeeTable sp_rename...
    by Nauman at November 1st, 2009 at 06:11 am