Create schema with separate autorization

Perform the following steps in SQL Server to create a schema called "bi" and give create view rights to user "domain\testuser":

CREATE SCHEMA [bi]
CREATE ROLE [db_bischema]
ALTER AUTHORIZATION ON SCHEMA::bi TO [db_bischema]
GRANT CREATE VIEW TO [db_bischema] -- "create view" includes the power to drop views
ALTER ROLE [db_bischema] ADD MEMBER [domain\testuser]

Advantages:
  • If views are removed from or added to the schema, autorization does not need to be added to each individual view
  • Give certain users the rights to (re)create the views inside the schema, without the risk of modification of the rest of the database

Pass table-valued parameter from VB.Net

1) Create table type itemtabletype for a table with one, unique clustered column called "item":

create type itemtabletype
as table (
item nvarchar(30) not null,
primary key clustered (item)
)

2) Create a procedure cst_testtabletype that has itemtable type as a table-valued input parameter:

create procedure cst_testtabletype
( @tv itemtabletype readonly )
as
begin
declare @itemcount int = 0
select @itemcount = count(*) from @tv
select concat('succesfully parsed item ',item) from @tv
end

The procedure returns all items provided in the table-valued parameter prefixed with the words "successfully parsed item ".

3) Give execution rights to user(s) and/or groups that are allowed to execute the procedure and table type:

grant exec on type::itemtabletype to [User]
grant exec on cst_testtabletype to [User]

4) Finally, create the following test routine  in VB.Net:

    Public Sub Start()

        'create data table
        Dim Table1 As DataTable

        'create a table named tmptbl
        Table1 = New DataTable("tmptbl")

        Dim row As DataRow

        Try

            'declare a column named item
            Dim item As DataColumn = New DataColumn("item")

            'setting the datatype for the column
            item.DataType = System.Type.GetType("System.String")

            'adding the column to table
            Table1.Columns.Add(item)

            'declaring a new row
            Dim i As Integer
            For i = 1 To 1000
                row = Table1.NewRow()
                row.Item("item") = i
                Table1.Rows.Add(row)
            Next

        Catch

        End Try

        'establishing connection. you need to provide password for SQL server
        Dim myConnection = New SqlConnection()

        myConnection.ConnectionString = "Server=mySQLServer;Database=myDB;Trusted_Connection=True;"

        Try

            myConnection.Open()

            Dim myCommand As New SqlCommand("cst_testtabletype", myConnection)

            ' the table-valued parameter is called @tv for cst_testtabletype
            Dim myparam As SqlParameter = myCommand.Parameters.Add("@tv", SqlDbType.Structured)
            'create parameter
            myCommand.CommandType = CommandType.StoredProcedure
            myparam.Value = Table1

            Dim dr As SqlDataReader = myCommand.ExecuteReader()
            While dr.Read()
                Debug.Print(dr.Item(0))
            End While

        Catch ex As SqlException

            MsgBox(ex.Message)

        End Try

    End Sub

IsNumeric() stinks

Read the thoroughly insightful and funny post by Mike Teevee as a reaction to the technet documentation with regards to the useless ISNUMERIC() function introduced in SQL Server 2012:
http://technet.microsoft.com/en-us/library/ms186272.aspx

Select "SQL Server 2012" under "Other Versions", otherwise the post is hidden.