Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

What is the relation between renaming the computer and sp_dropserver/sp_addserver?

Alberto last edited by

I have default SQL server instance (not a named instance).

have changed the SQL server computer name, and not run the following commands: sp_dropserver 'oldname'; sp_addserver 'newname', 'local'; restart - then what is the impact of not running these?

Either ways (whether the above commands are run or not run), all applications that use the old computer name fail and need to use the new computer name.
Reply Quote 0
1 Reply Last reply
R
rosemadder last edited by
Host name re-naming will not automatically take effect sys.servers system table where the instance name is recorded. The https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15 needs to be performed to make the hostname changes to reflect in sys.servers metadata table.

Update system metadata to reflect computer name changes for remote connections and applications that use @@SERVERNAME, or that query the server name from sys.servers.

To make the application changes easy, we create CName(DNS record) which is mapped to the hostname/AGName. And applications should use the CName in their connection strings instead of direct host name or AG name. So whenever there is a change to host or AG name, all you need to do is update the DNS mapping, so the applications will automatically redirected to use the new changes.

Ex: your cahanges will look like this before and after host name is updated.

Before:
```
applicationName                 Record Type     Destination
application01.domain.com        CNAME           hostname01
```
After: (hostname01 is updated to hostname02)
```
applicationName                 Record Type     Destination
application01.domain.com        CNAME           hostname02
```
Your applications must use application01.domain.com in their connection string.

Read more about CName usage https://support.dnsimple.com/articles/cname-record/ .
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

R

Granting a user a permission to restore a database
SQL, Database Testing • sql server permissions restore sql server 2019 role • • rosemadder

2

0
Votes

2
Posts

0
Views

A

