cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a, cte1 b, cte1 c, cte1 d) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)
Select Key_PS = Row_Number() over (Order By A.N) ,Key_Value = Substring(@String, A.N, A.L) ,Key_Pos = A.N From cte4 A
Select Key_PS = Row_Number() over (Order By (Select null)), Key_Value = LTrim(RTrim(B.i.value('(./text())', 'varchar(max)'))) From (Select x = Cast(''+ Replace(@String,@Delimiter,'')+'' as xml).query('.')) as A Cross Apply x.nodes('x') AS B(i)
Log Shipping:: It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.
Mirroring:: Database mirroring is a primarily software solution for increasing database availability. It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
Replication:: It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
How do you go about transferring a disk-based workload to the respective memory-optimized design? How do you process memory-optimized tables? How important for performance are natively-compiled Stored Procedures? Artemakis Artemiou comes up with a step-by-step guide to implementing an in-memory OLTP solution.
Every new version of SQL Server should have its own Adventureworks database. The reason is that SQL Server comes up with new features with every version and most of the new features need a new dataset sample to demonstrate the capabilities of the features. This is the why every version of SQL Server has its own AdventureWorks database.
A common scenario in many client-server applications is allowing the end user to dictate the sort order of results. Some people want to see the lowest priced items first, some want to see the newest items first, and some want to see them alphabetically. This is a complex thing to achieve in Transact-SQL because you can't just throw @SortColumn and @SortDirection into a straight T-SQL query.
T-SQL supports the OUTPUT clause after the inception of SQL server 2005 and later editions. We can use the OUTPUT clause with DML statements (INSERT, DELETE, UPDATE) to return information from modified rows.
We primarily use the OUTPUT clause for auditing and archiving modified rows. In this tutorial, we will walk through the use of the OUTPUT clause with different DML statements and examples. First, we will create a table, dbo.Songs, and populate it with some data.
It is very common in information systems to have functions where the users are able to search the data by selecting freely among many possible criterias. When you implement such a function with SQL Server there are two challenges: to produce the correct result and have good performance.
With a good SQL Server disaster recovery strategy, data recovery is not a problem. You have a reliable full database backup with valid data, a full database backup and subsequent differential database backups, or a full database backup and a chain of transaction log backups. But not all situations are ideal. Here we’ll address some sub-optimal scenarios, where you can still successfully recover data.
Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no e-mail is being sent. So how do you send an alert to an operator?
The Porter stemming algorithm (or ‘Porter stemmer’) is a process for removing the commoner morphological and inflexional endings from words in English. Its main use is as part of a term normalisation process that is usually done when setting up Information Retrieval systems.
select SchemaName = s.name, TableName = t.name from sys.schemas as s inner join sys.tables as t on s.schema_id = t.schema_id inner join sys.columns as c on t.object_id = c.object_id inner join sys.identity_columns as ic on c.object_id = ic.object_id and c.column_id = ic.column_id group by s.name, t.name order by s.name, t.name;
Amazon Web Services (AWS) is a flexible, cost-effective, easy-to-use cloud computing platform. Relational database management systems, or RDBMS, are widely deployed within the Amazon cloud. In this whitepaper, we help you understand how to deploy SQL Server databases on AWS. You can run SQL Server databases on Amazon Relational Database Service (Amazon RDS) or Amazon Elastic Compute Cloud (Amazon EC2).
Columnstore indexes were first released with SQL server 2012 and have now gained a shiny new polish with the release of SQL server 2014. MSDN claims an increase of performance of up to 10 times can be gained by using column store indexes. But what are they and how are they better?
In SQL Server, the classic stored procedures that we know and love are interpretive. This means that when a stored procedure is executed for the first time, the SQL Server relational engine first compiles it, and then executes it. By contrast, when we create a natively compiled stored procedure, SQL Server compiles it into machine code immediately, and stores it in a DLL.
As such, it's typically a good idea to save in-memory OLTP, as well as in-memory stored procedures for applications where speed and efficiency are of the utmost importance. However, for those tables and procedures that you can move to in-memory, you will often see a huge improvement in performance over standard disk-based stored procedures.
There are two kinds of database indexes: unique and non-unique. Unique indexes and non-unique indexes are different. They are as different as night and day. They are as different as men and women. They are as different as code and data. In fact, unique indexes are code and non-unique indexes are data, and that's how they should be treated.
In this article we will discuss on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF. Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. As a better practice we should always use SET QUOTED_IDENTIFIERS ON setting.
Irawan Soetomo's insight:
SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL). https://msdn.microsoft.com/en-us/library/ms174393.aspx
The directories for the default data files, log files and backups are stored in the system registry. With SSMS you had the ability to change the backup location, but this was removed from SSMS for SQL 2005 and was later put back into SSMS with a later release of SQL Server.
It is quite tough to decide what kind of high availability solution is available on the table and which one can fit all your production server requirements in terms of licenses, hardware cost, maintenance and professional services. In this blog post, I would like to explain every single SQL Server high availability solution that is currently feasible with the related pros and cos.
Sharing your scoops to your social media accounts is a must to distribute your curated content. Not only will it drive traffic and leads through your content, but it will help show your expertise with your followers.
How to integrate my topics' content to my website?
Integrating your curated content to your website or blog will allow you to increase your website visitors’ engagement, boost SEO and acquire new visitors. By redirecting your social media traffic to your website, Scoop.it will also help you generate more qualified traffic and leads from your curation work.
Distributing your curated content through a newsletter is a great way to nurture and engage your email subscribers will developing your traffic and visibility.
Creating engaging newsletters with your curated content is really easy.