23

If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this: select StationID, ParameterID, m.DateTime LastDate from StationParameter sp cross apply ( select top 1 DateTime from MyTable where StationID = sp.StationID and ParameterID = sp.ParameterID order by DateTime desc ) m To enable ...


15

row_number is not deterministic if there can be ties (i.e. rows with the same PartitionField and DateField values). Any of the tied values might end up with a PartitionRowId of 1 which would presumably change the final result. You could use rank instead of row_number but that would cause you to consider all the tied rows which may not be what you want. ...


12

Nonclustered indexes contain a row locator back to the base table. This is a clustered index key for rowstore tables with a clustered index or a physical RID (file/page/slot) for heaps. So the parts in your question assuming that it uses the primary key (in the event that these are different) are moot. The row locator is added to the key for non unique ...


11

I can't think of a fully transparent way to achieve what you want with views without disabling row goals in general, which probably won't suit your purposes. As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query. Perhaps ...


7

Also "reducing fragmentation" is not per se a performance goal. On many (most?) modern storage platforms there is little difference between sequential and random IO, which is a major historical reason for defragmenting. I've worked on systems where the difference in throughput between sequential and random IO was 10x or more. As SQL Server attempts to ...


6

First question, are you performing log backups? If your database is in FULL recovery, you definitely should be performing regular log backups (at least every 15 minutes, depending on your RTO and RPO) and this helps achieve two important goals: Provide point-in-time recoverability of your database Maintain a reasonably sized log file Truncation of the log ...


6

I am failry clear on what ISOLATION LEVEL READ UNCOMMITTED does for reads, but I am not sure about deletes. It does nothing. You can't modify the database without proper locking. That would produce not just the failures and nonsense results that dirty reads produces, but would corrupt the database.


4

Your 'counter' properties are in an object within an array, so your JSON path in JSON_MODIFY isn't correct. You need to reference the index of the item in the array to correctly update the value: UPDATE test01 set json_data = JSON_MODIFY(json_data,'$[0].counter2',2000) where test01_id =10 If you had additional objects in the array with different counter 1/...


3

if it's possible to create new stored procedures and tables on the reporting database which will not be dropped as part of the replication sync? Yes, you can create new stored procedures and tables on the reporting database. When creating a publication, you choose the tables and other database objects that you want to publish. Objects that are included ...


3

You can go in three ways: Use replication. This is a large area of ​​database engineering. I recommend you get started with types of replication. Use log shipping. This is usually used to create an emergency database (hot backup). Pay attention: The secondary server will read-only. The easiest option. Run backup on the primary server and restore this file ...


2

I ran into that problem a few minutes and found a way to remove the fulltext stuff from a bacpac. I found this powershell script (but fixed my backup by hand, because it was faster than trying to understand and use the script) https://gist.github.com/JohnLBevan/6a8876f71a25aa600254d7127cf1a819 Rename your backup to zip extract model.xml and Origin.xml ...


2

It looks like the other suggestions so far all lead back to sys.dm_db_index_physical_stats This is where you will have to look to get the index stats, but you could look to use the LIMITED option and restrict it to specific indexes at a time. With the size of your table, I would want to know how quickly fragmentation occurs to help me confirm that the ...


2

You should not have your backup files on the SQL server as you would lose those backup if ever there is an issue with the SQL server. Best practice would be to store your backup outside of the SQL Server (on a shared folder for example) that can be accessed by both node of the AG. As the primary node can switch from one or the other node, you should code ...


2

As you're going to have new id values, it's not going to to be easy to do a straight copy. I might suggest copying the data to a set of staging tables with no foreign keys defined. Then, in the staging tables, increase the existing ID values for all rows in a given table by a fixed amount that will guarantee they are higher than the max value in the ...


2

You can generate a BACPAC using SqlPackage.exe or the Export Data-Tier Application wizard in SSMS. There are some limitations on what objects can be exported, however, in general, you will be able to export most of the objects in your database. This is similar to MattyZDBA's answer except in a BACPAC the schema and data are wrapped in a single file and you ...


2

