links

specialization is for insects. http://www.agiledata.org/essays/mappingObjects.html
http://www.hacknot.info/hacknot/action/showEntry?eid=48

http://www.codeproject.com/KB/audio-video/TalkingClipboard.aspx

A Day in the Life Software Developer

http://www.princetonreview.com/cte/profiles/dayInLife.asp?careerID=145

Advertisements

8 thoughts on “links

  1. Irfan Munir says:

    Total votes: 0
    Views: 802
    Comments: 10

    Category: SQL
    Please login to rate or to leave a comment.

    Q&A with Database Administrators
    Published: 30 Nov 2007

    By: Pinal Dave

    Pinal Dave shares his notes about Q&A with Database Administrators.

    I have been in India for more than a month now, as I am leading a very large outsourcing project. We have conducted few interviews since the project required more Database Administrators and Senior Developers. I am listing few of the questions discussed during all the interviews. The whole event of interviews was very interesting. I met some very good programmers from all over the country. Many interesting questions were discussed between interviewers and candidates. I am listing some of those questions here. Some are technical and some are just my personal opinions.

    Questions & Answers

    Q. What kind of Database Administrator do you think is the best Database Administrator?

    A. Primary job of DBA is to secure the data and keep it safe as well as being able to reproduce data efficiently, when required. A Database Administrator, who can fulfill the requirements of Securing Data and Retrieving Data, is the best DBA as per my view.

    When I hire DBA I always ask them questions about backup strategies and efficient restoring methodologies.

    Q. Can I restore the database if I do not have full backup but I have all the primary data file and secondary data files as well as logs?

    A. You can not restore the database without having full database backup. However, if you have copy of all the data files (.mdf and .ndf) and logs (.ldf), when database was in working condition (or your desired state) you can attach that database using sp_attach_db.

    Q. As per your opinion, what are the five top responsibilities of DBA?

    A. I rate following five tasks as the most important responsibilities of DBA.

    Securing the database from physical as well as logical integrity damage.
    Restore the database from backup as part of disaster management plan.
    Optimize the queries performance by proper indexing and optimizing joins, where conditions, select clause etc.
    Design the new schema and support legacy schema as well legacy database systems.

    Help developers to be better at writing SQL related code.

    Q. One of the developers in my company moved one of the columns from one table to some other table in the same database. How can I find the name of the new table where the column has been moved?

    A. This question can be answered by querying system views.

    For SQL Server 2005 run the following code:

    SELECT OBJECT_NAME(object_id) TableName  FROM sys.columns  WHERE name = ‘YourColumnName’  SELECT OBJECT_NAME(object_id) TableName
    FROM sys.columns
    WHERE name = ‘YourColumnName’

    The previous query will return all the tables that use the column name specified in the WHERE condition. This is a very small but very handy script.

    Q. What is the difference between SQL Server 2000 object owner and SQL Server 2005 schema?

    A. Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.

    SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]

    SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]

    In SQL Server 2000, before dropping the user who owns database objects, all the objects belonging to that user need to be either dropped or their owner has to be changed. Every time a user has to be dropped or modified, system admin has to go through this inconvenient process.

    In SQL Server 2005, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects. Multiple users can be assigned to a single schema and they all automatically receive the same permissions and credentials as the schema to which they are assigned. Due the same reason in SQL Server 2005 – when a user is dropped from database – there is no negative effect on the database itself.

    Q. What is BI? I have heard this term before but I have no idea what is it?

    A. BI is an acronym that stands for Business Intelligence. Microsoft has started to promote the acronym BI since the launch of SQL Server 2005. However, it has been in use for long time. The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting key enterprise data so that anyone in your company can quickly and easily ask questions based on accurate and timely data. Effective BI allows end users to use data to understand why your business got the particular results that it did, to decide on courses of action based on past data, and to accurately forecast future results

    Q. What is your recommendation if a query is running very slow?

    A. Your question is very difficult to answer without looking at code, application and physical server. Few things should be looked at right away when similar situations arise.

    Restart Server

    Upgrade Hardware
    Check Indexes on Tables and Create Indexes if necessary
    Make sure SQL Server has priority over other operating system processes in SQL Server settings
    Update statistics on the database tables

    Q. What should be the fill factor for Indexes created on tables?

    A. Fill factor specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. Fill factor must be an integer value from 1 to 100. The default is 0. I keep my servers default fill factor as 90.

    Q. Which feature in SQL Server 2008 (to be released in February 2008) has surprised you? Name only one.

    A. Plan Freezing is the new feature I never thought of. It is a very interesting feature and it is included in SQL Server 2008 CTP5. SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

    Q. How do you test your database?

    This is a very generic question. I will be describing my generic database testing method as well as stored procedure testing methods.

    Testing Databases:

    Table Column data type and data value validation.
    Index implementation and performance improvement.

    Constraints and Rules should be validated for data integrity.
    Application field length and type should match the corresponding database field.
    Database objects like stored procedures, triggers, functions should be tested using different kind of input values and checking the expected output variables.

    Testing Stored Procedures:

    Understand the requirements in terms of Business Logic.
    Check that code follows all the coding standards.

    Comparing the fields’ requirements of application to the fields retrieved by a stored procedure. They should match.
    Repeatedly run stored procedures many times with different input parameters and compare the output with expected results.
    Pass invalid input parameters and see if a stored procedure has good error handling.

    About Pinal Dave

    Sorry, no bio is available
    View complete profile

    Top Articles in this category

    Using SQL Server Integration Services (SSIS) Control Flow in SQL Server 2005
    SQL Server Integration Services (SSIS) in SQL Server 2005 has been redone, and is clearly superior over SQL Server 2000. With all of the new capabilities and features in SSIS, it is possible to create very complex transformation packages. This article will show you how to take advantage of the control flow features.

    Using SQL Server Integration Services (SSIS) Data Flow in SQL Server 2005
    SQL Server Integration Services (SSIS) in SQL Server 2005 has been redone, and is clearly superior over SQL Server 2000. With all of the new capabilities and features in SSIS, it is possible to create very complex data transformation packages. This article will show you how.

    The SqlCacheDependency Class
    Luke Stratman shows how and why to use the SqlCacheDependency class.

    SQL Server Pre-Code Review Tips

    A list of tips for enforcing coding standards in SQL.

    Generating Relational data from xml datatype Part 1
    The introduction of the xml datatype in SQL Server has given us an enhancement of actually dumping raw XML obtained from different servers into a distributed environment into one relational table. This article which is the first part of two, deals with the basics of the xml datatype and retrieving data from the xml datatype.

    <!–
    var browName = navigator.appName;
    var SiteID = 1;
    var ZoneID = 14;
    var browDateTime = (new Date()).getTime();
    if (browName==’Netscape’)
    {
    document.write(”); document.write(”);
    }
    if (browName!=’Netscape’)
    {
    document.write(”); document.write(”);
    }
    // –>


    Top

     

     

    =5)
    { document.write(”); }
    else if ((browVersion>=4)&&(ua.indexOf(“mac”)==-1))
    { document.write(”);
    document.write(”);
    document.write(adcode); }
    else if (browVersion>=3)
    { document.write(‘‘); } }
    else
    { document.write(”); }
    // –>


    =5)
    { document.write(”); }
    else if ((browVersion>=4)&&(ua.indexOf(“mac”)==-1))
    { document.write(”);
    document.write(”);
    document.write(adcode); }
    else if (browVersion>=3)
    { document.write(‘‘); } }
    else
    { document.write(”); }
    // –>



     

    Discussion

    Subject

    Author

    Date


    Q. What is your recommendation if a query is running very slow?

    Granville Barnett

    11/30/2007 11:10 AM

    I’m not  a SQL guy, but…If I had a query that was running slow I would first look at the query execution plan to see which bits of my query were taking the most time.  Failing that, lets just say that the query returns a major dataset with many, many columns and rows or one or the other then I would look at horizontal and/or vertical partitioning.Updating the hardware would probably be the last thing I would do.Like I said earlier I’m not a SQL guy, my knowledge is limited but wouldn’t you want to ask them some questions like:- Design me a schema for Xxx company where they have etc….- Then maybe you could have an add-on question to the design that asked them to perform a pretty complex query to get some data for you.

    I’m assuming that database administrators actually know SQL and database design methodologies.
    My 2 cents.
    Granville Barnett
    http://weblogs.asp.net/gbarnett
    Feel free to ask me any .NET question

    Reply

    |
    Permanent
    link


    Restarting the Server

    Sonu Kapoor

    11/30/2007 11:22 AM

    I dont think that restarting the server should be the first solution. The first think I would do is to check my indexes on the used tables.
    [MVP since 2005] [MCAD]

    Webmaster of DotNetSlackers
    Question or Suggestion
    Feel free to ask my any .NET question

    Reply
    |
    Permanent
    link


    RE: Restarting the Server

    Pinal Dave

    11/30/2007 12:41 PM

    Sonu,You are absolutely correct. First of all DMV should be checked for the indexes on used tables as well as unused indexes.The answers were given on the spot and in discussion with other DBA’s. The reason for restarting server was to see that if some at that moment process was not taking over CPU time. Though this option can be checked afterwards. Regards,Pinal

    Reply
    |
    Permanent
    link


    RE: RE: Restarting the Server

    Sonu Kapoor

    11/30/2007 12:44 PM

    Isn’t better to check who is actually using those resources (if any) instead of just restarting the server?
    [MVP since 2005] [MCAD]

    Webmaster of DotNetSlackers
    Question or Suggestion
    Feel free to ask my any .NET question

    Reply
    |
    Permanent
    link


    RE: RE: RE: Restarting the Server

    Pinal Dave

    11/30/2007 12:53 PM

    Yes. Your suggestion adds value to what original discussion had missed.There are actually much more things one should do when queries are running slow. The one article I really like which one should read regarding SQL Server optimization is http://technet.microsoft.com/en-us/library/aa964133.aspx .Kind Regards,Pinal

    Reply

    |
    Permanent
    link


    RE: RE: RE: RE: Restarting the Server

    Granville Barnett

    12/1/2007 11:49 AM

    I’m with Sonu and my suggestions on that one.  No matter how much hardware you have you are still going to have to face the fact that you’re queries are running slow one day.Restarting server would of been something I would of mentioned in passing after I’d gone through the other stuff.

     
    Granville Barnett
    http://weblogs.asp.net/gbarnett
    Feel free to ask me any .NET question

    Reply
    |
    Permanent
    link


    RE: RE: RE: RE: RE: Restarting the Server

    Kazi Manzur Rashid

    12/1/2007 7:04 PM

    The most obvious reason of a query running slow is either it is poorly written which means it does not have any index or it does not utilize the indexes or the index is too much fragmented. You can check the index fragmentation with DBCC INDEXDEFRAG command of SQL Server. @Sonu: You can use the sp_Who and sp_Who2 to check the active connection. (Could not rember the difference between these two command).@Granville: Yes that is true that the query will run slow one day. and this is the responsibility of the DBA to fine tune the db maybe by rebuilding the index adding more hardware, data partitioning etc.
    Long Live .NET
    Kazi Manzur Rashid (Amit)
    _________________________
    http://weblogs.asp.net/rashid/

    Reply
    |
    Permanent
    link


    Performance Optimization

    Kazi Manzur Rashid

    12/1/2007 7:07 PM

    Hi Pinal, How about writing a Performance optimization tips/tricks article on SQL Server 2005.
    Long Live .NET
    Kazi Manzur Rashid (Amit)
    _________________________
    http://weblogs.asp.net/rashid/

    Reply

    |
    Permanent
    link


    RE: Performance Optimization

    Pinal Dave

    12/1/2007 8:52 PM

    Hi Kazi,Sure I will focus on that as next article.Kind Regards,Pinal

    Reply
    |
    Permanent
    link


    Q. What is your recommendation if a query is running very slow?

    Sameer Alibhai

    12/4/2007 3:53 PM

    I am not an SQL guy, I am more of a .NET programmer, but the first thing I would do is look at the actual query and see if its poorly written.  Often its running slowly not because of the hardware, indexes, and what not, but rather the query itself is poorly written – too many inner joins, or what not.
    SharpDev

    Reply
    |
    Permanent
    link

    Please
    login
    to rate or to leave a comment.

    //<![CDATA[
    function toggleVisibilityAndHighlight(sourceElement, commentCellId, OrgCss)
    {
    if ($get(commentCellId).className!=”hidden commentRow”)
    sourceElement.parentNode.parentNode.className=OrgCss;
    else
    sourceElement.parentNode.parentNode.className=”commentHeaderHighlight”;

    // Get parentNode twice to get a reference to the row
    //Sys.UI.DomElement.toggleCssClass(sourceElement.parentNode.parentNode, “commentHeaderHighlight”);
    Sys.UI.DomElement.toggleCssClass($get(commentCellId), “hidden”);
    }

    function ReplyTo(ReplyToId, Subject)
    {
    var h=document.getElementById(‘ctl00_ViewAddArticleComments1_h’);
    if(h.value==”False”){
    window.location=”/logon.aspx?ReturnUrl=/articles/sql/QuestionAnswersWithDataBaseAdministrators.aspx#comments”;
    return;
    }

    document.getElementById(‘ctl00_ViewAddArticleComments1_txbSubject’).value=”RE: ” + Subject;
    document.getElementById(‘ctl00_ViewAddArticleComments1_replyTo’).value = ReplyToId;
    }

    var allSelects = document.getElementsByTagName(‘textarea’);
    for (var i = 0; i

    Product Spotlight
    .NET Code Generation is Here!Generate database-driven Web apps for .NET in minutes. Quickly create visually stunning, feature rich apps that are easy to customize and ready to deploy. Free Trial

    /* Hack: position fixed su IE > 5.5, IE < 7*/
    .UpdateProgress
    {
    position:absolute;
    height:15px;
    width:125px;
    top: expression( ( 0 + ( ignoreMe = document.documentElement.scrollTop ? document.documentElement.scrollTop : document.body.scrollTop ) ) + ‘px’ );
    right:3px;
    padding:3px 5px;
    background: #f5f5f5;
    border: 1px solid #ddd;
    color:#000;
    font-weight:bold;
    }

    Please wait…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s