Tag: SQL Server

SQL Server – Policy Based Management

SQL, SQL-Advanced, SQL-General, Varinder Sandhu

SQL Server 2008 has a new feature called Policy Based Management, also know as Declarative Management Framework (DMF), to tackle the problem of standardizing your SQL server instance. Although Policy Based Management can be used just to alert an administrator when an object is out of compliance, depending upon the type of policy, you can also enforce compliance by preventing changes that would violate a policy. Policy Based Management introduces the following new objects that are used to design and

» Read more
, ,

SQL Server – Distributing and Partitioning

SQL, SQL-Advanced, SQL-General, Varinder Sandhu

Key Points of Distributing and Partitioning Partition allow you to divide a table or index into multiple filegroups. Table and index are partition horizontally, based on rows by specifying a partitioning column. To create a portioned table or index you need to perform the following actions: Create a partition function Create a partition schema mapped to partition function Create a table or index on the partition schema SPLIT function to add a new boundary point and hence partition. MERGE function

» Read more
, , ,

SQL Server – Backup Type for Each Recovery Model

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-General, Varinder Sandhu

Before looking about Backup type for each Model, sharing with brief information about Recovery and Recovery Model Recovery Recovery options determine the behavior of transaction log and how damaged pages are handled. Recovery Model Every database within a SQL server a SQL server instance has a property setting called recovery model. The recovery model determines the types of backups you can perform against a database. The recovery models available in SQL server 2008 are: Full Bulk-Logged Simple

» Read more
, , , ,

SQL Server – Backup all the sql server databases

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Sharing with you script to backup all the SQL server databases Script: DECLARE @name VARCHAR(50) — database name DECLARE @path VARCHAR(256) — path for backup files DECLARE @fileName NVARCHAR(256) — filename for backup DECLARE @fileDate VARCHAR(20) — used for file name SET @path = ‘D:Backup’ SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),113) SELECT name,flag=0 INTO #temp FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) –according to requirement we can filter databases in where clause SET ROWCOUNT 1 WHILE (exists(SELECT * FROM #temp WHERE flag=0))

» Read more
, ,

SQL Server – Understanding the System Databases of SQL Server

SQL, SQL-Advanced, SQL-General, Varinder Sandhu

We all know there are some system databases available in SQL Server. But we should have understanding about system databases. Here just try to give you understanding of system databases very briefly. There are four system databases available in SQL Server 2008 Master Model msdb tempdb Master Database The master database records all the system-level information for a SQL Server system. The master is the database that records the existence of all other databases and the location of those database

» Read more
, , , , , , ,

SQL Server – Linked Server – Mapped all users with one statement

SQL, SQL-General, SQL-Linked Server, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Hello Friends, I have setup the Linked server with the help of script here. My one of colleague helps me to write the script with we can map all user with one statement. This script will map all local SQL server users with remote user that you provide in the script. You don’t need to map all user manually. Script: EXEC master.dbo.sp_addlinkedserver @server = N‘dsql2k’, @srvproduct=N”, @provider=N‘SQLOLEDB’, @datasrc=N‘servername’ EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N‘dsql2k’,@useself=N‘False’, @locallogin=NULL,@rmtuser=N‘sa’,@rmtpassword=‘XXXXXX’ if anybody have any other experience, please share as

» Read more
, ,

Transaction Isolation Level

Crystal Report, SQL, SQL-Advanced, Varinder Sandhu

What is default Isolation level for SQL Server 2008 or Crystal Report? Many people have same above question about Isolation Level, may we have same question. So here share the brief answers of this question. Transaction Isolation Level: In database systems, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations. Default Isolation level for SQL Server 2008 is “Read Committed” SQL Server 2008 has following transaction isolation level Read Committed

» Read more
, , ,

Welcome SQL Server 2011-Denali – CTP1

SQL, SQL-General, SQL-Server 2011, Varinder Sandhu

Dear friends, A new release of SQL Server is available, and if you follow blogs or tweets or check Microsoft’s SQL Server website. You might have seen mention of the new version, code-named “Denali” SQL Server 2011 – CTP1 Released. CTP stands for Community Technology Preview It’s available in both 32-bit and 64-bit version. May you are getting excited about the new version. So that‘s why I am sharing with you all… What‘s New SQL 2011 Microsoft SQL Server 2011

» Read more
, ,

Query Optimization with Linked Server

SQL, SQL-Linked Server, Varinder Sandhu

Hello Friends, I have faced a problem in SQL queries with Linked server; I just want to share with you all. Let me explain with example as below Say, we have set of applications which are running on SQL 2000, due to some reason we need to migrate sub set of applications to SQL 2008. Now the few applications running on SQL 2008 and few on SQL 2000, applications running on SQL 2008 using some of databases which lie on

» Read more
, ,

Add the value into an Identity Column in SQL Server

SQL, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. But sometime Identity columns  missed the value, we want to insert missed value into the column. For that we need to enable IDENTITY_INSERT for the table. USE mytempDB GO CREATE TABLE myTable ( myIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY, myValue NVARCHAR(30) NOT NULL ) GO INSERT myTable(myIdentity, myValue) VALUES (5, ‘Varinder Sandhu’) –Result =  Error because IDENTITY

» Read more
, , ,
1 … 6 7 8 9