SQL SERVER – Fix – Error 1759 Computed column in table is not allowed to be used in another computed-column definition

This is a very common error with beginning developers when they start working with computed columns.

When a new developer finds a joy of computed column, it is very natural to use the same computed column for other columns. However, a computed column cannot be used in another computed column or it will give an error.

Here is a simple reproduction of the scenario.

-- Create table
CREATE TABLE Table1 (Col1 INT, Col2 AS Col1*10);

Now we will add another computed column Col3 based of Col2 (which is computed column itself).

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col2*10;

When we execute above statement, it will give us following error.

Msg 1759, Level 16, State 0, Line 2
Computed column ‘Col2′ in table ‘Table1′ is not allowed to be used in another computed-column definition.

This is because we can not use computed columns in another computed column. This kind of referencing is not allowed in SQL Server.

Workaround:

Here is a quick workaround. Our end goal is to create another column which is multiply of Col2 by 10. Now Col2 is Col1 multiplied by 10. That means Col3 is Col1 multiplied by 100. Let us create a new column which is Col1 multiplied by 100.

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col1*100;

That’s it! We are done.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Unable to ALTER Computed Column in SQL Server – How to ALTER Computed Column

A Very common question, I often receive is

Q: “How to ALTER computed columns?”

A: There is NO way to alter computed column. You will have to drop and recreate it.

Here is a demonstration of it.

If you try to alter the computed column it will throw following error.

-- Create table
CREATE TABLE Table1 (Col1 INT, Col2 AS Col1*10);
-- Failed attempt to alter column
ALTER TABLE Table1
ALTER COLUMN Col2 AS Col1/10;

Above script will give following error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘AS’.

The best way to fix this is to drop the column and recreate it.

-- Drop Column
ALTER TABLE Table1
DROP COLUMN Col2;
-- Create Column
ALTER TABLE Table1
ADD Col2 AS Col1/10;

Let me know if there is any other way of altering the column without dropping it.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup

About four years ago, I wrote a blog post where I posted a script about finding backup time for all the databases. You can see the blog post over here SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. However, this script was just giving details about last full backup time. SQL Expert Sravani has posted a fantastic script which also displays last full, differential and log backup for the database.

Here is the script.

SET NOCOUNT ON
GO
SET quoted_identifier OFF
DECLARE
@dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT "#####################################################################"
PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"
PRINT "#####################################################################"
PRINT "DatabaseName Full Diff TranLog"
PRINT "##########################################################################################################################################"
SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset
WHERE database_name = c.database_name
AND TYPE = 'I')
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)'Transaction Log Backup Status'
FROM msdb..backupset
WHERE database_name = d.database_name
AND TYPE = 'L')
WHERE s.name <>'tempdb'
ORDER BY s.name

Sravani, please send me email at my mail id and I will send USD 20 worth Amazon Gift Card or INR 1000 Flipkart Gift Voucher for your wonderful contribution. If you use any such script in your daily routine. Please send me email and I will be glad to post the same on blog with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022

[Note from Pinal]: This is a 22nd episode of Notes from the Field series. Security is very important and we all realize that. However, when it is about implementing the security, we all are not sure what is the right path to take. If we do not have enough knowledge, we can damage ourself only. DB Data Roles are very similar concept, when implemented poorly it can compromise your server security.

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of Brian in his own words.


I am prejudiced against two fixed database roles: db_datareader and db_datawriter. When I give presentations or talk to customers, some are surprised by my stance. I have two good reasons to recommend against these two roles (and their counterparts, db_denydatareader and db_denydatawriter).

A Violation of the Principle of Least Privilege

The first reason is they violate the Principle of Least Privilege. If you’re not familiar with this security principle, it’s really simple: give permissions to do the job – no more and no less. The db_datareader and db_datawriter roles give access to all tables and views in a given database. Most of the time, this is more access than what is needed. This is a violation of the Principle of Least Privilege.

There are some cases where a user needs such access, but there is always the possibility that a new table or view will be added which the user should not have access to. This creates a dilemma: do I create new roles and remove the user from db_datareader or db_datawriter or do I start using DENY permissions? The first involves additional work.The second means the security model is more complex. Neither is a good solution.

Failing the 3 AM Test

The second reason is the use of these roles violates what I call the “3 AM test.” The 3 AM test comes from being on call. When I am awakened at 3 AM because of a production problem, is this going to cause me unnecessary problems? If the answer is yes, the solution fails the test. I classify db_datareader and db_datawriter role usage as failing this test. Here’s why: the permissions granted are implicit. As a result, when I’m still trying to wake up I may miss that a particular account has permissions and is able to perform an operation that caused the problem. I’ve been burned by it in production before. That’s why it fails my test.

An Example

To see why this is an issue, create a user without a login in a sample database. Make it a member of the db_datareader role. Then create a role and give it explicit rights to a table in the database. This script does so in the AdventureWorks2012 database:
USE AdventureWorks2012;
GO
CREATE USER TestDBRoleUser WITHOUT LOGIN;
GO
EXEC sp_addrolemember @membername = 'TestDBRoleUser', @rolename = 'db_datareader';
GO
CREATE ROLE ExplicitPermissions;
GO
GRANT SELECT ON HumanResources.Employee TO ExplicitPermissions;
GO

Pick any table or view at random and check the permissions on it. I’m using HumanResources.Employee:

We see the permissions for the role with explicit permissions. We don’t, however, see the user who is a member of db_datareader. When first troubleshooting it’s easy to make the assumption that the user doesn’t have permissions. This assumption means time is wasted trying to figure out how the user was able to cause the production problem. Only later, when someone things to check db_datareader, will the root cause be spotted. This is why I say these roles fail the 3 AM test.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Developer – How To Market Yourself as a Software Developer

