Earlier today, I realised that I had made a very stupid mistake. Rather than write a view, I wrote a parameterless inline table-valued function. This got me thinking: Interface aside, is there any difference at all between the two? As far as I know, they are logically the same and perform identically. So, to rephrase, what do views offer that parameterless inline table-valued functions don't? All that came to mind was indexed views, but I've never seen anyone actually use those.

I'm trying to get a clean view of a table showing product transfers between warehouses. There are 2 records for each transfer: a "FROM" record debiting the sending warehouse and a "TO" record crediting the receiving warehouse. The (simplified) data looks like this: Transfer TransNo ItemNumber FromToDesc Location TransEachQty 236393 176 FROM 1 -10 236393 176 TO 3 10 236393 386 FROM 1 -60 236393 386 TO 3 60 236393 659 FROM 1 -14 236393 659 TO 3 14 I want to end up with a simplified one-record-per-transfer view like this: TransNo ItemNumber FromLocation ToLocation TransQty 236393 176 1 3 10 236393 386 1 3 60 236393 659 1 3 14 I'm not sure how to get there from the table I'm starting with. Recommendations, please? SQL Fiddle

I have 2 tables DraftProducts and Products. The only difference between the 2 tables is the "phase" field. I have productCode varchar(20), phase(tinyint), Name, Price, SpecialCells.... etc fields in my DraftProducts table. the last phase is actually 1 record in the Product table for me. Until now, when 1 user said that the draft work was finished, I was automatically recording the last phase in the Product table. But when the last draft changes, it needs to be updated again. It needs to be deleted when deleted etc. a lot of additional work is going on. In my database, some tables work from the DraftProducts table and some tables from the Products table. But can I do this as "Products view" independent of the user? How can I create a "ProductsView" with max(phase) 1 record? How should I do group by or distinct? As a result of this table, the Products table or view I want should be like this. p1=8 price, category, description etc. p2=15 price, category, description etc. p3=22 price, category, description etc. ``` CREATE TABLE [dbo].[DraftProducts]( [productCode] [varchar](20) NULL, [phase] [varchar](50) NULL, [name] [varchar](50) NULL, [category] [varchar](20) NULL, [description] [varchar](20) NULL, [price] float NULL ) ``` ``` CREATE UNIQUE CLUSTERED INDEX [DraftProductsIndex1] ON [dbo].[DraftProducts] ( [productCode] ASC, [phase] ASC ) ``` ``` insert into DraftProducts (productCode,phase,name) values ('p1',1,'aaaaaaaaaaaaaaa'), ('p2',2,'aaaaaaaaaaaaaaa'), ('p3',1,'aaaaaaaaaaaaaaa'), ('p2',15,'bbbbbbbbbbbbbbb'), ('p3',22,'bbbbbbbbbbbbbbb'), ('p1',8,'bbbbbbbbbbbbbbbbbbbbbbbbb'), ('p2',7,'ccccccccccccccc') ``` ``` 7 rows affected ``` ``` select * from DraftProducts /* finish phase p1=8 p2=15 p3=22 */ ``` productCode phase name category description price p1 1 aaaaaaaaaaaaaaa null null null p1 8 bbbbbbbbbbbbbbbbbbbbbbbbb null null null p2 15 bbbbbbbbbbbbbbb null null null p2 2 aaaaaaaaaaaaaaa null null null p2 7 ccc

I had to deal with a third-party database, I am trying to get all columns used in a view but I got them encrypted e.g: instead of getting `int` for `NUMERO_SALARIE` I got `TNumSal` why? ``` DECLARE @TableViewName NVARCHAR(128) SET @TableViewName=N'DP_SALARIE' SELECT b.name AS ColumnName, c.name AS DataType, b.max_length AS Length FROM sys.all_objects a INNER JOIN sys.all_columns b ON a.object_id=b.object_id INNER JOIN sys.types c ON b.user_type_id=c.user_type_id WHERE a.Name=@TableViewName AND a.type IN ('U','V') ``` Is there any way to decrypt them?

I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso. As far as I understand, 'entity' is not the proper term since this would correspond to a table but not a view or even a query. 'Result' would be counterintuitive on modifiable data structures. Which would be the proper term in the professional environment?

