-
Square braces in SQL Server play a major role in T-SQL programming. When an object name contains a space, special character, etc, the only way to express them is to put them aroud squre braces. Consider that you want to create a table user master (with spaces between user and master), you can use [user...
-
During working with one logic, i got chance to work with PIVOT operation. Sometime we need do require rowdata as column in our custom logic, then we can use some temp table and then populate agreegate data in temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain...
Posted to
sqlideas
by
Paresh Prajapati
on
02-18-2012
Filed under:
Filed under: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, t-sql, sql server denali, #SQL Server, #sql, sql server 2011, database, sql server general, SQL Scripts, pivot
-
As you all know tempdb is responsible of storing all the temporary objects created for a server. One of my co-workers asked me "what is the scope of permanent tables created in the tempdb database?". The answer is it can be accessed via tempdb from various connections just like objects are...
-
Without running these queries can you find out the query that would throw an error? 1 select 7*-2&.3 2 select 7|2&-3 3 select 7+-(.2)%(+3) 4 select ((7&2%3))
-
One of the members in the forum asked about using decimal datatype in identity column. The question was "Is decimal point allowed in the identity column?" Ok. The identity column can never have any decimal points. It is a whole number. The decimal datatype can be used to have a bigger number...
-
As a DBA we often need to get information on various Queries, One of them is how expensive it is. A query can be judged as expensive on various criteria’s like Long Running, Memory Utilization, CPU utilization etc. I came up with this Stored Procedure which gives you Expensive Queries based on criteria...
-
Purging your MSDB Job History is sometime very important which helps you read the Jos history in GUI very easily and keeps which only required number of days. You can add this in your Maintenance Plan or using a SSIS Package. I found one of the way a Stored Procedure to do the same. This Procedure accepts...
-
When a datatype timestamp or rowversion is used, it generates unique value whenever a row is added or updated in a table. It is one of the ways to generate auto-generated values not only for insertion but for updation too. If you want to know the minimum active rowvertion number for a database, you can...
-
Everybody deserves a second chance in love, life, and in TSQL as well! Now that it's the beginning of a New Year, 2012, it's the perfect time to start over again, and make up for the things that you missed, should the opportunity present itself. That's why I am blogging right now, because...
-
The system variable @@TRANCOUNT is used to get the number of transactions that are active. Consider the following code begin transaction select 3 select @@TRANCOUNT commit transaction The result of @@TRANCOUNT is 1. Consider the following code. begin transaction select 3 begin transaction select 456...
-
I experienced into one issues for database backups were failed. And this was due to space issues on disk drive. This disk drive is spacific to allocated for the database backups only. The space was eaten by this database backups and this drive contains so many old backups. I have manually deleted all...
Posted to
sqlideas
by
Paresh Prajapati
on
01-07-2012
Filed under:
Filed under: sql, tsql, sql server, ms sql, ms sql server, t-sql, #SQL Server, #sql, database, sql server general, Maintenance, SQL Scripts, backup, query
-
Cross tab result is always required and it is bit complex in case of dynamic column. If column name has to be dynamic then this is the good idea to have the PIVOT Query, for example I have 3 Tables and data like this: First Table ----------------------------------------------------------- IF OBJECT_ID...
-
The below scripts will help us to determine the list of columns present in a table , their datatype and size along with whether it is identity/computed or not Solution 1 Select [Table Name] = so.name ,[Column Names] = sc.name ,[Data Type] = st.name ,[Size] = sc.length ,[Is Identity] = Case When sc.status...
-
This post is really inspired by Nakul's wonderful post: http://beyondrelational.com/blogs/nakul/archive/2011/12/29/sql-server-t-sql-different-ways-to-generate-a-comma-separated-string-from-a-table.aspx However, Whenever I faced a situation to concatenate the values in a table there was always a requirement...
-
As I have informed you earlier in this post , there are some interesting tips I have contributed in December 2011. You may find them interesting if you follow them. The following are some of my tips I posted there Most Learned Posts of all time You can drop multiple tables using single DROP statement...