Yes, this is actually fairly easy to accomplish using Module Signing. With module signing, you create a module (in this case, a stored procedure) and grant the required permission to the module, not to user or login. You only grant the user permission to execute the module. And, by doing this, not only can you be granular enough to effectively only grant the ability to restore, by hard-coding the RESTORE statement, you are essentially granting permission to only restore this particular database. Detailed instructions, explanation, and walk-through demo can be found here: Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level But to simplify, here are the basic steps: (we will assume that the stored procedure will be created in the [master] DB, which means we don't need to copy the certificate to another DB. the stored procedure could just as easily be created in a utility database, in which case you just need to copy the certificate to [master] as shown in the instructions in the linked blog post) Create a stored procedure. This can be as simple as just the RESTORE statement for a specific DB, or it can accept an input paramater for @DBName sysname and construct Dynamic SQL using that parameter value, this allowing for restore of any particular DB, or maybe a @DbToRestore TINYINT parameter that is used in a CASE statement allowing the user to select from a limited set of pre-defined database names to restore. Create a certificate specifying a password (do not rely on the Database Master Key / DMK). Do not give the end-user / customers this password (including a script containing the password). Sign the stored procedure (i.e. the "module") using that certificate and its password. Create a login (not user) from the certificate. Add the login to the dbcreator fixed server role. If the end-user / customer does not already have a user in [master] for their login: create a user in [master] for the customer's login. Grant the customer's user EXECUTE permission on the stored procedure. For added security: it might be a good idea to prevent them from signing another module, or re-signing this one, if they somehow manage to get the password. This is not always necessary, but is probably a good idea here since you distribute the system to the customer. Backup the certificate to a file Remove the private key from the certificate Without the private key, the certificate can only validate that a module was signed with it, but it can no longer sign anything. Since signing a module applies the permissions of the associated certificate-based login (i.e. the login that was added to dbcreator), this prevents the customer from applying those permissions to their own modules, if they ever managed to create any. ALSO, if the customer manages to alter this stored procedure to do something that you are trying to prevent, the signature is dropped, meaning that the module no longer has the elevated permissions (even if they put the stored procedure back to the original code). Please also take a look at the following answer of mine, also here on DBA.StackExchange, to a similar question: Execute Permissions for a Store Procedure that creates databases
G

GRANT permissions to all tables, views, procedures in SQL Server 2000
SQL, Database Testing • sql server permissions sql server 2000 • • gionna

2

0
Votes

2
Posts

0
Views

Ugh, 2000. Assuming all objects are owned by dbo, you can generate a script, e.g.: SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type IN (N'U', N'V'); SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type = N'P'; Now you can copy and paste the output in case you want to exclude any entries or validate. If you just want to blindly execute, you can instead do this, but it relies on the content of any individual set of commands being < 4K: DECLARE @sql NVARCHAR(4000) = N''; SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type = N'U'; EXEC sp_executesql @sql; SET @sql = N''; SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type = N'V'; EXEC sp_executesql @sql; SET @sql = N''; SELECT @sql = @sql + N'GRANT EXECUTE ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type = N'P'; EXEC sp_executesql @sql;
SqlConnection failure in C#
Software Programming • c sharp sql server • • Bogopo

2

0
Votes

2
Posts

0
Views

R

This isn't a mistake here. It is a warning (warningWarning 1 The field 'WindowsFormsApplication1.Form3.dataRead' is used C:...WindowsFormsApplication1\Form3.cs 23 WindowsFormsApplication1Just saying that you are not using the informed component anywhere in your code. About the connection, the connection string It's wrong. If you want to aim connection string for a SDF file, the correct one is:SqlCeConnection ABC = new SqlConnection( "Data Source=C:\Users\Somline\Documents\Visual Studio 2008\Projects\PROGRAMA\WindowsFormsApplication1\Database1.sdf;Persist Security Info=False"); SqlCeCommand command = new SqlCommand(); SqlCeDataReader dataRead; Note that the classes are SQL Server Compact, not SQL Normal server (SqlCeConnection, SqlCeCommand, SqlCeDataReader). You have Ce in the middle of the name.
XML vs. Relational databases?
SQL, Database Testing • xml relational theory • • carriann

2

0
Votes

2
Posts

0
Views

I use XML when I need more flexible structure than a database table can offer. When you have data that you do not know exactly the structure beforehand, it could be profitable to store it as XML. You can store such XML in a relational database field, as many e-learning systems do. In fact e-learning course structure is a good example because individual courses could be quite different from each other. So XML is somewhere between a document and a relational-table. Imagine a database table where each record would have a very different structure, that would be good use for XML. Also XML is very good for data interchange, meanwhile files like .DBF, .XLS etc. are not so good.
S

Error when formatting CEP with Format() function
Software Programming • sql server • • Saumya

2

0
Votes

2
Posts

0
Views

A

The function FORMAT only accepts numerical types and date types. You could do this:DECLARE @cep int = 23574510; SELECT FORMAT(@cep,'#####-###') AS [CEP]
How to convert SQL Server database file to MongoDB?
SQL, Database Testing • sql server database design mongodb • • Demir

2

0
Votes

2
Posts

0
Views

A

You could also make use of the OOP paradigm; you could then write a simple program, that will basically do the following. create Classes for each of your SQL Server relations create your MongoDB structure acording to those models load the data into objects insert it into Mongo This solution could be feasable or not, depending on your DB's size/structure. That should do it. A straight-forward one-click solution I don't think there's available.
What is SSIS in relation to SQL Server?
SQL, Database Testing • sql server t sql ssis ssdt • • irl

2

0
Votes

2
Posts

0
Views

J

SSIS (SQL Server Information Services) is a first-party (Microsoft) component/platform for Microsoft SQL Server. It isn't an add-on product, you will be given the option to include components when installing SQL server. Don't think of it as a wrapper for T-SQL, think of it as a place to run T-SQL (and do all kinds of other very useful things). (SSIS is the successor/replacement for DTS (Data Transformation Services) in SQL 2000 and prior, if you're familiar with that.) To use SSIS you create a "package", which is a standalone file with a dtsx extension. You create the package in SSDT (SQL Server Data Tools), a customized version of MS Visual Studio (this used to be called BIDS). Your SSIS package can be executed on any SQL server, by calling it directly from a SQL Agent job. You can also run it on a separate server that has SSIS server components installed, or from a development workstation using either SSDT or the "SSIS Package Execution Utility". Here's an example of typical use: The SQL Agent allows you to create scheduled jobs, in which you might run a bit of T-SQL code to perform some database maintenance or something. Paste the T-SQL code into the step detail window, set the job schedule, and you are good to go. But what if you need to do something more complex than you'd normally attempt in T-SQL? Or something that T-SQL can't do at all? SSIS allows you to run T-SQL, but you can also do a ton of other stuff: Easily manage connections to multiple SQL servers (or other ODBC sources), for imports/exports or other operations Create high-performing data flows and transformations (fastest possible bulk imports, column lookups, robust error handling, including piping failed rows into a different dataset or destination table) Supports a branching flow, which can launch truly parallel tasks, something that normally isn't possible using T-SQL Supports other script/code languages for more sophisticated logic Easy mechanisms for looping through objects Detailed error logging and/or package progress logging Lots of other cool stuff. So you create your more sophisticated "nightly data processing" SSIS package that imports and validates data from 2 other SQL servers, 1 Oracle instance, and 5 text files, then processes the data per your business rules, all using parallel processing to take advantage of your multiple CPU cores. Then call that from your job step instead of your T-SQL code. Note: installing the Server Component of SSIS isn't strictly necessary, that is a tool to store and manage SSIS packages, or (if desired) run SSIS packages on a dedicated host. EDIT: SSDT is the tool used to create and edit SSIS packages (thanks, @JonathanFite!). It is basically a customized version of MS Visual Studio. It used to be called BIDS (Business Intelligence Design Studio).
Index on Persisted Computed column needs key lookup to get columns in the computed expression
SQL, Database Testing • sql server sql server 2008 exec languageution plan c languageomputed c langu bookmark lookup • • Alberto

2

0
Votes

2
Posts

0
Views

R

Why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why? Columns A, B, and C are referenced in the query plan - they are used by the seek on T2. Also, why can the query use the index on t2, but not on t1? The optimizer decided that scanning the clustered index was cheaper than scanning the filtered nonclustered index and then performing a lookup to retrieve the values for columns A, B, and C. Explanation The real question is why the optimizer felt the need to retrieve A, B, and C for the index seek at all. We would expect it to read the Comp column using a nonclustered index scan, and then perform a seek on the same index (alias T2) to locate the Top 1 record. The query optimizer expands computed column references before optimization begins, to give it a chance to assess the costs of various query plans. For some queries, expanding the definition of a computed column allows the optimizer to find more efficient plans. When the optimizer encounters a correlated subquery, it attempts to 'unroll it' to a form it finds easier to reason about. If it cannot find a more effective simplification, it resorts to rewriting the correlated subquery as an apply (a correlated join): It just so happens that this apply unrolling puts the logical query tree into a form that does not work well with project normalization (a later stage that looks to match general expressions to computed columns, among other things). In your case, the way the query is written interacts with internal details of the optimizer such that the expanded expression definition is not matched back to the computed column, and you end up with a seek that references columns A, B, and C instead of the computed column, Comp. This is the root cause. Workaround One idea to workaround this side-effect is to write the query as an apply manually: SELECT T1.ID, T1.Comp, T1.D, CA.D2 FROM dbo.T AS T1 CROSS APPLY ( SELECT TOP (1) D2 = T2.D FROM dbo.T AS T2 WHERE T2.Comp = T1.Comp AND T2.D > T1.D ORDER BY T2.D ASC ) AS CA WHERE T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS ORDER BY T1.Comp; Unfortunately, this query will not use the filtered index as we would hope either. The inequality test on column D inside the apply rejects NULLs, so the apparently redundant predicate WHERE T1.D IS NOT NULL is optimized away. Without that explicit predicate, the filtered index matching logic decides it cannot use the filtered index. There are a number of ways to work around this second side-effect, but the easiest is probably to change the cross apply to an outer apply (mirroring the logic of the rewrite the optimizer performed earlier on the correlated subquery): SELECT T1.ID, T1.Comp, T1.D, CA.D2 FROM dbo.T AS T1 OUTER APPLY ( SELECT TOP (1) D2 = T2.D FROM dbo.T AS T2 WHERE T2.Comp = T1.Comp AND T2.D > T1.D ORDER BY T2.D ASC ) AS CA WHERE T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS ORDER BY T1.Comp; Now the optimizer does not need to use the apply rewrite itself (so the computed column matching works as expected) and the predicate is not optimized away either, so the filtered index can be used for both data access operations, and the seek uses the Comp column on both sides: This would generally be preferred over adding A, B, and C as INCLUDEd columns in the filtered index, because it addresses the root cause of the problem, and does not require widening the index unnecessarily. Persisted computed columns As a side note, it is not necessary to mark the computed column as PERSISTED, if you don't mind repeating its definition in a CHECK constraint: CREATE TABLE dbo.T ( ID integer IDENTITY(1, 1) NOT NULL, A varchar(20) NOT NULL, B varchar(20) NOT NULL, C varchar(20) NOT NULL, D date NULL, E varchar(20) NULL, Comp AS A + '-' + B + '-' + C, CONSTRAINT CK_T_Comp_NotNull CHECK (A + '-' + B + '-' + C IS NOT NULL), CONSTRAINT PK_T_ID PRIMARY KEY (ID) ); CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL; The computed column is only required to be PERSISTED in this case if you want to use a NOT NULL constraint or to reference the Comp column directly (instead of repeating its definition) in a CHECK constraint.
R

SQL script to count rows for all views in a database with SELECT-only access
SQL, Database Testing • sql server permissions view count • • rielyn

2

0
Votes

2
Posts

0
Views

A few improvements. QUOTENAME() to thwart bad namers add schema name, since schema is important use sys.views instead of sys.objects (and remove type from output) limit sys.partitions to index_id 0 or 1, to prevent double-counting in the case of an indexed view with additional non-clustered indexes (not sure what you think AVG will accomplish - what if there is an indexed view with a non-clustered filtered index? use dynamic SQL to avoid manual copy/ manual paste/ manual massage/ manual run DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'UNION ALL SELECT ' + 'N''' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) + N''', FromMetadata = N' + CONVERT(varchar(11),COALESCE(SUM(p.rows),0)) + N', TheHarderWay = COUNT(*) FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) +N' ' FROM sys.views AS v INNER JOIN sys.schemas AS s ON v.schema_id = s.schema_id LEFT OUTER JOIN sys.partitions AS p ON v.object_id = p.object_id AND p.index_id IN (0,1) GROUP BY s.name, v.name ORDER BY s.name, v.name; SET @sql = STUFF(@sql, 1, CHARINDEX(N'SELECT', @sql)-1, N''); PRINT @sql; --EXEC sys.sp_executesql @sql; Note that FromMetadata will be 0 unless the view is indexed.
E

rename database
SQL, Database Testing • sql server • • elysha

2

0
Votes

2
Posts

0
Views

S

you could change (rename) file name for MDF and LDF during database restoring What would happen if I don't rename the mdf and ldf files. nothing, everything will keep working as before.
K

Can a 2NF relation be M:M?
SQL, Database Testing • database design • • kalena

2

0
Votes

2
Posts

0
Views

The normal forms don't really have anything to do with many-to-many relationships. If you happen to lose some as a byproduct of the normalization process, that's fine, but you won't generally do so. If we consider that we have two tables: Salesman and Product which each have ID fields as their primary key and we have a third table called Specializes which shows which Salesmen specialize in selling which products. This Specializes table would represent the many-to-many relationship since each salesman can specialize in multiple products and each product can be specialized in by more than one salesmen. It would probably look something like this (excuse the awkward formatting, we can't do real tables on StackExchange): | SalesmanID | ProductID | |------------|-----------| | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | | 2 | 3 | |------------|-----------| Obviously, the lack of nulls and repeated rows means that this table is in 1NF. In this table, the only candidate key is {SalesmanID, ProductID} and as such, there are no non-prime attributes. It also contains no non-trivial functional dependencies. Thus, it is necessarily in 2NF, 3NF, and BCNF. I'm also going to assert without proof that it's in 4NF, 5NF, 6NF, and DKNF (to avoid having to explain all of the details thereof). So really, no normal form removes many-to-many relationships, nor are they meant to. The purpose of normal forms is not to remove many-to-many relationships (and I am not actually clear on why you would want to) but rather to remove potential insert anomalies, update anomalies, and deletion anomalies. The primary role of the normal forms is to ensure that each piece of information is represented in a database table precisely once. Having the same information embedded in multiple places leads to problems. But that has nothing to do with many-to-many relationships. I think that you mean to be asking a slightly different question about something like situations where a many-to-many relationship is embedded in a table which also tries to contain other information, such as if the table above contained the product name in addition to the product number (where name is functionally determined by number). A table like that would either violate 2NF (if the name did not also functionally determine the number) or Boyce Codd Normal Form (if the name did functionally determine the number). You could also perhaps be thinking of a different situation: when we have two unrelated 1:M relationships in the same table, such as if we were to add a third column to identify which language or languages each salesman speaks. | SalesmanID | ProductID | Language | |------------|-----------|----------| | 1 | 1 | English | | 1 | 2 | English | | 2 | 1 | Spanish | | 2 | 2 | Spanish | | 2 | 3 | Spanish | | 2 | 1 | French | | 2 | 2 | French | | 2 | 3 | French | |------------|-----------|----------| As you can see, that table is quite problematic, since we need 6 entries to express that Salesman 2 specializes in 3 products and speaks 2 languages. This is a fourth normal form violation. Edit: Upon clarification, it's clear that what he's asking about is a table like the Specializes table, but with extra information about the salesmen and the products, essentially, a table which contains two entity sets and their many-to-many relationship in a single table. So to answer that question directly, yes, you can have lousy tables like that which are in 3NF. The normal form which guarantees that that won't happen is Boyce-Codd Normal Form (BCNF). Here's an example of a lousy table like that which is vulnerable to all kinds of anomalies (insert, update, and delete), but is in 2NF and 3NF. | SalesmanName | SalesmanID | ProductID | ProductName | |--------------|------------|-----------|-------------| | Alex | 1 | 1 | Thingy | | Alex | 1 | 2 | Whatsit | | Barb | 2 | 1 | Thingy | | Barb | 2 | 2 | Whatsit | | Barb | 2 | 3 | Whoosit | |--------------|------------|-----------|-------------| So, looking at this table, it's obviously in 1NF. Further, we can identify the non-trivial functional dependencies very straightforwardly. SalesmanName -> SalesmanID. SalesmanID -> SalesmanName. ProductID -> ProductName. ProductName -> ProductID. Next we need to identify the candidate keys. There are four: {SalesmanName,ProductID}, {SalesmanName,ProductName}, {SalesmanID, ProductID}, and {SalesmanID, ProductName}. As such, we have no non-prime attributes. Thus, we are necessarily in 2NF (no functional dependencies between non-prime attributes) and 3NF (no non-trivial functional dependencies where the left-hand-side is not a super key and the right hand side contains a non-prime attribute). However, we are not in BCNF because there do exist non-trivial functional dependencies whose left-hand-side is not a superkey. Any similar situation will also always not be in Boyce-Codd Normal form because there will be some non-trivial functional dependency whose left-hand-side is not a superkey. Any table like this will essentially have two entity sets each of whom have some attributes. Basically, it will have a left entity set and a right entity set. The left entity set will have some attributes which uniquely identify each left entity and the right entity set will have some attributes which uniquely identify each right entity. Those will be involved in functional dependencies. However, they will each not be candidate keys because you'll have to combine them to get a candidate key for the whole table. As such, they won't be superkeys and there will be a Boyce-Codd Normal Form violation. So BCNF will stop it cold. Anything less than that will only catch some cases. Really, if you only remember one normal form, it should be BCNF.
R

Finding aggregated values based on dates in another table
SQL, Database Testing • sql server t sql • • rielyn

2

0
Votes

2
Posts

0
Views

Something like this? SELECT emp.Employee_Id, emp.Absence_date, SUM(FTE) AS FTE FROM #EMPLOYEE_ABSENCES AS emp JOIN #employee_Positions AS pos ON absence_date BETWEEN position_start_date AND position_end_date AND pos.Employee_Id = emp.Employee_Id GROUP BY emp.Employee_Id, emp.Absence_date ORDER BY Absence_date ASC
UNF to 3NF is this relation workable?
SQL, Database Testing • database design normalization • • emmalee

2

0
Votes

2
Posts

0
Views

You need to learn the difference between repetition and redundancy. Sometimes a field value is repeated coincidentally. This kind of repetition cannot be normalized out. Normalization is about studying the functional dependencies between key and non-key elements. It is not about putting everything that might occur twice into a table with a surrogate key. For example, you say that a phone number may be used by multiple companies and have modeled phone number as an independent table. This would prevent update anomalies if when one company changed its number all of the other companies using that number changed theirs at the same time and in the same way. Does any of that actually make business sense? It doesn't to me. Also, normalizing geography into a hierarchy is a questionable proposition. However, you haven't even normalized into a hierarchy. Is region ID not determined by country ID? If so, your LOCATION table is not 3NF. Similarly, are there postcodes that bridge states? I don't know the Australian system well enough, but I know that in Canada, the US and the UK this doesn't happen. You've designed everything like its a star schema and company is the fact. Facts in star schemas are usually transactions, not static entities. Transactions don't tend to change their properties, because they usually represent something that actually happened and one doesn't generally go back in time to change history. Static entities live for a long time in your data (maybe forever) and change their properties fairly often. Star schema is not a good, efficient model for this type of data. What you should do is run queries on the frequency distributions of each column and on combinations of columns that you think may be keys combined with columns that you know aren't keys. This will help you test your assumptions about which columns are truly able to act as keys. Then you should temper those findings with some common sense around what could potentially happen to your data in terms of changes to column values. Once you know your actual functional dependencies found in your data, then follow the relatively mechanical process of moving your relation to 3NF.
T

Renaming the target table of a trigger
SQL, Database Testing • sql server sql server 2008 r2 trigger • • terrea

2

0
Votes

2
Posts

0
Views

E

As @AaronBertrand pointed out in his comment, objects are attached to other objects by object_id, not by object name. Having said that, there is a way to move data from one table to another without the need to use sp_rename. Since example code is the cleanest way to show how something works, I've created the following test-bed code to illustrate how to use ALTER TABLE ... SWITCH syntax to enable data migration. First, we'll create an "existing" Product and ProductAudit table, with a trigger on the Product table, that inserts rows into the ProductAudit table: USE tempdb; IF OBJECT_ID('dbo.Product') IS NOT NULL DROP TABLE dbo.Product; CREATE TABLE dbo.Product ( ProductKey int NOT NULL CONSTRAINT PK_Product PRIMARY KEY CLUSTERED IDENTITY(1,1) , SomeData varchar(4000) NOT NULL , TriggerUpdated bit NOT NULL CONSTRAINT df_Product_TriggerUpdated DEFAULT (0) ); GO IF OBJECT_ID('dbo.ProductAudit') IS NOT NULL DROP TABLE dbo.ProductAudit; CREATE TABLE dbo.ProductAudit ( ProductAuditKey int NOT NULL CONSTRAINT PK_ProductAudit PRIMARY KEY CLUSTERED IDENTITY(1,1) , ProductKey int NOT NULL , SomeData varchar(4000) NOT NULL ); GO CREATE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.ProductAudit (ProductKey, SomeData) SELECT i.ProductKey , i.SomeData FROM inserted i UPDATE dbo.Product SET TriggerUpdated = 1 FROM dbo.Product p INNER JOIN inserted i ON p.ProductKey = i.ProductKey; END GO Here, we'll insert 100,000 rows into the Product table: ;WITH src AS ( SELECT * FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num) ) INSERT INTO dbo.Product (SomeData) SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000)) FROM src s1 CROSS APPLY src s2 CROSS APPLY src s3 CROSS APPLY src s4 CROSS APPLY src s5; SELECT ProductCount = COUNT(*) FROM dbo.Product pa WHERE pa.TriggerUpdated = 1; SELECT ProductAuditCount = COUNT(*) FROM dbo.ProductAudit pa; Results of the SELECT queries above: ProductCount 100000 ProductAuditCount 100000 This will create a "temporary" table to hold the rows we want to retain: IF OBJECT_ID('dbo.ProductAuditNew') IS NOT NULL DROP TABLE dbo.ProductAuditNew; CREATE TABLE dbo.ProductAuditNew ( ProductAuditKey int NOT NULL CONSTRAINT PK_ProductAuditNew PRIMARY KEY CLUSTERED IDENTITY(1,1) , ProductKey int NOT NULL , SomeData varchar(4000) NOT NULL ); This code "migrates" the data from the existing table into the new table, retaining the triggers present on the original table, without recreating them. The ALTER TABLE ... SWITCH syntax is a meta-data-only operation that is extremely quick, making this a great way to migrate data. The code below sets the transaction isolation level to lock the tables involved to prevent other processes inserting or updating data in any of the involved tables. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --lock all row-ranges affected by --the migration DECLARE @msg varchar(1000); BEGIN TRANSACTION BEGIN TRY --disable the trigger temporarily DISABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product; --copy the rows we want to retain from the old table to the new table INSERT INTO dbo.ProductAuditNew (ProductKey, SomeData) SELECT pa.ProductKey , pa.SomeData FROM dbo.ProductAudit pa WHERE pa.ProductAuditKey % 5 = 0; --retain 20% of the rows --truncate the "old" table TRUNCATE TABLE dbo.ProductAudit; --switch the single partition belonging to the "new" table --into the "old" table ALTER TABLE dbo.ProductAuditNew SWITCH TO dbo.ProductAudit; --remove the "new" table, which is actually empty now DROP TABLE dbo.ProductAuditNew; --re-seed the table's identity object; only necessary if an IDENTITY column is present DBCC CHECKIDENT('dbo.ProductAudit', RESEED); --re-enable the trigger ENABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product; COMMIT TRANSACTION END TRY BEGIN CATCH SET @msg = ERROR_MESSAGE(); PRINT (@msg); ROLLBACK TRANSACTION END CATCH SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --return to default mode SELECT ProductAuditCount = COUNT(*) FROM dbo.ProductAudit pa; Results: ProductAuditCount 20000 Here, we'll insert another 10 rows into the dbo.Product table to prove the trigger still works: ;WITH src AS ( SELECT * FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num) ) INSERT INTO dbo.Product (SomeData) SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000)) FROM src s1; SELECT ProductAuditCount = COUNT(*) FROM dbo.ProductAudit pa; Results: ProductAuditCount 20010
String aggregation using STUFF
SQL, Database Testing • sql server sql server 2016 string aggregation • • Mystic

2

0
Votes

2
Posts

1
Views

R

It's not clear what logic you are trying to achieve with regards to null rows, but it seems the logic might be: Same string aggregation on all rows per polNum If boxNo is null and there are other rows with the same polNum then replace the string aggregation with null CREATE TABLE #Doc (polNum int, boxNo varchar(5), batchNum varchar(7)) ; INSERT INTO #Doc (polNum, boxNo, batchNum) VALUES (111111, null, 'qwe1234'), (111111, 'ff/55', 'rto1235'), (111111, 'ee/59', 'yhn1238'), (111111, 'ww/55', 'rto1235'), (222222, 'dd/58', 'jkl1234'), (222222, NULL, 'fgh1234'), (333333, NULL, 'asz1235'), (444444, 'ff/55', 'edc1234'), (444444, 'tt/58', 'qaz1234') ; SELECT polNum, boxNo , CASE WHEN boxNo IS NOT NULL OR COUNT(*) OVER (PARTITION BY polNum) = 1 THEN STUFF(( SELECT DISTINCT ','+batchNum FROM #DOC a WHERE a.polNum=d.polNum FOR XML PATH(''), TYPE ).value('text()[1]','nvarchar(max)'),1,LEN(','),'') END batchNum FROM #DOC d <p><a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=2829d1d3f965e3f04ba0c967b4352e3f" rel="nofollow noreferrer">db<>fiddle</a></p>

What is the relation between renaming the computer and sp_dropserver/sp_addserver?

Suggested Topics

Granting a user a permission to restore a database SQL, Database Testing • sql server permissions restore sql server 2019 role • • rosemadder

GRANT permissions to all tables, views, procedures in SQL Server 2000 SQL, Database Testing • sql server permissions sql server 2000 • • gionna

SqlConnection failure in C# Software Programming • c sharp sql server • • Bogopo

XML vs. Relational databases? SQL, Database Testing • xml relational theory • • carriann

Error when formatting CEP with Format() function Software Programming • sql server • • Saumya

How to convert SQL Server database file to MongoDB? SQL, Database Testing • sql server database design mongodb • • Demir

What is SSIS in relation to SQL Server? SQL, Database Testing • sql server t sql ssis ssdt • • irl

Index on Persisted Computed column needs key lookup to get columns in the computed expression SQL, Database Testing • sql server sql server 2008 exec languageution plan c languageomputed c langu bookmark lookup • • Alberto

SQL script to count rows for all views in a database with SELECT-only access SQL, Database Testing • sql server permissions view count • • rielyn

rename database SQL, Database Testing • sql server • • elysha

Can a 2NF relation be M:M? SQL, Database Testing • database design • • kalena

Finding aggregated values based on dates in another table SQL, Database Testing • sql server t sql • • rielyn

UNF to 3NF is this relation workable? SQL, Database Testing • database design normalization • • emmalee

Renaming the target table of a trigger SQL, Database Testing • sql server sql server 2008 r2 trigger • • terrea

String aggregation using STUFF SQL, Database Testing • sql server sql server 2016 string aggregation • • Mystic

Granting a user a permission to restore a database
SQL, Database Testing • sql server permissions restore sql server 2019 role • • rosemadder

GRANT permissions to all tables, views, procedures in SQL Server 2000
SQL, Database Testing • sql server permissions sql server 2000 • • gionna

SqlConnection failure in C#
Software Programming • c sharp sql server • • Bogopo

XML vs. Relational databases?
SQL, Database Testing • xml relational theory • • carriann

Error when formatting CEP with Format() function
Software Programming • sql server • • Saumya

How to convert SQL Server database file to MongoDB?
SQL, Database Testing • sql server database design mongodb • • Demir

What is SSIS in relation to SQL Server?
SQL, Database Testing • sql server t sql ssis ssdt • • irl

Index on Persisted Computed column needs key lookup to get columns in the computed expression
SQL, Database Testing • sql server sql server 2008 exec languageution plan c languageomputed c langu bookmark lookup • • Alberto

SQL script to count rows for all views in a database with SELECT-only access
SQL, Database Testing • sql server permissions view count • • rielyn

rename database
SQL, Database Testing • sql server • • elysha

Can a 2NF relation be M:M?
SQL, Database Testing • database design • • kalena

Finding aggregated values based on dates in another table
SQL, Database Testing • sql server t sql • • rielyn

UNF to 3NF is this relation workable?
SQL, Database Testing • database design normalization • • emmalee

Renaming the target table of a trigger
SQL, Database Testing • sql server sql server 2008 r2 trigger • • terrea

String aggregation using STUFF
SQL, Database Testing • sql server sql server 2016 string aggregation • • Mystic