I have a table atbl_UserSetting with a column with name ID which is a primary key with identity and a seed/increment. This table is accessible through a view for regular users. This is the view: ``` CREATE OR ALTER VIEW [dbo].[atbv_UserSetting] AS SELECT ID, User_ID, [Key], Value FROM dbo.atbl_UserSetting AS US WHERE EXISTS (SELECT TOP (1) 1 AS Expr1 FROM dbo.atbl_User AS U WHERE (ID = US.User_ID) AND (Login = SUSER_NAME())) ``` Now when I run this query, it will return true (1): ``` SELECT COLUMNPROPERTY(OBJECT_ID('atbv_UserSetting'),'ID','IsIdentity') ``` Same for INFORMATION_SCHEMA.COLUMNS. However, if I change the view to this instead (joining in the User-table): ``` CREATE OR ALTER VIEW [dbo].[atbv_UserSetting] AS SELECT US.ID, US.User_ID, US.[Key], US.Value FROM dbo.atbl_UserSetting AS US INNER JOIN dbo.atbl_User AS U ON U.ID = US.User_ID WHERE U.[Login] = SUSER_NAME() ``` The same property of the column returns false (0). This in turn leads to SqlCommandBuilder (.NET System.Data.SqlClient) generating insert-command with the ID column and when calling Update on SqlAdapter it will fail since the IDENTITY_INSERT is set to OFF. When the view use the WHERE clause instead of joining in another table, the columnproperty returns true(1) for IsIdentity and SqlCommandBuilder correctly does not include the ID column in the insert command. So my question is, why is not SQL Server able to recognize correct value of the column property "IsIdentity" for a view when other tables are joined in?

I'm talking with a coworker about a way to approach a problem. We have a table that keeps track of a filling process, that has unique index columns (or should at least) on columns `Lot` and `TestGrade`. Recently, we added logic so that when a `TestGrade` of anything higher than 1 is created, all the lower levels. Some logic is done so the lower level rows are not necessarily identical to the top level. For example, I INSERT something with `Lot='ABCD'` and `TestGrade=5` then in my `Fills` table I see ``` id Lot TestGrade OtherColumns ====================================== 10 'ABCD' 1 blah3 9 'ABCD' 2 bar 8 'ABCD' 3 foo 7 'ABCD' 4 blah1 6 'ABCD' 5 blah ``` Now the issue is in many instances, I only want the top level records of of the Fills table, so in the example, I would only want the record with `id=6`. We have two ideas on how to do this. I want to make a view that looks like ``` CREATE VIEW [dbo].[vwFills] AS SELECT t.* FROM [dbo].[Fills] t JOIN (SELECT [Lot], MAX(TestGrade) as TestGrade FROM dbo.Fills GROUP BY [Lot]) t2 ON t.[Lot] = t2.[Lot] AND t.[TestGrade] = t2.[TestGrade] ``` My coworker wants to do something similar but inside a function that will look like this ``` ALTER FUNCTION [dbo].[fnFills] ( @Fills tyFills READONLY ) RETURNS @returnTable TABLE( //Copy of the table definition of Fills, without id INT NOT NULL, Lot VARCHAR(10) NOT NULL, TestGrade INT NOT NULL, //rest of the columns.. ) AS Begin insert into @returnTable select t2.* from ( select [Lot], max([TestGrade]) as TestGrade from @Fills) t1 left join Fills t2 on t1.[Lot] = t2.[Lot] and t1.[TestGrade] = t2.[TestGrade] Return End ``` Then to call this function, it would look like ``` DECALARE @FillRecords tyFills; INSERT INTO @FillRecords SELECT * FROM db.Fills; SELECT * FROM dbo.fnFills(@FillRecords); ``` Our `dbo.Fills` does have

