Skip to main content

MS SQL Server Views

"Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:

- To focus, simplify, and customize the perception each user has of the database.
- As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
- To provide a backward compatible interface to emulate a table whose schema has changed." [1]

Beside that, our team used view in order to improve the performance of our web apps when a database has a very complicated relationship between its tables by using ORM Frameworks such as Hibernate.

Example code:
--create
CREATE VIEW placeholders
AS
SELECT     EMPKEY AS empkey, CONNUMB AS connumb, EMPNBR AS empNbr, ACEEMPN AS empFirstName, ACEEMPFN AS empLastName, EMPNAM AS empFullName, 
                      EMPSIGN AS empSign, AECSALUT AS empSalutation, AECTITLE AS empTitle
FROM         dbo.HRMAEC

--update
ALTER VIEW placeholders AS
SELECT EMPKEY AS empkey, CONNUMB AS connumb, EMPNBR AS empNbr, ACEEMPN AS empFirstName, ACEEMPFN AS empLastName, EMPNAM AS empFullName1, 
                      EMPSIGN AS empSign, AECSALUT AS empSalutation, AECTITLE AS empTitle,AECEMAIL As empEmail
FROM HRMAEC


-- drop
DROP VIEW placeholders

--select
SELECT * FROM placeholders
WHERE empkey = 1 AND connumb = 1


Reference:
[1]. https://msdn.microsoft.com/en-us/library/ms187956.aspx

Comments

Popular posts from this blog

My 2017 Review

Passion for System Design After finishing a one year project, my longest stable team (3 years) was separated into two smaller teams. Sadly, but that was a good chance for me to become a key member in my new team. My preferred skills is about system architectures; therefore most of the tasks of building the application structures are handled by me. In order to enhance my design system skills, I have spent much my time for reading books closely after work. These following books helps me a lot.
- Object-Oriented Thought Process | Matt Weisfeld
- Head First Design Pattern | Elisabeth Freeman and Kathy Sierra
- Java 8 in Action: Lambdas, Streams, and Functional-style Programming | Alan Mycroft and Mario Fusco
Junior Technical Architect I was requested to join a technical architect team (aka Team. Alpha) where I actually has gained experiences almost on interviewing candidates for my company (lol). Besides, I noticed myself must improve the skills of convincing people because I have had a …

AngularJS - Build a custom validation directive for using multiple emails in textarea

AngularJS already supports the built-in validation with text input with type email. Something simple likes the following:
<input name="input" ng-model="email.text" required="" type="email" /> <span class="error" ng-show="myForm.input.$error.email"> Not valid email!</span>
However, I used a text area and I wanted to enter some email addresses that's saparated by a comma (,). I had a short research and it looked like AngualarJS has not supported this functionality so far. Therefore, I needed to build a custom directive that I could add my own validation functions. My validation was done only on client side, so I used the $validators object.

Note that, there is the $asyncValidators object which handles asynchronous validation, such as making an $http request to the backend.

This is just my implementation on my project. In order to understand that, I supposed you already had experiences with Angular…

Strategy Design Pattern

For example, I have an program with an Animal abstract class and two sub-classes Dog and Bird. I want to add a new behavior for the class Animal, this is "fly".  Now, I face to two approaches to solve this issue:

1. Adding an abstract method "fly" into the class Animal. Then, I force the sub-classes should be implemented this method, something like:

public abstract class Animal{ //bla bla public abstract void fly(); } public class Bird extends Animal{ //bla bla public void fly(){ System.out.println("Fly high"); } } public class Dog extends Animal{ //bla bla public void fly(){ System.out.println("Cant fly"); } }
2. Creating an interfaces with method "fly" inside. The same issue to abstract class, I force the classes these implement this interface should have a method "fly" inside:

public interface Flyable{ public void fly(); } public class Bird implements Flyable{ //bla bla public void fly(){ System.out.println…

How did I start practising BDD?

At the beginning days I have practised TDD (Test Driven Development) with using JUnit, I approached that I should test methods belong to a class. For example:

I have a class with some methods:

public class A{ public void method1(){ } public void method2(){ } }
And then, I wrote some test methods to check the corresponding ones, for example:

public class ATest{ @Test public void testMethod1(){ .... assertTrue(...); ..... assertEquals(...); } @Test public void testMethod2(){ } }
After that, I know that a test method (ex: testMethod1) should just only tests one thing, so I decided to write more methods for each cases. It looks like the following:

@Test public void testMethod1_When_Case1(){ .... assertTrue(...); } @Test public void testMethod1_When_Case2(){ .... assertEquals(...); }
However, it was not a really good approach because it seems that I just focused on test the functionality of the method of the class. With TDD approach, I knew that I should test the…

Styling Sort Icons Using Font Awesome for Primefaces' Data Table

So far, Primefaces has used image sprites for displaying the sort icons. This leads a problem if we want to make a different style for these icons; for example, I would make the icon "arrow up" more blurry at the first time table loading because I want to highlight the icon "arrow down". I found a way that I can replace these icons to Font Awesome icons.


We will use "CSS Pseudo-classes" to archive it. The hardest thing here is that we should handle displaying icons in different cases. There is a case both "arrow up" and "arrow down" showing and other case is only one of these icons is shown.

.ui-sortable-column-icon.ui-icon.ui-icon-carat-2-n-s { background-image: none; margin-left: 5px; font-size: 1.1666em; position: relative; } .ui-sortable-column-icon.ui-icon.ui-icon-carat-2-n-s:not(.ui-icon-triangle-1-s)::before { content: "\f106"; font-family: "FontAwesome"; position: absolute; t…