BCP by itself may not be sufficient as it won't address non-table objects like stored procedures. To migrate the schema, one option would be to use a Visual Studio database project importing it from the 2016 instance and deploying it to your 2012 instance. Afterwards, two options to import your data rows are BCP and the Import/Export wizard in SSMS.


2

A restricted admin should only add users to roles through ALTER ROLE. The ability to grant new permissions to a role or user should be reserved for a real admin.


1

The problem is that procedure only works if read access is enabled for the secondaries and, as per the documentation, Basic Availability Groups do not allow readable secondaries. You need to manually perform the checks that this procedure normally handles as per the doco: This is expected behavior. You must verify the presence of the subscriber server ...


1

What happens when the old primary is available and back online? Synchronous-commit mode and Automatic Failover: When the former primary replica comes back online, it takes on the secondary role, and the former primary database becomes the secondary database. The new secondary replica quickly resynchronizes the new secondary databases with the corresponding ...


1

This is not possible since database is added to AOAG You cannot take the db offline unless you remove it from AG You can see and verify this yourself simply by adding some "TestDB" to your AG and try to take if offline Do I have to wait for the sync to finish before manually failing back over to Database A from Database B? Yes you have to wait for sync ...


1

There are the usual precautions such as ensuring up to the minute backups before you start. Another option you could consider though is to create Mount Points for the new drive so that you don't need to worry about changing drive letters afterwards - the fewer moving parts the better.


1

in other words does updating Table2 modify the dataset that uses Table2 for the original cursor definition. You are inserting data into table2 and these data changes could be visible unless you specify the STATIC keyword. STATIC Specifies that the cursor always displays the result set as it was when the cursor was first opened, and makes a temporary ...


1

If (and that's a big IF) you're running SQL Server 2019 (CTP 2.4) then you can get the last known ACTUAL execution plan for a previously cached query plan: SELECT qps.query_plan FROM sys.dm_exec_procedure_stats AS ps CROSS APPLY sys.dm_exec_query_plan_stats(deps.plan_handle) AS qps WHERE ps.object_id = OBJECT_ID('dbo.spMyProcedure');


1

Give this a shot. SELECT ST.NAME, su.name, CASE WHEN SYP.[ACTION] = 193 THEN 'SELECT' WHEN SYP.[ACTION] = 195 THEN 'INSERT' WHEN SYP.[ACTION] = 196 THEN 'DELETE' WHEN SYP.[ACTION] = 197 THEN 'UPDATE' ELSE CAST(SYP.[ACTION] AS CHAR(3)) END AS Permission FROM SYS.SYSPROTECTS SYP INNER JOIN SYS.SYSUSERS SU ON SU.uid = SYP.UID INNER JOIN SYS.SYSOBJECTS SO ON ...


1

The GRANT command in T-SQL also has the optional WITH GRANT OPTION If you give your restricted users a specific permission and want to allow them to allow other users to have that permission then use the WITH GRANT OPTION part when you assign that permission. Personally I am not a fan of the fixed database roles as I find them too encompasing. I prefer to ...


1

Ref: ALTER ROLE (Transact-SQL) The behavior you are seeing is by design. @David Browne answered your question. I am going to elaborate on his answer. But I can't find what role is able to grant and revoke permissions without being a db_owner. If it is a custom role a user needs the following permissions. ALTER permission on the role ALTER ANY ROLE ...


1

Since you mentioned default roles of SQL Server, I suggest you check the following graphs from Microsoft docs: Source: Server-Level Roles Source: Database-Level Roles If you do not see a default role with the specific privileges you need, create your own role and grant it the right privileges.


1

By default, SQL uses port 1433 and 1434 (if you are using named instance or the DAC connection). You can validate which port is used using SQL Server Configuration Manager


1

Well...the AD part is unnecessary, that only gives authentication/login privileges to users (basically all AD does in SQL Server is tell SQL Server "I have verified that this person is who she/he says it is and you can allow this person to connect because I say so" - this is a very basic explanation, of course). If you want to set up privileges about what ...


1

Yes you can do this. Best way to do is use the configuration.ini file and customise it as per your use. For system datafiles you will need to edit this portion of the file : INSTALLSQLDATADIR="S:" ABove example is where my system datafiles go into.. (S: )


Only top voted, non community-wiki answers of a minimum length are eligible