Neither your example data nor requirements mentioned continent.  If you don't come right, then come back with sample data that represents both continent and country, and show us what you...

The following SQL should get the results as requested in your post.

SELECT      ID         ...

If the users can add/remove columns to a table at will, I would actually consider this a good candidate for an EAV table.

If i wish to use bigint for storing huge number of records in a table, i know bigint can go upto 9223372036854775807.

What if for next record if i...

If i wish to use bigint for storing huge number of records in a table, i know bigint can go upto 9223372036854775807.

What if for next record if i reach...

In order for anybody to assist with performance issues, you will need to upload the execution plan of the 2 different queries.  The actual execution plan is preferred.

Just guessing here,...

If you use the SSMS table designer to change the field from INT to BIGINT, then generate the change script, you will see that under the hood, the table is...

• This reply was modified 5 years, 1 month ago by DesNorton.

Luckily, in this case, the PII views that sit on top of the underlying tables, all return data from only that table, and use exactly the same column names.

SO, I...

I don't see any way to do this.  A view could have columns from several different referenced entities and from no referenced entity at all (such as CREATE VIEW...

What about INFORMATION_SCHEMA.COLUMNS or sys.columns ?   Do you not have access to either of those?  It just seems to me that those would make it far easier than trying...

I have adapted the code from the following 2 links in order to create a sample of how to dynamically unpivot data

• http://stackoverflow.com/questions/18775409/unpivot-with-dynamic-columns-plus-column-names
• https://www.sqlservercentral.com/articles/an-alternative-better-method-to-unpivot-sql-spackle

if object_id('tempdb..#temp') is not...

I am not aware of any method similar to SELECT * FROM xml.

I use the text() for performance.  If you look at the execution plans for the the same query...

Use something like this, and change your data types as needed

SELECT 
    id          = XT.XC.value('(id/text())[1]',...

This should get you what you are looking for

SELECT 
    XT.XC.value('(id/text())[1]', 'int')
FROM
    @MyXMLString.nodes('/items/item') AS XT(XC)

In order to compare NULL values, you need to convert them to a known value.

Below is how I would do it, with 3 different data types

DECLARE @Var1...