background preloader

Relational database

Facebook Twitter

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. 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. Please look up my account for my articles and go to the other article. Download demo project - 25.1 KB Introduction A simple table in a relational database is the Employees table. How to Represent the Data in the Relational Database Side One part of the technique is to create a new key column in your relational table which has the hierarchical information in it.

As you can see from this example, there are random inserts going on, so the data is out of order. The symbology is not pertinent here. Client Processing Summary Where do I Use it? Should I use it as is? This is fairly easy to do. SQL Script. Monitoring SQL Server Database. What is Monitoring? 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. How To Do It? SSMS (SQL Server Management Studio) is the tool through which you can perform various monitoring tasks. Let's See Checking Database Status The red box shows a database which is currently Offline and the green box shows 2 databases which are online at the moment.

T-SQL Script Let's check the status of Demo database using T-SQL. Hide Copy Code SELECT DATABASEPROPERTYEX('Demo', 'Status') AS DBStatus Result DBStatus OFFLINE Now, let's check the status of the Northwind database using T-SQL. SELECT DATABASEPROPERTYEX('Northwind', 'Status') AS DBStatus DBStatus ONLINE Conclusion Similarly, you can use other properties to monitor the rest of the things.

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. 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) where Property is the property you want to match to a particular value using whatever operator or method is appropriate to return the required result.

MyCollection.Where(item=>values.Any(value=>value matches item.Property)); Some Examples Problem Get all items from List1 that are one of the items in List2. SELECT Field FROM TABLE WHERE Field IN (value1, value2,....valueN) Solution List<string> values = {value1, value2...valueN}; var results = table .Select(rec=>rec.field) .Where(field=>values.Contains(field, StringComparer.CurrentCultureIgnoreCase)); 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. 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! Background SQL Server gives a big help with that, with the OLE Automation processes. Basically, you have a set of objects and methods that help you in GETting, POSTing and PUTting data from SQL procedure to a Web Service Isn't it just awesome?

I think it is, and it opens up a wide range of possibilities. Here, there is the code for building a simple Movies Database, where you can enter the name of the movie that you are interested in, and all the information related to it will be read from the network and then written into the database. Using the Code Just create a [Movies] Database and execute the scripts attached. All the information will be stored in the [MoviesInfo] table: 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.

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. Hide Copy Code Now if we just fetch the data using query like this: SELECT Item, SubItem1, SubItem2 FROM MyReportData ORDER BY Item, SubItem1; We get the following result: Item SubItem1 SubItem2 First item Sub item 1 First item Sub item 2 Extra data First item Sub item 2 Additional data First item Sub item 3 Second item Sub item 1 Info Second item Sub item 2 Data Second item Sub item 2 Second item Sub item 3 Third item Sub item 1 Nonsense Third item Sub item 1 Even more nonsense First by Item column Then by SubItem1 column References.

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. 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. In SQL Server 2016, there is an easier way to do this using comparatively less amount for coding. DROP TABLE IF EXISTS [SomeTable]; DROP PROCEDURE IF EXISTS [SomeProcedure]; Even this can be used when dropping columns and constraints from a table.

ALTER TABLE [TableName] DROP CONSTRAINT IF EXISTS [ConstraintName] ALTER TABLE [TableName] DROP COLUMN IF EXISTS [TableName] Example: Currently, the following objects can be dropped with the DIE functionality: 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. 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. In this post we’ll examine how Dynamic Data Masking can help secure our data. Earlier we were masking the sensitive data by applying logic at application end or by replacing characters at SQL Server end and then returning the masked data to the application. In the later case, the first two risks are eliminated but still there is a chance that the sensitive data can be revealed by the SQL Server user through which the application retrieves data. 1. Data Masking If you search for definition of Data Masking over the web, you’ll end up with varied descriptions. Examples: SQL Table Hierarchy. Download source - 2 KB Introduction To get an idea about a new database, or to insert records into a database, or to delete records from a database, it is necessary to know the relationships between the tables. An SQL script is provided, which retrieves and shows in an intuitive way the table hierarchy of a database.

Background SQL Server provides the table sys.foreign_keys, which holds all foreign keys of a database. Using the Code The examples are based on the AdventureWorks database of Microsoft. Foreign Keys with Columns The columns used in a foreign key, are given in the table sys.foreign_key_columns. Hide Copy Code An excerpt of the @fkcolumns table is given here: The foreign key FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID has two referenced columns and two parent columns. Parent-child Tables A foreign key links a parent table with a child table. An excerpt of the @fkrefs table is given here: Table Tree An excerpt of the top level tables is given here: History.

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. Query 1: Retrieve List of All Database Example: Query 2: Display Text of Stored Procedure, Trigger, View exec sp_helptext @objname = 'Object_Name' Query 3: Get All Stored Procedure Relate To Database SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE o.xtype='P' Example: To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 4: Get All Stored Procedure Relate To Table SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P' Query 5: Rebuild All Index of Database EXEC sp_MSforeachtable @command1="print '? ' Query: Syntax: Syntax: Syntax: SQL Server Stored Procedure explained with Examples. 1. Introduction A "Stored Procedure" in Sqlserver is an executable program unit that can be stored in the database as objects.