“Everybody wants to make money and build a successful environment around and it is a universal truth my friend”, John mentioned to me while we were talking on Skype earlier this year.

Conversation: John and Pinal

John is the founder of simpleprogrammer.com and a very close friend of mine. We discuss pretty much anything and everything. He is from USA and I am from India, so our conversation usually starts either in the morning or evening, but it keeps on running for hours, thanks to our family who allows us to do this crazy long but productive talk.

When John said he has discovered how famous software developers make so much money, I found it very intriguing. I know John for many years, he does not do cheesy talk or say things without substance. I was wondering what would John do.

What would John do?

John had two options 1) He keeps this secret with him forever or 2) Tell his secret to the world. If you know John, you can easily guess that he is not going to keep quiet about it. He is going to make it public and make it universally accessible to the world. It is very similar to the publishing the books like Alchemist or The Secret or any other similar books. The author of those books does not keep the secret of success to themselves only. They share and make the secret a true instrument in people’s life.

As expected, John built a fine program of “How to Market Yourself as a Software Developer”. This is indeed a very interesting program as it is not a video, book, tutorial or any exercise file. It is the matter of the fact everything. John has not restricted the success story to a simple medium. He has let the story build around a concept and has not limited the potential of the program.

What did I do to help John?

Nothing. We all have nothing to do to help John. This is not about John or Simple Programmer. It is about a program which tells and shares secrets of How to Market Yourself as a Software Developer. We all are a software developer and we all are successful in our own field. When I say We – this also includes YOU too. Think about it, are not you successful in what you do? You absolutely are. We all have done something right in our past or present. We all have secrets which are not shared with the world.

Just like that, John has compiled his own success story with many other’s success stories and build this program. I think it is a story build with the experience of the many real software developer who has achieved something in their life.

What NEXT?

Join the program. I am sure there are plenty of things which you know, I should know to be more efficient and successful. Similarly, there are plenty of things other knows, but you need to learn to be more successful in your career. Why not learn it from each other. I will be interested to know your thoughts about how can I be more successful.

Secret Code

If you want to join the program. Here is the secret code I have got from John. You can go to the link http://simpleprogrammer.com/market to join the program. The program cost USD 299 however, when you use secret code sqlauthority, you will get a discount of USD 100 (that is 33% to the MRP).  You will have instantly access to the entire package immediately as well as 30 Day Money Back Guarantee.

What are you waiting for?

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – List User Defined Tables – Two Methods

There are different methods to list out user defined tables in MySQL. We will see two methods in this post.

Let us create the following tables in the database named Test

Create a database named Test

CREATE DATABASE Test;

Create the tables named items and sales in the database Test.

USE TEST;
CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Now to list out all user defined tables, use any one of the following methods

Method 1: Use SHOW TABLES command

SHOW TABLES;

If you execute the above statement, it will list out all the user defined tables in the database in which it is run (in this example the database name is Test)

The result is

Tables_in_test
 --------------
 items
 sales

You can also use database name along with this

SHOW TABLES FROM Test;

Which will give you the same result

Method 2: Use INFORMATION_SCHEMA view

You can make use of ANSI standard INFORMATION_SCHEMA.TABLES
view to do the same shown as below

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Test';

The result is

Tables_in_test
 --------------
 items
 sales

This is a very small trick but works great when you want to know what are different tables exist in MySQL Database.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Detect Schema Change Across Two Servers

If you are database developer or database administrator, you will absolutely feel the story. You can download the software discussed in this blog post to play it along.

Story of My Early Career

A very interesting scenario, I have encountered in my earlier career. In earlier days, we used to develop everything on my local box. Our application and database both stayed in the same box. I was allowed to code only on the module or section which was assigned to me. I can’t go out and code or do anything else besides what I was assigned. When we code this modular way, it was easy to deploy the code upon completion of the project. This all worked fine till I went for vacation.

Once I went on vacation and the development of my project was done by my friend and colleague Romesh. Upon returning, Romesh wanted to hand over the entire project to me and he wanted to continue on his own project. The problems started here as his project and my project were totally different. Our database has been the same, but the table and stored procedure which we worked were mostly very different. In this situation the best practice for me was to take the schema changes he has done on the machine to my machine and continue the project. I had to be very careful as I should not take any other schema from his current project. If I have a part of his project, when I deploy my project that will go live and it should not happen.

The Epic Dilemma

Here was the problem – we were sitting together and discussing how to synchronize the schema from his machine to my machine selectively. I did not want the schema of the project he was earlier working on as that will further complicate my deployment, but I do want all the object which belonged to my projects. How do we achieve this complicated goal of selective schema difference detection and synchronization? 

Selective Schema Compare Detection and Synchronization

After some research online, we landed on Embarcadero’s DB Change Manager. We downloaded the trial version and tried to do a selective schema comparison. It is pretty easy to use tool which walks us through the wizard and instantly we were able to detect schema change between my database server and Romesh’s database server. We used a couple of filters to indicate which are my tables and which are his tables and quickly able to only see the selective changes.

Once we detected the changes between schema, we quickly generated the script to deploy on my database server with the help of Manual Synchronization features. I copied the script and I was instantly able to move on with the my project. I really feel that sometime tools like Embarcadero’s DB Change Manager makes quite a positive impact on efficiency of work life.

Here is the quick video which demonstrates how quickly one can use Embarcadero’s DB Change Manager compares schema.

Let me know what you think of this video. You can download Embarcadero’s DB Change Manager.

Reference: Pinal Dave (http://blog.sqlauthority.com)