A Recovery Model is property of a database that control how transaction log is maintained. SQL Server supports simple, FULL and BULK-LOGGED recovery models. There are multiple ways in which to check recovery model of a database in SQL Server.

1. Using SQL Server Management Studio:
Right click on database in Object explorer -> Go to Properties dialog box -> Options page -> Recovery model

2. Using Metadata function – DATABASEPROPERTYEX():
SELECT [RecoveryModel] = DATABASEPROPERTYEX('SqlAndMe','Recovery')
GO 

Result Set: 
RecoveryModel
SIMPLE 

3. Using catalog view – sys.databases:
SELECT [DatabaseName] = name,
       [RecoveryModel] = recovery_model_desc
FROM   sys.databases
GO  

Result Set:
DatabaseName   RecoveryModel
master         SIMPLE
tempdb         SIMPLE
model          FULL
msdb           SIMPLE
Pubs           SIMPLE
EuWindows      SIMPLE
TestDB         SIMPLE
ProductCatalog SIMPLE
ReportDemo     SIMPLE
ReportServer   FULL
ReportServerTempDB  SIMPLE

(11 row(s) affected)

Using sys.databases catalog view is easier as it returns information of all databases on server. Hope this tutorial works for you!

In SQL Server there is not any inherent capacity accessible for discovering the number of words in a String. Here I reveal to both of you diverse methodologies for doing this, the first is the most simpleone, and is applicable only of these words are separated by a single space.

DECLARE @String VARCHAR(4000)
SELECT @String = 'SQL Server 2005'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

As I said prior, the above query will provides for you the right result, just if the words are differentiated with a solitary space. Presently on the off chance that they are differentiated by more than one space, this will provide for you off base results as the results are basically relied on upon  Length of the original string. Along these lines, what will be the arrangement, simply compose a function  to do this.

CREATE FUNCTION dbo.udfWordCount(
@OriginalText VARCHAR(8000)
)
RETURNS int
as
/*
SELECT dbo.udfWordCount ('hello   world')
*/
BEGIN
    DECLARE @i int ,@j INT, @Words int
    SELECT     @i = 1, @Words = 0
    WHILE @i <= DATALENGTH(@OriginalText)
    BEGIN
        SELECT    @j = CHARINDEX(' ', @OriginalText, @i)
       if @j = 0
        BEGIN
            SELECT    @j = DATALENGTH(@OriginalText) + 1
        END
        IF SUBSTRING(@OriginalText, @i, @j - @i) <>' '
              SELECT @Words = @Words +1
        SELECT    @i = @j +1
    END
    RETURN(@Words)
END
GO
SELECT dbo.udfWordCount ('SQL Server2012')
SELECT dbo.udfWordCount ('SQL Server 2012 ')

Today, I am going to tell you how to replace the special characters in a string with spaces. In this case, I need to use PATINDEX.

PATINDEX
It will returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. And this is the code that I used:
PATINDEX ( '%pattern%' , expression )

Example:
DECLARE @Str varchar(100)
SET @Str='Welcome!@+to+#$%SQL+^&*(SERVER)_+'
SELECT PATINDEX('%SQL%', @Str)

Here is the result from that code:

Remove Special Characters from String in SQL Server DECLARE @regexp INT
DECLARE @Str varchar(100)
SET @Str='Welcome!@+to+#$%SQL+^&*(SERVER)_+   '
SET @regexp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str)
WHILE @regexp > 0
BEGIN
SET @Str = STUFF(@Str, @regexp, 1, ' ' )
SET @regexp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str)
Print @regexp
END
SELECT @Str

Result:

STUFF
This STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. This is the code:
STUFF ( character_expression , start , length , replaceWith_expression )

Example:
DECLARE @regexp INT
DECLARE @Str varchar(100)
SET @Str='welcome to sql server'
SET @Str = STUFF(@Str, 1, 1, '@' )
Select @str 

From SQL Server 2005, all tables are grouped into schemas. Even though making a table in case the schema name isn't specified it's developed inside the default schema from the user making it. You are able to use ALTER SCHEMA command to move tables in among schemas. For instance, in case I develop a table using below script it is going to be developed below my default schema that is dbo:
USE [hostsql]
GO
CREATE TABLE Employee
(
       ID     INT,
       Name VARCHAR(20)
)
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM   sys.tables
WHERE  name = 'Employee'
GO

Result Set:
name            schema
Employee      dbo
(1 row(s) affected)

