The SQL Server 2008 Management Studio will throw the following warning message when we tried to save the modification done in an existing table. It won’t allow us to save the changes in the table.
Warning Message:
“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created”.
We can fix this issue in Management Studio by un-checking a property. To fix this issue in Management Studio, Go To Tools > Options > Designer > Tables and Database Designs > uncheck the property “Prevent saving changes that require table re-creation”.
The SQL Server 2005 Management Studio allows us to “Edit” and “View” the whole set of records in a table. But in SQL Server 2008 Management Studio, this flexibility was restricted. SQL Server 2008 by default allows us to select top 1000 records and allows us to edit top 200 records. If our records in a table are more than the default numbers, SQL Server 2008 won’t display those records or allows us to edit those records. We can increase those default value.
Here are the steps…
1. Open SQL Server 2008 Management studio.
2. Right Click on any of the table. It will display the default value.
3. Go to “Tools” > “Option” > “SQL Server Object Explorer” > “Command”.
4. Change the default value to your desired value. Here i am entering “10000″.
5. Now go and check again. The value gets changed.
We successfully changed the default value to our desired value.
YTD - Abbreviation for YTD is Year-To-Date. YTD function will sum the measurable value from the period beginning January 1st of any year we specified up until the date we specified in the same year.
Database - “dbo.YTD“
Table Structure
Sample Records
SQL Query for YTD using Self-Join
/* Passing Date as a parameter*/
Declare @Date Datetime
Set @Date = ‘Mar 01, 08′
/* Calculating the Year beginning based on the Date parameter */
Declare @SDate Datetime
Set @SDate = Convert(Varchar, Convert(Datetime, @Date) – Datepart(y, Convert(Datetime, @Date)-1),7)
/* Converting the Date parameter to a generalized format */
Declare @EDate Datetime
Set @EDate = Convert(Varchar,(@Date),7)
/* Passing Product a parameter */
Declare @Product Nvarchar(25)
Set @Product = ‘AA’
/* Self-Join Query to display both YTD amount and the SaleAmount for a Particular Date in a single Query */
Select A.ProductName, B.SalesAmount, Sum(A_sales) YTDSalesAmount
From
/* Query for calculating “YTD” */
( Select ProductName, Sum(SalesAmount)as A_sales
From dbo.YTD
Where Date Between @SDate And @EDate AND ProductName = @Product
Group By ProductName )A,
/* Query to show the “SalesAmount” for the “Date” Parameter*/
( Select ProductName, SalesAmount
From dbo.YTD
Where Date = @EDate AND ProductName = @Product )B
Group By A.ProductName, B.SalesAmount
Result
1. Date = ‘Mar 01, 08′ and Product =’AA’.
2. Date = ‘Mar 01, 08′ and Product =’AB.
Same like this by changing the “Date” Parameter, we can get YTD for different Dates.
