background preloader

Relational database

Facebook Twitter

Handling NULL in T-SQL - CodeProject. Table of Contents Foreword Displaying error message is always better than generating wrong output.

Handling NULL in T-SQL - CodeProject

When you deal with NULL, there might be a chance of getting output even though the code has some logical errors. It plays a vital role to maintain the quality and accuracy on database output. In this article, I would like to describe different aspects, general perceptions and best practices about NULL in T-SQL. Your comments are highly appreciated. :). General Perceptions of NULL It is blank. What is NULL? NULL means 'NO RESULT' or 'UNKNOWN' which is not equal to itself. Hide Copy Code NULL <> NULL --NULL does not equals NULL. if(NULL=NULL) -- It returns nothing(no error and no result). CODD's Rule 3: Systematic treatment of null values: The DBMS must allow each field to remain null (or empty).

A value of NULL indicates that the value is unknown. Five Common SQL Mistakes. As you learn SQL, watch out for these common coding mistakes You’ve written some SQL code and you’re ready to query your database.

Five Common SQL Mistakes

You input the code and …. no data is returned. Instead, you get an error message. Don’t despair! Coding errors are common in any programming language, and SQL is no exception. Watch Your Language (and Syntax) Using Conditional Logic in SQL with CASE Expression - CodeProject. The main purpose of a CASE expression is to return a value based on one or more conditional tests.

Using Conditional Logic in SQL with CASE Expression - CodeProject

CASE expressions can be used anywhere in a SQL statement an expression is allowed. Though a truly an expression, some people refer to them as “CASE statements.” This most likely stems from that use of the phrase in programming languages. The CASE expression is extremely versatile. How to manipulate hierarchical information in flat relational database tables (optionally industrial strength) - CodeProject. Warning Just thought about looking into this algorithm I invented back in 2002.

How to manipulate hierarchical information in flat relational database tables (optionally industrial strength) - CodeProject

Its 29/Nov/2016 when I am thinking about this. Upon cursory, couple of minutes, examination of the article and the algorithm it has serious flaws. I will put up a new algorithm that serves the same purpose in another article. This article will remain up to keep a history of the gensis of the idea for this algorithm I had. Download demo project - 25.1 KB Introduction. Monitoring SQL Server Database. What is Monitoring?

Monitoring SQL Server Database

While working or managing a database, we may need various types of information regarding the database such as settings like owner's name, file names, backup schedules and status of the database, etc. So, we can say, monitoring database is checking or verifying various properties like settings and status of the database. Finding Items in a Collection that Match one of... Introduction Recently, I have answered a couple of Quick Answers where the user wanted a list of items that match a condition from another collection (similar to SQL's IN operator.

Finding Items in a Collection that Match one of...

The trick to solving this is reversing the order of the operands in the Where Linq extension from the norm. Using the Code The normal structue of a Where predicate follows the pattern: Hide Copy Code myCollection.Where(item=>item.Property matches value) Build Your Own Movie Database with SQL Server. Introduction In the last month, I have been dealing with the problem of calling a Web Service from SQL Server.

Build Your Own Movie Database with SQL Server

Why would I? Fairness, mainly. When all your applications are passing through the same Web Service that manages concurrency and audit, if a stored procedure writes directly onto the database, it is not really fair! How to Format Repeating Values in SQL Result Set. Download source code - 514 B Introduction Overall, formatting the output data from the database server should never be done in SQL.

How to Format Repeating Values in SQL Result Set

There are a lot of better tools and languages to do the formatting. SQL is primarily designed for data retrieval and manipulation, not for creating fancy output. Having that said, a question that often arises is, "How can I remove repeating values for a column in my result set? ". The Code First, we need a test table and some data. DROP IF EXISTS in SQL Server 2016 (DIE) Prior to SQL Server 2016, when we need to drop a SQL Object, it's the best practice to check whether the respective object exists or not.

DROP IF EXISTS in SQL Server 2016 (DIE)