As you are able to notice coming from the output the table is currently in dbo schema. Currently to move this table to another schema utilizing ALTER SCHEMA command, first we have to create the schema in case it doesn't exist by now. When that many of us can move table to new schema.
USE [SqlAndMe]
GO CREATE SCHEMA Staff
GO 
ALTER SCHEMA Staff
TRANSFER dbo.Employee
GO 
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM   sys.tables
WHERE  name = 'Employee'
GO

Result Set:
name            schema
Employee      Staff 
(1 row(s) affected)

As you can see from the result, the table of Employee is now moved to Staff schema.

Today, I will write about How to fix Error:  “The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node" on SQL Server 2012. And this is the error message:

The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

The local node is not part of quorum and is therefore unable to process this operation. This may be due to one of the following reasons:

• The local node is not able to communicate with the WSFC cluster.
• No quorum set across the WSFC cluster.

The local node isn’t part of quorum and so unable to process that operation.This prompt me that perhaps the second instance doesn't understand itself its HADR enable. thus I qery sys.dm_hadr_cluster_members and sys.dm_hadr_cluster for both nodes.

On node1, it will come correct information. On node2, the result's empty.
SELECT *
FROM sys.dm_hadr_cluster_members;
Go
select * from sys.dm_hadr_cluster

The way to fix it's disable the HADR from SQL Server configuration manager . Bounce SQL Server and SQL agent. rentable HADR and bounce SQL server and SQL agent. The issue was resolved after second bounce

.

Here, I have provided an article showing you how to utilize the two new logical functions Choose and IIF in SQL Server. The Choose function works like an array kind of thing and the IIF function is used to check a condition. In this article we will see both functions with examples. These functions are also called new logical functions in SQL Server 2012. So let's take a look at a practical example of how to use the Choose and IIF functions in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

These are the two logical functions:

1. IIF() Function
2. Choose() Function

IIF() Function

The IIF function is used to check a condition. Suppose X>Y. In this condition a is the first expression and b is the second expression. If the first expression evaluates to TRUE then the first value is displayed, if not the second value is displayed.

Syntax

IIF ( boolean_expression, true_value, false_value )

Example

DECLARE @X INT;
SET @X=50;
DECLARE @Y INT;
SET @Y=60;
Select iif(@X>@Y, 50, 60) As IIFResult

In this example X=50 and Y=60; in other words the condition is false.  Select iif(@X>@Y, 50, 60) As IIFResult. It returns false value that is 60.

Output

Choose() Function

This function is used to return the value out of a list based on its index number. You can think of it as an array kind of thing. The Index number here starts from 1.

Syntax

CHOOSE ( index, value1, value2.... [, valueN ] )

CHOOSE() Function excepts two parameters,

Index: Index is an integer expression that represents an index into the list of the items. The list index always starts at 1. 

Value: List of values of any data type.

Now some facts related to the Choose Function

1. Item index starts from 1

DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H') As ChooseResult 

In the preceding example we take index=5. It will start at 1. Choose() returns T as output since T is present at @Index location 5.

Output

2.  When passed a set of types to the function it returns the data type with the highest precedence; see:

DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex ,35,42,12.6,14,15,18.7)  As CooseResult

In this example we use index=5. It will start at 1. Choose() returns 15.0 as output since 15 is present at @ShowIndex location 5 because in the item list, fractional numbers have higher precedence than integers.

3. If an index value exceeds the bound of the array it returns NULL

