You ask if you can have more than one primary key field and you most certainly can. You can have only one primary key, but that can consist of as many columns as you need to uniquely identify your rows. Use something like this when you are creating your table: CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) … Read more
Returning a Table Variable will make it a multi-statement table valued function and can be bad for performance due to the fact that it’s treated like a table except there are no statistics available for SQL Server to base a good execution plan on – so it will estimate the function as returning a very … Read more
Check out the documentation for the SELECT statement, in particular this section: Logical Processing Order of the SELECT statement The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, … Read more
You are correct, it is a bug in SSMS. I’m using SQL Server Management Studio 2008 R2 and when I try to create a View using the built-in designer, I get the same error message as you: SQL Execution Error Error Source: .Net SqlClient Data Provider Error Message: Cannot call methods on date. As @Aaron … Read more
You can’t use variable names to bind columns or other system objects, you need dynamic sql DECLARE @value varchar(10) SET @value=”intStep” DECLARE @sqlText nvarchar(1000); SET @sqlText = N’SELECT ‘ + @value + ‘ FROM dbo.tblBatchDetail’ Exec (@sqlText)
You can use a ranking function and a common table expression. WITH e AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ApplicationId ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC ) AS Recency FROM [Event] ) SELECT * FROM e WHERE Recency = 1
declare @D as datetime=”2012-01-02T12:04:11.443″ select dateadd(hour, datediff(hour, 0, @D), 0) select dateadd(minute, datediff(minute, 0, @D), 0)
I think you can hit CTRL + C and CTRL + V without any text selected and you can duplicate the line below it. Edit: works in SSMS18
You can grant them the VIEW DEFINITION privilege to those procs. See here for what this privilege does. You can apply VIEW DEFINITION at different scopes: Server Database Schema Individual entities (e.g. a proc, a function, a view) You can also use a query to generate a script for many procs. So if you have … Read more
The [name] field in sys.objects will contain only the actual name (i.e. trg), not including the schema (i.e. dbo in this case) or any text qualifiers (i.e. [ and ] in this case). AND, you don’t specify the table name for DROP TRIGGER since the trigger is an object by itself (unlike indexes). So you … Read more