-
It’s fairly simple, create a CLUSTERED INDEX on the table. While creating a clustered index you need to specify the partition scheme to be used in ON clause of CREATE INDEX statement. We still need to create a partition function and a partition scheme, I have posted earlier about how to create...
-
In most cases we use stored procedures to update or delete rows from related tables. But, you can also cascade the actions using foreign key constraints. This is generally not used due to the fact that it is transparent, and leads to *mysterious* results. Let’s see how it works. Consider two different...
-
While working with linked server as it was filed and giving error as linked server was failed. I think it may be the issue with the username or password on which the linked server communicate. To troubleshoot issue i have tried to change the password , I got best common error. That's it : "Ad...
Posted to
sqlideas
by
Paresh Prajapati
on
08-07-2011
Filed under:
Filed under: sql, tsql, sql server, ms sql, ms sql server, #SQL Server, #sql, database, sql server general, sql errors, SQL Server Problems
-
During the performance issues of database servers and database itself, one of my activity to improve performance of the Indexes. I have created some of the clustered indexes on tables which have only nonclustered indexes and also on some heap tables. Also i have created some additional nonclustered indexes...
Posted to
sqlideas
by
Paresh Prajapati
on
08-06-2011
Filed under:
Filed under: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, t-sql, index, #SQL Server, #sql, sql server 2011, database, SQL Scripts, query
-
Management Studio provides a shortcut for selecting or editing top rows of a table. You can use this by right-clicking table in Object Explorer, and choosing appropriate option: Select Top 100 Rows: This opens a new query for returning top 1000 rows from the table and executes the same. Edit Top 200...
-
Whenever we need to update a column length, we can do the same by using wither Table designer in Management Studio or by using T-SQL. Changing this using Management Studio adds an overhead of recreating the table, which can cause external fragmentation in the database. Let’s compare both these...
-
Problem: Whenever we require aggregate data we use Group by. Using Group by we can get the aggregate data , but if it is require to get the data using different set of columns then we need to write the separate queries using different sets with union all with all queries. Solution: SQL Server 2008 came...
Posted to
sqlideas
by
Paresh Prajapati
on
08-03-2011
Filed under:
Filed under: sql, sql server 2008, tsql, sql server, ms sql, ms sql server, t-sql, #SQL Server, #sql, database, sql server general, enhancements, SQL new features
-
An easiest way to compare two tables is by using UNION, EXCEPT and INTERSECT operators. These operators takes two row sets as input, the output depends on the operator: UNION: produces a new row set by joining both row sets, identifying common rows in input row sets and duplicates are removed, EXCEPT...
-
Sometimes we need to change the name of the database, for example, a database might have been restored using a different name. Whatever the reason may be, this can be easily done via Management Studio or T-SQL. 1. Rename a Database using Management Studio: Step 1: Renaming Database: Right Click the Database...
-
So many things i have learned for the SQL Server and i have posted as "Just Learned on beyondrelational.com." Really Just Learned give us the opportunity to learn, contribute and share so many things for all the technologies. Here i would like give a summary of all the posts for SQL Server...
Posted to
sqlideas
by
Paresh Prajapati
on
08-01-2011
Filed under:
Filed under: sql, sql server, ms sql, ms sql server, new features, mssql, database, SQL new features, CTP, blog, user, JustLearned
-
sys.dm_os_volume_stats is a new dynamic management function introduced in SQL Server 2008 R2 SP1 and Denali CTP3. It returns information about partitions on which database files reside. You can now check for free space on a particular partition, instead of using xp_fixeddrives which does not support...
-
In this article, we will discuss fundamentals of SQL Azure reporting, which as of now is in community technology preview (CTP) and there has been a tremendous interest in this marvelous feature or call it a service, if you will. The response was so great that the CTP invite request was closed! yeah ...
Posted to
Paras Doshi
by
Paras Doshi
on
07-31-2011
Filed under:
Filed under: TSQL, SQL Server, brh, sql, sql server 2008, sql server 2005, #SQL Server, #sql, t-sql, pagination, SQL Scripts, database, sql errors, ms sql, SQL Server Problems, sql server general, cte, ms sql server
-
There are three new server-related DMVs introduced in SQL Server 2008 R2 Service Pack 1 and SQL Server Codename "Denali". 1. sys.dm_server_services: - For SQL Server 2008 R2 SP1, it contains information about SQL Server and SQL Server Agent services for the current instance. - For "Denali"...
-
Last time, i have posted for the new Anaytical functions of Denali CTP3.You can see WHAT IS NEW in SQL Server Denali CTP3. This time i am going to write for new conversion functions which are summarized below, 1. TRY_CONVERT() - Returns a value to be cast to the specified data type if the cast succeeds;...
Posted to
sqlideas
by
Paresh Prajapati
on
07-28-2011
Filed under:
Filed under: sql, tsql, sql server, ms sql, ms sql server, new features, sql server denali, mssql, sql server 2011, database, enhancements, SQL Scripts, SQL new features, Denali, CTP, SQL Server 11
-
LAG() function can be used to access data from a previous row in the result set without using a self-join. And it’s counterpart LEAD() can be used to access data from a subsequent row in the same result set. These functions are introduced in “Denali” as T-SQL functions, these has been...