DECLARE @ShowIndex INT;
SET @ShowIndex =9;
Select Choose(@ShowIndex , 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index=9. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last Index=8.

Output

4. If the index value is negative then that exceeds the bounds of the array therefore it returns NULL; see:

DECLARE @ShowIndex INT;
SET @ShowIndex =-1;
Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index= -1. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array.

Output

5. If the provided index value has a float data type other than int, then the value is implicitly converted to an integer; see:

DECLARE @ShowIndex  INT;
SET @ShowIndex =4.5;
Select Choose(@ShowIndex ,35,42,12.6,13,15,20) As CooseResult

In this example we take index= 4.5. It will start at 1.  If the specified index value has a float data type other than int, then the value is implicitly converted to an integer. It returns the 13.0 as output since 15 is present at @ShowIndex=4.5 which means index is 4.

Output

Microsoft SQL Server 2012 extends the EXECUTE statement to introduce WITH RESULT SETS option which can be used to change the Column Name and Data Types of the result set returned by the execution of stored procedure.

Example Using WITH RESULT SETS Feature of SQL Server 2012

Let us go through an example which illustrates WITH RESULT SETS Feature of SQL Server 2012.

Use AdventureWorks2008R2
GO

IF EXISTS (
SELECT * FROM sys.objects    
WHERE object_id = OBJECT_ID(N'[dbo].[WithResultSets_SQLServer2012]')   
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WithResultSets_SQLServer2012]
GO

CREATE PROCEDURE WithResultSets_SQLServer2012
AS   
BEGIN      
SELECT                 
 TOP 5                 
                                                 PP.FirstName + ' ' + PP.LastName AS Name            
,PA.City               
,PA.PostalCode         
FROM  Person.Address PA            
INNER JOIN                   
Person.BusinessEntityAddress PBEA                          
ON PA.AddressID = PBEA.AddressID               
INNER JOIN                         
Person.Person PP                         
ON PBEA.BusinessEntityID = PP.BusinessEntityID       
ORDER BY PP.FirstName
      END
GO

Once the stored procedure is created successfully. The next step will be to execute the above stored procedure using WITH RESULT SET Feature of SQL Server 2012.

/* Execute Stored Procedure which uses WITH RESULT SETS  Feature of SQL Server 2012*/
EXEC WithResultSets_SQLServer2012GO
/*
 Example - Using WITH RESULT SETS Feature of SQL Server 2012
*/
EXEC WithResultSets_SQLServer2012
WITH RESULT SETS
(
 (
  [Employe Name]  NVARCHAR(100),
  [Employee City]       NVARCHAR(20),
  [Employee Postal Code]      NVARCHAR(30)
 )
)
GO

In the above image you could see that once you execute WithResultSets_SQLServer2012 stored procedure using WITH RESULT SET feature of SQL Server 2012 you can change the Column Name and Data Type as per your need without actually altering the exisiting stored procedure. In the second result set (above image) you could see that the Column Names are changed from Name to Employee Name, City to Employee City and PostalCode to Employee Postal Code. Similary, the data type was changes from VARCHAR to NVARCHAR.

Conclusion

The WITH RESULT SET Feature of SQL Server 2012 is a great enhancement to the EXECUTE Statement. This feature will be widely used by Business Intelligence Developers to execute a stored procedure with in an SQL Server Integration Services (SSIS) Package to return the result set with required Columns and modified data types.

In SQL Server 2012, there is a new data model, called tabular, that is part of the new feature called the Business Intelligence Semantic Model (BISM).  BISM also includes the multidimensional model (formally called the UDM).

The Tabular model is based on concepts like tables and relationships that are familiar to anyone who has a relational database background, making it easier to use than the multidimensional model.  The tabular model is a server mode you choose when installing Analysis Services.

The tabular model is an enhancement of the current PowerPivot data model experience, both of which use the Vertipaq engine.  When opening a PowerPivot for SharePoint workbook, a SSAS cube is created behind the scenes, which is why PowerPivot for SharePoint requires SSAS to be installed.

So if tabular models and PowerPivot models use the same Analysis Services engine, why are tabular models necessary when we already have PowerPivot?

There are four things that tabular models offer that PowerPivot models does not:

1. Scalability - PowerPivot has a 2 GB limit for the size of the Excel file and does not support partitions, but tabular model have no limit and support partitions.  Tabular models also support DirectQuery
2. Manageability – There are a lot of tools you can use with the tabular model that you can’t use with PowerPivot: SSMS, AMO, AMOMD, XMLA, Deployment Wizard, AMO for PowerShell, and Integration Services
3. Securability – Tabular models can use row security and dynamic security, neither of which PowerPivot supports, only Excel workbook file security
4. Professional development toolchain - Tabular models live in the Visual Studio shell.  Thus, they enjoy all the shell services such as integrated source control, msbuild integration, and Team Build integration.  PowerPivot lives in the Excel environment, thus it is limited to the extensibility model provided in Excel (which doesn’t include source control or build configuration).  Also, because tabular models live in the VS environment, build and deployment can be naturally separated

So Analysis Services can now be installed in one of three server modes: Multidimensional and Data Mining (default), PowerPivot for SharePoint, and Tabular.

More info:

When to choose tabular models over PowerPivot models

Comparing Analysis Services and PowerPivot

Feature by Server Mode or Solution Type (SSAS)