What are the important advance topics in SQL server that every programmer should learn?

What is SQL Server?
SQL Server is a database server by Microsoft. ... SQL is a special-purpose programming language designed to handle data in a relational database management system. A database server is a computer program that provides database services to other programs or computers, as defined by the client-server model,If you are intrested to learn about SQL server DBA please visit SQl Server DBA online training Hyderabad


I don’t think there are “advanced” topics within T-SQL that every programmer has to know. I do think there are fundamentals that every programmer ought to know that, based on my experience, they don’t. A few of these include:
  • Normalization, done properly, is a good thing that not only helps make the data better, but enhances performance and makes coding easier, not harder.
  • T-SQL is based on the concepts of sets. When you start trying to deal with data as individual rows through WHILE loops and cursors, you’re leaving that behind.
  • One column for one piece of information. Don’t try to overload behaviors, see normalization above.
  • The data type matters. Sure, you can store that date in a very pretty format if you make it a varchar, but you’ve now committed yourself to not using date math and/or lots of CAST statements, which not only clutter the code, but destroy performance.
  • T-SQL does not lend itself to code reuse. Yeah, writing a JOIN between the same four tables over and over is a pain, but writing views that join to views that query views, etc, ad infinitum, cripples the query optimizer and destroys performance. Bite the bullet, write the JOIN more than once.
  • NOLOCK is absolutely not some secret run faster button that you’ve discovered and should not be added to every query. In fact, NOLOCK leads to missing data or duplicate data in your queries. Please don’t write software that manages my bank account using NOLOCK.
  • Parameterize your queries. I not only don’t care that you’re using an ORM tool, I actively encourage it’s use. Why? Every one I’ve worked with, out of the gate, parameterizes the queries. Why is this so important? It reduces the number of queries that have to be compiled, improving system performance. It reduces the number of plans that have to be stored in cache, enhancing memory management within the system. Probably most importantly, it prevents SQL Injection attacks.
  • Learn about SQL Injection and how to avoid it. To quote my kids, it’s current year.
That should about cover it. If every single developer working against the database knew this stuff, then maybe I could come up with some advanced topics for them all to learn too.

Comments

Post a Comment

Popular posts from this blog

Android Interview Questions And Answers

Image
A set of questions that I have accumulated over the years in preparation for my many Android Interviews Prep. 1) What is Android? Android is an open-source, Linux-based operating system used in mobiles, tablets, televisions, for more Android Online Training 2) What is the Android Application Architecture? Android application architecture has the following components: Services − It will perform background functionalities Intent − It will perform the interconnection between activities and the data passing mechanism Resource Externalization − strings and graphics Notification − light, sound, icon, notification, dialog box and toast Content Providers − It will share the data between applications 3) What are the advantages of Android? Open-source:  It means no license, distribution and development fee. Platform-independent:  It supports Windows, Mac, and Linux platforms. Supports various technologies:  It supports camera, Bluetooth, wifi, speech, EDGE etc. tech
Read more

Ten Things a Junior DBA Should Learn

Image
There is more to being a Junior DBA than knowing SQL. The DBA works at the intersection of the database, server, operations group, and developers. A DBA must understand concepts from all these facets of IT as well as be able to draw upon knowledge of their production environment to troubleshoot performance, hardware, and software issues. Below is a list of the ten topics I feel every entry level DBA should understand. The list I created stems from my experiences working with databases as both a DBA, Developer, and Manager.  When looking to hire entry level DBA’s, the interview questions I ask are drawn from these areas. If you’re looking to start a job as DBA, then you’ll want to be familiar with these topics,If you are intrested to learn about SQL DBA please visit: ios app development course Backup and Restore Any DBA worth their salt should know the DBMS’ (Database Management System’s) built-in methods to backup and restore data, such as using Oracle Recovery Manager, bu
Read more

SQL Server Interview Questions & Answers

Image
What is MySQL MySQL is a fast, easy to use relational database. It is currently the most popular open-source database. It is very commonly used in conjunction with PHP scripts to create powerful and dynamic server-side applications. MySQL is used for many small and big businesses. It is developed, marketed and supported by MySQL AB, a Swedish company. It is written in C and C++,If you are intrested to learn about SQL Server please visit  SQl Server DBA online training Hyderabad 1. What are the two authentication modes in SQL Server? There are two authentication modes – Windows Mode Mixed Mode   Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page. 2. What Is SQL Profiler? SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis. 3. What is recursive stored procedure? SQL
Read more