Otherwise, the operation will return in an error. Hide Copy Code DROP TABLE [SomeTable] If the object is not found, it will return the following error: Msg 3701, Level 11, State 5, Line 11 Cannot drop the table 'SomeTable', because it does not exist or you do not have permission. Hence, we need to change the syntax as: IF EXISTS(SELECT 'x' FROM sys.objects AS O WHERE O.name = 'SomeTable' AND O. Masking sensitive data with SQL Server 2016.

Introduction At the time of writing this article, it’s been more than 2 months since the most secure and powerful SQL Server yet released for public.

Masking sensitive data with SQL Server 2016

It has many features which you can use to make your data and application more secure, more maintained and easily retrieved as per your requirements. It has introduced both client side security such as Always Encrypted as well as server side security such as Data masking etc. SQL Table Hierarchy. Download source - 2 KB Introduction. 50 Important Queries In SQL Server. In this article I will explain some general purpose queries. I think each developer should have knowledge of these queries. These queries are not related to any specific topic of SQL. But knowledge of such queries can solve some complex tasks and may be used in many scenarios, so I decided to write an article on these queries. SQL Server Stored Procedure explained with Examples. 1. From SQL-Server to Web Service. Introduction One of my daily tasks is to integrate SAP's Business One ERP inside our customer's IT system. Integration is an important part of the job, since the companies for which we implement the ERP have more often than never, other systems to do complementary tasks and all these systems have to communicate together.

For one of the last projects I worked on, the need was to "deeply" integrate the ERP in the IT environment of the customer. For reasons which belong to this customer, this bi-directional integration (send modification done in the ERP, integrate modifications done in other systems) was done thru web services calls... So it means SOAP calls with xml data transfers. Easily said not so easy to implement (at least the first time.

Lead and Lag Functions in SQL Server. Introduction SQL Server 2012 introduced new analytical function LEAD() and LAG(). These functions accesses data from nth next row and nth previous row in the same result set without the use of a self-join. LEAD(): used to access data from nth next row in the same result set without the use of a self-join LAG(): used to access data from nth previous row in the same result set without the use of a self-join Using the Code Syntax. How to import data into MS SQL server from CSV and XML. SQL Server Structure Error Handling. Convert SQL Server database to PostgreSQL. A simple and another way to find rows on n’th maximum number from a table. A highly experienced leader with successful track record of software development, product innovations, brand management and corporate communication etc.

Some successful product innovations have also achieved and awards “Most Valuable Professional” (MVP) at 2010 and 2011 by codeproject.com and also selected as a mentor of codeproject.com. SQL Server Table and Column Naming Conventions. Simple Way To Use Pivot In SQL Query. Introduction This is a very simple example of Pivot query for the beginners.

SQL Server export to CSV. Using Conditional Logic in SQL with CASE Expression. The main purpose of a CASE expression is to return a value based on one or more conditional tests. Get monthly attendance report by Store Procedure in Sql. JSON for SQL Server. Part 1. Download source - 86.4 KB. A Case Study on How to use Joins, Subqueries, and Unions to Combine Data in SQL. Renaming a Database in Mysql. Present and Future of DBMSs.

MySQL

How to Convert an SQL Server Data into JSON Object. E-Surfer's Weblog: Thinking,feeling in JAVA, J2EE, XML, Web Services development. Using a Database With Your Android* App. SQL Server 2012 Metadata. A generic Collection to DataTable Mapper. SQL Server Dapper. Graph Database. Data and Goliath: The Hidden Battles to Collect Your Data and Control Your World: Bruce Schneier: 9780393244816: Amazon.com: Books.

Everything about SQL- Get Started. Using DataSets to work with (relational) in-memory data. Using C# to connect to and query from a SQL database. Table Generation Finder For Rotational Database In SQL Server. Open source solutions, PostgreSQL, Nginx, MongoDB, cloud database. Order by in SQL by Multiple Drop Down List in One Single Logic. Opencsv - Frequently Asked Questions. XML Database and XQuery to the Rescue. Mapkon/oc-conversion-tools. Printing list of ODBC data sources with pyodbc module. Data with Structure Migration from MySQL to SQL Server 2012. Algorithms, 4th Edition by Robert Sedgewick and Kevin Wayne. Caribbean Open DRI Community: Setting up your o... German Association for Medical Informatics, Biometry and Epidemiology (GMDS) Back Issues Methods of Information in Medicine. Technical Symposium on Computer Science Education. Big data. HEALTH DATA COLLECTION. Mobile. Designing robust web API.

API Design. Web API Design Cookbook. How to design an API: best practises, concepts, technical aspects. TOOLS. API. APIs.

Softwares

Comparing STRIDE-RDM, REDCap and Medrio - Services - SCCI - Stanford Medicine. System Architecture. MTD Data Capture - Data Capture. Electronic Data Capture Software Features. Project Proposal - Aneesha Arora. International Journal of Medical Informatics. Accessing MYSQL Database Using Java in Eclipse IDE. Basic SQL Queries for Beginners. Aggregate functions - SQL Server: How to handle divide by 0 cases in query. Binary Replication Tutorial - PostgreSQL wiki. Rubyrep: Replicating two databases. POSTGRESQL REPLICATION USING RUBYREP by Carmen Cuacos on Prezi. PostgreSQL Replication Tools. PL/pgSQL (en) – PostgreSQL. Index. Learn NoSQL Database Design From Scratch by Eduonix Learning Solutions. PostgreSQL. Conditional Statements in PHP code Between HTML Code. Mysql - How to assign data only to users who have a specific role?

File:RBAC.jpg. Database - How to design Users, Roles and permission schema? Relational Database Design - (Third Edition) - ScienceDirect. Database - What is the difference between 3NF and BCNF? PHP: Securing database connection credentials. Restrict Access to Tables and Fields. Nbviewer.ipython. Chapter%209%20-%20Loading%20data%20from%20SQL%20databases. Creating and using Stata plugins. FAQ: Using a plugin to connect to a database.

Free CSS Templates - Download free CSS templates. Gehrke. Sql2alg_eng. SQL Relational Algebra Examples. Relational Algebra Translator. 1NF, 2NF, 3NF and BCNF in Database Normalization. 1NF, 2NF, 3NF and BCNF in Database Normalization.