Skip to main content

Posts

Showing posts with the label SQL Server

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,

SQL Server - Multi language data support

Problem: I used CKEditor to edit my pages. Then, I stored the content of the pages to SQL Server 2008 database. The page contents should support in different languages such as English, German and Vietnamese. I met a problem when I worked on Vietnamese pages. The text was successfully saved but it was displayed wrong when reading again from database, likes the following: Input: "Ðây là Tiếng Việt" Display: "Ðây là Ti?ng Vi?t". Solution: We need to use nvarchar (nchar | ntext) data type for strings  and we also need to precede all unicode strings with ' N ' Here is my code: --create table CREATE TABLE [dbo].[xedu_page_content]( [id] [numeric](11, 0) IDENTITY(1,1) NOT NULL, [category] [varchar](100) NOT NULL PRIMARY KEY, [content] [Ntext] NULL ) --save BEGIN TRAN IF not exists (SELECT category FROM xedu_page_content WHERE category='"+in.categoryKey+"') INSERT INTO xedu_page_content(category,content) VALU