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

[Snippet] CSS - Child element overlap parent

I searched from somewhere and found that a lot of people says a basic concept for implementing this feature looks like below: HTML code: <div id="parent">  <div id="child">  </div> </div> And, CSS: #parent{   position: relative;   overflow:hidden; } #child{   position: absolute;   top: -1;   right: -1px; } However, I had a lot of grand-parents in my case and the above code didn't work. Therefore, I needed an alternative. I presumed that my app uses Boostrap and AngularJs, maybe some CSS from them affects mine. I didn't know exactly the problem, but I believed when all CSS is loaded into my browser, I could completely handle it. www.tom-collinson.com I tried to create an example to investigated this problem by Fiddle . Accidentally, I just changed: position: parent; to position: static; for one of parents -> the problem is solved. Look at my code: <div class="modal-body dn-placeholder-parent-positi...

Selenium - Override javascript functions to ignore downloading process

I have got an issue with downloading process on IE 8. This browser blocks my automatic-download functionality on my app so that I could not work with my test case any more after that. In my case, I didn't care about the file is downloaded or not, I just focus on the result after downloading process finished successfully. Therefore, I found a solution to ignore this process so that I can work normally. I use Primefaces, here is the remote command to trigger the download process <p:remoteCommand name="cmdGenerateDocument" actionListener="#{logic.onGenerateDocument}" update="xrflDocumentCreationPanel" oncomplete="clickDownloadButton();"/> The following is my test case: @Test public void shouldUpdateStep6ToWarningIfStep1IsValidAfterFinished(){ MainPage mainPage = new MainPage(); waitForLoading(mainPage.getDriver()); EmployeeDetailPage empDetailPage = new EmployeeDetailPage(); waitForLoading(empDetailPage.getDriver()); e...

Coding Exercise, Episode 1

I have received the following exercise from an interviewer, he didn't give the name of the problem. Honestly, I have no idea how to solve this problem even I have tried to read it three times before. Since I used to be a person who always tells myself "I am not the one good at algorithms", but giving up something too soon which I feel that I didn't spend enough effort to overcome is not my way. Then, I have sticked on it for 24 hours. According to the given image on the problem, I tried to get more clues by searching. Thanks to Google, I found a similar problem on Hackerrank (attached link below). My target here was trying my best to just understand the problem and was trying to solve it accordingly by the Editorial on Hackerrank. Due to this circumstance, it turns me to love solving algorithms from now on (laugh). Check it out! Problem You are given a very organized square of size N (1-based index) and a list of S commands The i th command will follow t...

[Snippet] Generate a new unique "name" string from an existing list

Suppose that we have a list of employees. Everytime, we want to add new employee into this list, the name of the employee will be generated with the following rules: - the name of the new one is set to " [originalname] 1 " - in case the name already exist, " [originalname] 2 " is used, and so on. Here is my code snippet by Javascript: var employees =[ {id: 1, name: 'name'}, {id: 2, name: 'name 1'}, {id: 3, name: 'name 2'}, {id: 5, name: 'name 4'} ]; var commonUtils = { isExistName: function(_name, _collection, _prop) { for(var i = 0; i< _collection.length; i++){ if(_collection[i][_prop].localeCompare(_name)==0){ return true; } } return false; }, generateNewName: function(_name, _collection, _prop){ var i = 1; var searching = true; while (searching) { var newName = _name+ " " + i; if (!this.isExistName(newName, _collection, _pro...

Avoiding Time-Wasting Pitfalls in Agile Estimation

If you do Scrum at work, you might be very familiar to the estimation in Planning 1 . My PO has once complained to my team that why it took too long for estimating just a story. Wasting time results in the planning timebox is violated. I give you some advice from my experience: Estimation is estimation, not measure. When you read some requirements, you see some risks but you actually don't know how complicated it will be.  Don't try to influence the others by explaining how to do it in too detail. Just keep in mind that you know the business domain pertaining to customer needs and estimate how much effort you will spend for it. The effort should be compared to your baseline one that you use for a simple requirement. The bottom line is we do "relative estimation", not absolute estimation. For example, you are asked to estimate the height of a building. Basically, you just need to answer "how many times higher is the build than your height"; you do...