I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in sorting it. Environment - SQL Server 2019 - StackOverflow2013 database (50GB version), Compat Mode 150 (problem is not specific to this version) The setup: Firstly, I've created a view to return everyone with a high reputation: ``` CREATE VIEW vwHighReputation WITH SCHEMABINDING AS SELECT [Id], [DisplayName], [Reputation] FROM [dbo].[Users] WHERE [Reputation] > 10000 ``` Next, since I'll be searching by display name, I've created a couple of indexes on the view: ``` CREATE UNIQUE CLUSTERED INDEX IX_Users_Id ON [dbo].[vwHighReputation]([Id]) GO CREATE NONCLUSTERED INDEX IX_Users_DisplayName ON [dbo].[vwHighReputation]([DisplayName]) INCLUDE (Reputation) GO ``` If I query via the view, I can see my nonclustered index is being used: ``` SELECT * FROM [dbo].[vwHighReputation] WHERE [DisplayName] LIKE 'J%' ``` Plan: (https://www.brentozar.com/pastetheplan/?id=Sy2EoJaiv) So far so good. I can even use my view as part of a more complex query with an OUTER APPLY, and I still get a seek with only 63 reads against my index (this is obviously a contrived example, but helps illustrate the problem that I'll come to): ``` SELECT [U].[Id], [A].[Reputation], [A].[DisplayName] FROM [dbo].[Users] AS [U] OUTER APPLY ( SELECT * FROM [dbo].[vwHighReputation] AS [v] WHERE [v].[Id] = [U].[Id] ) AS [A] WHERE [A].[DisplayName] LIKE 'J%'; ``` Plan: https://www.brentozar.com/pastetheplan/?id=HJaw3y6ov However, if I add a TOP 1 to my OUTER APPLY: ``` SELECT [U].[Id], [A].[Reputation], [A].[DisplayName] FROM [dbo].[Users] AS [U] OUTER APPLY ( SELECT TOP 1 * F

I have a few tables that I would like to merge in a similar way that Python Pandas Concatenate does. I do not want to replicate the information for it is rather large. I'd like to have a `VIEW` like behavior. Let me illustrate by a fake example that as far as I know would not work but just to properly explain what I would like to achieve. Let us say I have these two tables: ``` CREATE TABLE table_1 ( [a] [bigint] NOT NULL, [b] [bigint] NOT NULL, [c] [varchar] (32) NULL, [d] [bigint] NULL, ) GO CREATE TABLE table_2 ( [a] [bigint] NOT NULL, [b] [bigint] NOT NULL, [c] [varchar] (32) NULL, [e] [varchar](256) NULL, ) GO ``` Where `a, b, c` are equivalent but hold different data. `d` exists in `table_1` but not in `table_2` and `e` exists in `table_2` but not in `table_1`. Tables do not hold any common data. That is, a `JOIN` on any field would bring zero results. I would like to be able to do the following (Or something equivalent rather. I know what I am showing is not doable): ``` CREATE VIEW MyUnion FROM( SELECT * FROM table_1 UNION SELECT * FROM table_2) AS alldata; ``` In such a way that if I run: ``` SELECT * FROM MyUnion; ``` And would get something like (`1` and `One` are just filler representing a generic number or string): ``` a b c d e 1 1 One 1 NULL 1 1 One NULL One ``` Is there any way to do anything like this without duplicating the data? (i.e. creating another table holding everything) Thank you!

I have an updatable view pointing to an underlying table that has a partial index. It looks something like this `CREATE TABLE if not exists foo ( a INT, b INT, x INT, y INT, z BOOLEAN, CONSTRAINT x_or_y CHECK ( (z and x is not null and y is null) or (not z and x is null and y is not null) ) ); CREATE UNIQUE INDEX ux ON foo (x, a) WHERE z=TRUE; CREATE UNIQUE INDEX uy ON foo (y, a) WHERE z=FALSE; CREATE OR REPLACE VIEW foo_view AS SELECT * FROM foo; ` That is, for each row, `y` must be null if `z` is true; `x` must be null if `z` is false; and, only one of `x` and `y` may be not null. `(x, a)` and `(y, a)` must be unique. (Sorry if this is overly complicated. I'm translating from my real table that has a lot of other cruft.) My problem comes when I want to update with `ON CONFLICT`. I believe I ought to be able to do this. `INSERT INTO foo_view(x, y, a, b, z) VALUES (5, null, 1, 2, true), (null, 5, 1, 2, false); select * from foo_view; INSERT INTO foo_view(x, y, a, b, z) VALUES (5, null, 1, 2, true) ON CONFLICT (x, a) where z=true DO UPDATE set b = EXCLUDED.b; ` But, I get the exception: ``` ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification ``` I can insert into `foo` instead of `foo_view` with the same `ON CONFLICT` without error. Here is a fiddle: https://www.db-fiddle.com/f/cX2HXg91Q7yKoPeMBYzVLg/0