These stored procedures can be executed from client application programs to get good performance. The stored procedure can be called from other scripting or even from some other stored procedures. The communication between SqlServer Stored procedure and client application happens through parameters and return values. Client applications optionally sends data to the stored procedure and stored procedures perform processing based on the data sent by the client application. We will see all these stuff in this article. 2. You can create a stored procedure in the SQL Server Management Studio. The structure of the stored procedure is shown in Fig.2.

In the Fig.3 below, a simple stored procedure is shown skipping all the optional parts of the procedure. The below-given video, shows creating and executing a simple stored procedure using SQL Server Management Studio: 3. 4. 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.

I'm not here to discuss the pros and cons of this choice but to present a specific problem (and the solution given): some of the outgoing calls from the ERP had to be synchronal from the modifications done by the user up to the return of the web service call. ). Background Global architecture So, where are we now? 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 Hide Copy Code LEAD (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause ) LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause ) Parameters scalar_expression: column name for which value needs to be accessed offset: nth previous (for lag) or next (for lead) row to access the column default: default value to display if nth row does not exist Example 1 Example 2 (With Partition By)

How to import data into MS SQL server from CSV and XML. Introduction A previous article I wrote on 'DBA Skills for Developers' gave a round up of various tips and tricks to make the life of a developer who had 'inherited' the task of in-house DBA hopefully a bit better. I was going to expand that article and add information on importing data to SQL server, but decided it is a topic and article that will grow over time so I decided to break it out on its on - and this is it. Importing data to SQL Server While the wonderful amazing database those lovely people who pay you designed trundles along and does its thing, generating its own data from simple in-app crud and pulling data perhaps from web-services and other cool sources, the day will come when you are presented with a big lump of flat-file style data in the format perhaps of CSV or XML, and told - import that please....

#groan I have found that its generally better to work with the tools provided by the clever people who designed and maintain MS SQL server. . (1) Bulk CSV data loader History. SQL Server Structure Error Handling. Introduction SQL features for report exceptions are very limiting; all that is really available is the single text string passed as an argument to the “RAISERROR” error statement. As a developer I would very much like to include the information below, but require very little coding when reporting the error. A unique error number to identify the error. This is required for application documentation, where a list of error message and some unique error number is commonly required. A message that can be displayed to a general user of the application.

A specific message to a developer using the SQL code what the problem is. Approach SQL really only provides two basic tools for reporting and processing exceptions, “RAISERROR” to report exceptions and TRY-CATCH block for exception processing. I propose the best approach is to construct an XML document containing the error information required and send the XML document as a string within the “RAISERROR” mechanism. Hide Copy Code Auditing. Convert SQL Server database to PostgreSQL. Introduction This console application converts an SQL Server database to PostgreSQL. The AdventureWorksLT2008R2 database was used for testing.

The application uses Npgsql (.NET data provider for PostgreSQL). Npgsql will be installed automatically by the NuGet package manager. Background I first tried CSV export and import to convert an SQL Server database to PostgreSQL, but this failed to copy binary fields properly as PostgreSQL expects binary data in it's own proprietary format. Command line usage CopySqlServerToPostgresql [catalogname] [catalogname postgres] If the second argument is omitted, the same name is used for the PostgreSQL database. Example: Hide Copy Code CopySqlServerToPostgresql AdventureWorksLT2008R2 Using the code Both SQL Server (or SQL Server Express) and PostgreSQL need to be installed (PostgreSQL portable can also be used). A new PostgreSQL database with tables and Primary Keys will be created. #if DEBUG Catalog = "AdventureWorksLT2008R2"; Catalog2 = Catalog.ToLower(); History. A simple and another way to find rows on n’th maximum number from a table.

SQL Server Table and Column Naming Conventions. Simple Way To Use Pivot In SQL Query. SQL Server export to CSV. Using Conditional Logic in SQL with CASE Expression. Get monthly attendance report by Store Procedure in Sql. JSON for SQL Server. Part 1. 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. 25 Features developers are missing when not using Postgres! | 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... | Collaboration for Development. German Association for Medical Informatics, Biometry and Epidemiology (GMDS) | IMIA - International Medical Informatics Association. 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. APIs.

Softwares

Comparing STRIDE-RDM, REDCap and Medrio - Services - SCCI - Stanford Medicine. Entrypoint :: System Architecture. MTD Data Capture - Data Capture. Overture EDC | Electronic Data Capture Software Features | Forte. Project Proposal - Aneesha Arora. International Journal of Medical Informatics | Vol 83, Iss 5, Pgs 313-392, (May, 2014)

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. Functional Dependency (Normalization)