Problems with a job that rebuilds indexes We have a Maintenance Plan (MP) job that rebuilds indexes every week.

The last time, the job failed because of a deadlock. Should I rerun the whole job, or do I just need to rerun the rebuild of the deadlocked index?

The rebuilding of indexes is being performed using standard tools of MP plan.

As I understand it, if a standard MP job for index rebuild stopped because of deadlock, it means that other indexes (after the deadlocked index) also need to rebuild? Or MP job will rebuild all indexes except this one that failed?

Here is example of entry from job history:

  Progress: 2017-09-30 21:00:33.06
  Source: Rebuild Index Task
  Executing query "USE []  ".: 0% complete
  End Progress
  Progress: 2017-09...
  The package execution fa...
  The step failed.

There is no information about which index rebuilds completed, only information that a query started to execute. Looks like one way to check what was rebuilt or not is to check index fragmentation for whole database using T-SQL scripts?

There is only information about deadlock and nothing more in MP log.

You should rebuild index which it deadlocked on, and rebuild any other indexes which not did not occur afterwards- if whole job failed and stopped.
Hopefully you are rebuilding indexes, during less busy times, say nighttime or after hours. If you have no choice, and have a continuously busy system,
Please Rebuild with Online option,

ALTER INDEX ALL ON [dbo].[Test] REBUILD WITH (ONLINE = ON);

Additionally, I would only rebuild indexes which require it.  Hallengren website has an option to reorganize indexes say with 5% Fragmentation, and rebuild for 30% Fragmentation. I don't think Windows SQL Server Maintenance has this option yet.

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Check when the deadlock occurred, and how the long the total job duration lasted. If those points meet, then the job stopped at the deadlock. If the job duration lasted longer than deadlock, more indexes were rebuilt.


MySQL Unions/Subselects not utilizing keys from associated tables I've noticed by doing EXPLAINs that when a MySQL union between two tables is used, mysql creates a temporary table, but the temp table does not use keys, so queries are slowed considerably.

Here is an example:

SELECT * FROM (
    SELECT
    `part_number`,
    `part_manufacturer_clean`,
    `part_number_clean`,
    `part_heci`,
    `part_manufacturer`,
    `part_description`
    FROM `new_products` AS `a`
UNION
    SELECT
    `part` as `part_number`,
    `manulower` as `part_manufacturer_clean`,
    `partdeluxe` as `part_number_clean`,
    `heci` as `part_heci`,
    `manu` as `part_manufacturer`,
    `description` as `part_description`
    FROM `warehouse` AS `b`
) AS `c`

WHERE `part_manufacturer_clean` = 'adc'

EXPLAIN yields this:

id        select_type    table        type    possible_keys    key      key_len    ref      rows     Extra
1         PRIMARY        <derived2>   ALL     (NULL)           (NULL)   (NULL)    (NULL)    17206    Using where
2         DERIVED        a            ALL     (NULL)           (NULL)   (NULL)    (NULL)    17743
3         UNION          b            ALL     (NULL)           (NULL)   (NULL)    (NULL)    5757
(NULL)    UNION RESULT   <union2,3>   ALL     (NULL)           (NULL)   (NULL)    (NULL)    (NULL)

In this case, part_manufacturer_clean and manulower are keys in both tables. When I don't use the subselects and union, and just use one table, everything works fine. I'm not sure if the issue is with the union or with the subselects. Is there any way to union two tables and still use keys/indexes for performance?

The optimizer is not that clever - yet (see footer).

You could still use UNION, if you rewrite the query (a job that could/should be done by a decent optimizer):

    SELECT
    `part_number`,
    `part_manufacturer_clean`,
    `part_number_clean`,
    `part_heci`,
    `part_manufacturer`,
    `part_description`
    FROM `new_products` AS `a`
    WHERE `part_manufacturer_clean` = 'adc'
UNION
    SELECT
    `part` as `part_number`,
    `manulower` as `part_manufacturer_clean`,
    `partdeluxe` as `part_number_clean`,
    `heci` as `part_heci`,
    `manu` as `part_manufacturer`,
    `description` as `part_description`
    FROM `warehouse` AS `b`
    WHERE `manulower` = 'adc' ;

You can also try latest MariaDB versions (5.3 and 5.5) that have several improvements on the optimizer (if changing to MariaDB is an option).

You can also try MySQL 5.6 (still in beta) which has some improvements on the optimizer, too.


SQL Server auto-trim of varchar value in equal comparison but not like comparison I came across some interesting behaviour on SQL Server (observed in 2005 and 2012) today that I was hoping someone could explain.

A query doing a comparison using = on an NVARCHAR field ignored the trailing space in the string (or auto-trimmed the value before comparison) but the same query using the like operator did not ignore the space. Collation being used is Latin1_General_CI_AS in 2012.

Consider this SQL Fiddle: http://sqlfiddle.com/#!6/72262/4

Note that the like operator does not return a result for the trailing space string, but the = operator does. Why is this?

Bonus points: I am unable to replicate this on a VARCHAR field, I would have thought that a space would be handled in the same way in both data types - is this true?

My initial answer suggested that the ANSI_PADDING flag set to OFF may be to blame for the difference in behavior. However, this is incorrect; this flag only has an effect on storage, but not equality comparison.

The difference stems from Microsoft's implementation of the SQL standard. The standard states that when checking for equality, both strings left and right of the equality operator have to be padded to have the same length. This explains the following results:

insert into test_padding (varchar_clmn, nvarchar_clmn) values ('space ', 'nspace ')
go
-- equality for varchar column
select count(*) from test_padding where varchar_clmn = 'space' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space    ' --returns 1
-- equality for nvarchar column
select count(*) from test_padding where nvarchar_clmn = 'nspace' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace    ' --returns 1

The LIKE operator does not pad its operands. It also behaves differently for VARCHAR and NVARCHAR column types:

-- likeness for varchar column
select count(*) from test_padding where varchar_clmn like 'space' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space    ' -- returns 0
-- likeness for nvarchar column
select count(*) from test_padding where nvarchar_clmn like 'nspace' -- returns 0
select count(*) from test_padding where nvarchar_clmn like 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn like 'nspace    ' -- returns 0

The behavior of the LIKE operator for the ASCII type is SQL Server-specific; for the Unicode type it is ANSI-compliant.


MySQL connection doesn't work anymore I had two MySQL servers running (127.0.0.1:3306 and 127.0.0.1:3307). Now I get this strange error: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet' I can connect with localhost:3307, but there aren't any tables/databases. I'm fairly sure /etc/my.cnf is right:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root
password   = ***

[mysqld]
port       = 3306
socket   = /var/run/mysql/mysql.sock

[mysqld2]
port       = 3307
socket   = /var/run/mysql/mysql2.sock

What can I do now? How can I sanely restart the MySQL server?

Perhaps more importantly:

How can I make sure all the tables still exist?

Here is one thing I quickly see is missing : Each [mysqld] needs to have its own datadir. No two mysqld groups can share the same datadir. You need to allocate different datadirs in different folders and make sure you run "chown -R mysql:mysql (datadir folder)" on all datadirs.

Your /etc/my.cnf should look something like this:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root
password   = ***

[mysqld]
port       = 3306
datadir  = /var/lib/mysql1
socket   = /var/run/mysql/mysql.sock

[mysqld2]
port       = 3307
datadir  = /var/lib/mysql2
socket   = /var/run/mysql/mysql2.sock

Check out this link from the Pythian Group on how to start and stop the individual DB services.

From my answer, you should see that there must be a complete copy of the data in /var/lib/mysql1, /var/lib/mysql2, etc.


Is this an excessively large 'Lock' time and is it indicative of a problem? Given:

Production environment
Application Servers using Hibernate
SQL Server Studio Manager v17.5
SQL Server 2016 in a clustered HAG setup
SQL Servers do NOT have the Query Store feature enabled
The author of this question is a software engineer with enough SQL
Server knowledge to be categorized as mostly harmless

update 1

Database growth settings: Unlimited, 1024000 KB, data only
instant_file_initialization_enabled - Yes
is_auto_update_stats_async_on - No

update 2

The server has 4 CPU cores
There are spikes of waiting tasks of over 3,000,000.  I have no idea yet what they are. This must be the reason for the large 'lock' times.
These spikes occur every 10 or 15 seconds.  I have the following graph updating once a second:

The problem:
The root problem is that at seemingly random times of a busy day a couple of SQL queries timeout, however, for the purposes of this question I am interested in whether the screen grab is indicative of a problem in itself. Perhaps this is to subjective, but I have no experience with this value.
Action:
The failures themselves do not point directly to a concrete issue and therefore I am currently gathering evidence and attempting a process of elimination where possible.
Currently I am investigating whether excessive wait times and a 'perfect storm' of queries could cause a cascade of locks and thus a query timeout.
Evidence Gathered:

Several queries are resulting in either full index scans or full
table scans.
Several screen grabs with execution plans showing table scans.
Cursory inspection shows that indexes do exist - yet not used. I might be able to sanitize the screen grabs if they will prove useful.
The screen grab below showing a large wait time.

Question:
What other information would help determine if locking and wait times might be the cause of the query timeouts? For example, I have the following screen grab from sql server studio manager activity monitor. The value looked surprising to me.

So the elephant in the room. Is that wait time excessive?

The lock waits from the screenshot show 18,024,389 ms / sec as the recent (average) wait time over the last couple of minutes.  That means for every second of "wall clock time," there are 18,000 seconds (5 hours?!) of lock waits accumulated by queries.  That's so tremendously bad that I wonder if it's just a bug in the Activity Monitor UI.
Depending on how many cores the server has, and how many queries are running concurrently, even the smaller number (2.5 seconds of lock waits per second of wall clock time) is not ideal.
Those waits could imply a blocking chain (you can use sp_WhoIsActive to identify the lead blocker and then try to fix why it's blocking everything).  Regardless, they can definitely contribute to these client side timeouts you've described - every second that a query waits on locks is a second that it's not making progress on the actual query being run.


Backup/Restore Log File Question My question is in regards to restoring a DEV database from PROD.

When I take a backup of my Production Database, the LogFile is about 10GB. However, when it actually restores to DEV, it blows up to 60GB (with 50GB of free space) -- Causing major alerts due to a rapid growth on that disk.

Is there a reason something like this would happen?

Both PROD and DEV are Simple Recovery.

Thanks!

Run this on the production database

DBCC OPENTRAN(name_of_database)

There is probably an open transaction that won't allow your logfile (.ldf) to recycle. You can also verify this with

select log_reuse_wait_desc, * from sys.databases

Once you've killed or stopped the transaction that's preventing your ldf from being reused, you can shrink the log file to a more reasonable size.

USE MYDATABASE
GO

-- get logical name of ldf file
select name, type_desc from sys.databases where type=1

/* checks to see if ldf's vlf (virtual log files) can be marked inactive and reused */

/* this may take a long time depending on the size of your database and the activity*/

CHECKPOINT
CHECKPOINT

/* shrink the log file */
DBCC SHRINKFILE (N'logicalname' , some_reasonable_size_in_mb)

Once you've taken these steps on your production database, you can retry backing up the database for you dev environement.

Before restoring your backup, you can check how much space it will take up with

RESTORE FILELISTONLY FROM DISK = 'path\of\backup_file'

Look at the size column which will give you the size of each file being restored in bytes. This way you'll have a better idea of how much space you need before even trying to restore. Prevents unwanted surprises.


MYSQL 5.7 Partition a table based on partition key of another related table Please understand a little background before getting to know the problem.
TABLE transaction (

t_id: AUTO INCREMENT BIGINT

t_execute_on_date: DATE,

timestamp: DATETIME,

... other columns
)

The table transaction has 300M (three hundred million) rows and growing (1M rows daily) and growing and we need to drop old data based on the column  t_execute_on_date. I plan to drop old data (older than 2 years. approx 40M rows) on the basis of creation monthly range partitions based on KEY t_execute_on_date. I have tested this on a test server while using the procedure suggested in @RickJames blog post PARTITION Maintenance in MySQL:  create a new partitioned table, with optimised indexes according to queries and then reinserting records, and drop old partitions, and this approach seems fine to me. Any suggestions in this approach are also welcome.
I have done this in master-master replication with one master offline, reorganising data, and recovering replication lag, and then drop the old master, bring the traffic to new one, if everything is fine.
TABLE transaction_history (

th_id: AUTO INCREMENT BIGINT,

t_id: FOREIGN KEY (t_id of TABLE transaction)

timestamp: DATETIME,

... other columns
)

This table has 1.5B+ (One billion five hundred million) rows growing 15M+ daily.
This table has related history of transaction and contains multiple rows corresponding to one t_id.
Once old transaction data from table transaction is dropped, I would like to drop the same from this table transaction_history as well.
This table does not have any key like t_execute_on_date based on which I can partition.
This table has 1.5B+ rows I think that adding an additional column t_execute_on_date is just not possible.
Like the approach discussed above would I have to manually pull data from table transaction_history based on t_id of TABLE transaction (reorganised ,old data dropped) and reinserting only the selected records into the new table transaction_history_partitioned?
The PROBLEM I face is how to purge old data from table transaction_history?
Using MYSQL 5.7
Running on
[2.8T HDD, Amazon Linux AMI 2017.03, 16 core, 63GB RAM]

NOTE:  FOREIGN KEYS are not allowed in partitioned tables.  Simply drop the FKs but keep the indexes that they generated.  Presumably, the FK checks are redundant now that you have debugged your app.

adding an additional column t_execute_on_date is just not possible.

See pt-online-schema-change or gh-ost.
Using PARTITIONs is the optimal way to delete "old" data from the history table, but there are other approaches:  http://mysql.rjweb.org/doc.php/deletebig
The one that comes to mind is to have a continually-running job that keeps looking at the "next 1000" rows to see if any of them should be deleted.  (It must walk through the table using the PRIMARY KEY.)  It might do a LEFT JOIN to see which ones are gone, as hinted at in this pseudo-code:
$a = 0;
Loop...
    $z = SELECT th_id FROM transaction_history order by th_id LIMIT 1000,1;
    exit loop if $z IS NULL
    SELECT th_id
        FROM      transaction_history AS the
        LEFT JOIN transaction AS t  ON th.t_id = t.t_id
        WHERE t.t_id IS NULL
          AND th.th_id >= $a
          AND th.t_id   < $z
    DELETE those rows -- probably better to do this as a single query
$a = $z;

When you exit the Loop, simply start over.
The hope is that this approach to deleting "old stuff" will be fast enough to keep up, though there will be some lag.
Still, I would hope that pt-online-schema-change or gh-ost can turn a non-partitioned table into a partitioned table with minimal downtime, regardless of table size.  (No FKs.)
Caution:  The optimal indexes for a partitioned table are usually different than the equivalent non-partitioned table.  Toss any unused tables when adding partitioning; there is some overhead when building a table with the secondary indexes already in place.


Oracle Materialized View Refresh Group - Montly Interval I have a few Materialized Views that I need to update weekly and monthly.

So for the weekly I'm using:

execute DBMS_REFRESH.MAKE(
name=>'LPMVRefresh_Weekly_1',
list=>'mySchema.MV1_mv,mySchema.MV2_mv',
next_date => TO_DATE('2013-09-08:00:30:00','YYYY-MM-DD:HH24:MI:SS'),
interval =>'sysdate+7');

How can I do so they refresh monthly, say every 1st of the month at 1 am? Is there something like Interval => sysdate+monthly?

Next month's 1st day 1am can be calculated using the following SQL:

SELECT TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH')+1/24 AS ANSWER FROM DUAL;

The most probably it can be simplified.


Switching from 2 large collections to multiple small ones in MongoDB 3.4 I know this question is similar to other ones. But...

Only that we are switching from 2 very large collections (15Gb in size, constantly growing).

Basically those 2 large collections keep different data for our clients. I'm thinking of creating 2 collections for each client. In terms of size, they will be from 20MB to a few GB each (depending on client size).

Now the pros of having these is that when we loose clients, we need to remove their data. And will be much easier using dropCollection.

The other one is the index, as we keep the client Id on each document in the large collection. And this is not needed, we can remove this index.

About sharding. Now those 2 large ones are sharded across 3 servers. With the small ones, what would be best:

automatically create and shard them when a client comes in (we create collections, add the shard key, all automatically).
only shard the large clients, manually.

What happens if there are lots of small collections (like 10.000, each of 30mb let's say) vs a very large one?

Multiple small collections will take more space than few big collections.
Depending on amount of memory, big collections indexes are more effective than large amount of indexes of small collections.


SQL Server TempDB on Local SSDs with VMs Two physical Servers in a Cluster. Multiple SQL 2016 Virtual Machines. Fibre Channel SAN Storage used for the Virtual Machine Operating System and Database Data Files. I've seen multiple sources claim that using local SSDs for the TempDB is best practice.

How should the Local Storage be presented to the Virtual Machine for the TempDB? If the Virtual Machine migrates within the cluster, it will lose the virtual hard drive. How can this best practice be implemented with the above scenario.

You have to specify the same local storage paths on both nodes for the TempDB in order for the nodes to fail over properly.


How to Replace Hash Match (Partial Aggregate) with Stream Aggregate My execution plan shows a hash match (partial aggregate) instead of a stream aggregate even though I have a non-clustered index (which basically means sorting is already in place). It gives me a suggestion to create another non-clustered index. I do not want to create another index as the table has high I/O.
How do I convert the hash match into a stream aggregate?

Not a lot of information here, but to directly answer the question that was asked: you can use the ORDER GROUP query hint. From the documentation:

  { HASH | ORDER } GROUP

  Specifies that aggregations described in the
  GROUP BY, or DISTINCT clause of the query should use hashing or
  ordering.

That query hint may help you verify that a stream aggregate is a better choice than the hash aggregate. A few things to keep in mind are that it applies to the whole plan and it will continue to take effect even if there's an index change which means that an explicit sort is required.


Why is script with " xp_cmdshell " in comment failing with a transport level error? When executing the following batch that is part of a large script:

...
GO
-----------------------------------------------------------------------------------------------
-- Removed the xp_cmdshell for deleting the backup file
-----------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PR_MyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PR_MyProc]
GO
...

We get the following error on a remote SQL Server:

  Msg 121, Level 20, State 0, Line 0 A transport-level error has
  occurred when receiving results from the server. (provider: TCP
  Provider, error: 0 - The semaphore timeout period has expired.)

When we remove "xp_cmdshell" from the comment, the batch succeeds. This error only occurs on some of our SQL servers. We are running the script remotely. The version of SQL Server that fails is:

  Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (X64) (Build 9200: ) (Hypervisor)

On another server the same script succeeds. That server has this version:

  Microsoft SQL Server 2014 - 12.0.2000.8 (X64)   Feb 20 2014 20:04:26   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 (X64) (Build 7601: Service Pack 1) (Hypervisor)

Both servers have xp_cmdshell enabled. The failing server is running Windows 2012 R2 and the server that succeeds is running Windows Server 2008 R2 SP1. If we run the script on the server locally it succeeds. If we run the script remotely from another virtual server, it fails. We also found that the script only fails if the comment has "(space) xp_cmdshell (space)".

Can anyone tell me why this script fails? And why does it fail on some servers and not others?

There is no antivirus running on the server. SQL Server Management Studio is connected, changing the comment to remove xp_cmdshell always succeeds and the original script always fails.

There is a connect item discussing (well discussing...) exactly this issue, so it appears to be a bug.

Text 'xp_cmdshell' causes transport-level error: semaphore timeout period has expired from remote system

It appears that the string causes problems, not only in comments but also anywhere it's used as an exact string so even

SELECT 'xp_cmdshell'

results in the same error according to the bug report.

So it appears that the exact string xp_cmdshell is indeed the problem, unfortunately the connect item is closed as "not reproducible".
That being said, I can't reproduce it (on a local connection) on my SQL 2014 (12.0.2000.8, Windows NT 6.3  (Build 9600: )) test system either, so the operating system is probably relevant. Executing the query from a local management studio vs over the network might also be relevant as noted in the connect item.

So you could either work around it by changing the exact text (as you did) or try to reopen the connect item or file a bug report through Microsoft Support.


SQL query that concatenates values from duplicate rows in a single table Let's say I have a table of employees, like this:

Name     Sex     Role

Bob      M       Developer
Joe      M       QA

Now, I have a problem with duplicated rows in this table. I will fix it sometime but it's not the issue. What I currently need is something that queries such a table:

Name     Sex     Role

Bob      M       Developer
Bob      M       Janitor
Joe      M       QA
Joe      M       CEO

And will output the following rows:

Name     Role

Bob      Developer, Janitor
Joe      QA, CEO

It doesn't matter to me if the "Role" will be split to separate columns or be a single column with multiple values.

Using SQL Server 2008 if it matters.

Group_concat as in MySQL is not available in SQL Server up to SQL Server 2016. vNext will introduce STRING_AGG however that provides equivalent functionality.

You have some manual options though:

build a scalar function that takes an employee name/id as parameter and shows concatenated role values and apply this function to each employee
use xml functions
use an already built aggregate (similar as first option)

A solution for your current case (just took Brad's answer from SO and customized for your table) would be:

SELECT name, LEFT(roles , LEN(roles )-1) AS roles
FROM employee AS extern
CROSS APPLY
(
    SELECT role + ','
    FROM employee AS intern
    WHERE extern.name = intern.name
    FOR XML PATH('')
) pre_trimmed (roles)
GROUP BY name, roles;

These details are gathered from different sources:

Simulating group_concat MySQL function in MS SQL Server 2005? - on StackOverflow.com
Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005 - blog
GROUP_CONCAT in SQL Server - blog
T-SQL Equivalent For Group_Concat() Function - MSDN


how to script out the user defined table types? I can Get name and definition of all table types
using either of the following scripts:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL
   DROP TABLE #RADHE

CREATE TABLE #RADHE
(
 RADHE SYSNAME,
 COLUMN_NAME SYSNAME,
 TYPE_COLUMN SYSNAME,
 PRIMARY KEY CLUSTERED (RADHE,COLUMN_NAME)
 )

DECLARE @sql nvarchar(max) = N'',
  @stub nvarchar(max) = N'SELECT [RADHE]=N''$--RADHE--$'',
   COLUMN_NAME=name, TYPE_COLUMN=system_type_name
   FROM sys.dm_exec_describe_first_result_set(''DECLARE
   @tvp $--RADHE--$; SELECT * FROM @tvp;'',null,null)
   ORDER BY column_ordinal;';

SELECT @sql += REPLACE(@stub, N'$--RADHE--$',
  QUOTENAME(s.name) + N'.' + QUOTENAME(t.name))
FROM sys.table_types AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

INSERT INTO #RADHE
EXEC sys.sp_executesql @sql;

SELECT * FROM #RADHE

SELECT
    tt.name AS table_type_name,
    c.name AS column_name,
    c.column_id,
    t.name AS type_name,
    c.max_length,
    c.precision,
    c.scale,
    c.collation_name,
    c.is_nullable
FROM
    sys.columns As c
    JOIN sys.table_types AS tt
        ON c.object_id = tt.type_table_object_id
    JOIN sys.types AS t
        ON t.user_type_id = c.user_type_id
ORDER BY
    tt.name,
    c.column_id

and I can even GRANT REFERENCE on all user defined types using the following script:

    SELECT t.name,
           'GRANT REFERENCES ON TYPE::'
           + SCHEMA_NAME(t.schema_id)
           + '.'
           + t.name
           + ' TO public;' AS command_to_run
    FROM   sys.types AS t
where 1=1
 AND T.is_table_type = 1

But is there a way to script out all table types in a database?

I am looking to script out this table type, please note the constraints and index created with it:

use TableBackups
go

IF EXISTS(SELECT *
            FROM SYS.table_types tt
           WHERE tt.NAME=N'DistCritGroupData'
             AND SCHEMA_NAME(tt.SCHEMA_ID) = N'dbo')
   DROP TYPE DBO.DistCritGroupData

CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
  (
    [DistCritTypeId] [int] NOT NULL UNIQUE,
    [ItemAction] [int] NOT NULL,
    [ObjectId] [int] NOT NULL,
    [OperatorType] [int] NOT NULL,

    PRIMARY KEY NONCLUSTERED
    (
       [DistCritTypeId] ASC
    ),

    INDEX CIX CLUSTERED (ObjectId, OperatorType)
  );

Honestly, the amount of time you'll spend writing a version of this that accounts for all possible combinations of indexes, constraints, and default values, and troubleshoot all the combinations you don't expect from your first use case, I don't think you'll ever get that time back no matter how many table types you have to script, which you can always do from Object Explorer (or Object Explorer Details, for multiple):

Just for fun, I ran a trace to see what Management Studio sends to SQL Server in order to generate the create script for that table type, and it was about as pretty as I expected:

exec sp_executesql N'SELECT
SCHEMA_NAME(tt.schema_id) AS [Schema],
tt.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)',
N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',
@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
clmns.column_id AS [ID],
clmns.name AS [Name],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
ISNULL(clmns.collation_name, N'''') AS [Collation],
clmns.column_encryption_key_id AS [ColumnEncryptionKeyID],
ceks.name AS [ColumnEncryptionKeyName],
clmns.is_computed AS [Computed],
ISNULL(cc.definition,N'''') AS [ComputedText],
s1clmns.name AS [DataTypeSchema],
(case when clmns.default_object_id = 0 then N'''' when d.parent_object_id > 0 then N'''' else d.name end) AS [Default],
ISNULL(dc.Name, N'''') AS [DefaultConstraintName],
(case when clmns.default_object_id = 0 then N'''' when d.parent_object_id > 0 then N'''' else schema_name(d.schema_id) end) AS [DefaultSchema],
clmns.encryption_algorithm_name AS [EncryptionAlgorithm],
CAST(clmns.encryption_type AS int) AS [EncryptionType],
clmns.generated_always_type AS [GeneratedAlwaysType],
ISNULL(clmns.graph_type, 0) AS [GraphType],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS numeric(38)) AS [IdentitySeedAsDecimal],
CAST(ISNULL(ic.increment_value,0) AS numeric(38)) AS [IdentityIncrementAsDecimal],
CAST(0 AS bit) AS [IsClassified],
CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
CAST(clmns.is_filestream AS bit) AS [IsFileStream],
CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
CAST(clmns.is_masked AS bit) AS [IsMasked],
CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],
CAST(clmns.is_sparse AS bit) AS [IsSparse],
CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
ISNULL((SELECT ms.masking_function FROM sys.masked_columns ms WHERE ms.object_id = clmns.object_id AND ms.column_id = clmns.column_id), N'''') AS [MaskingFunction],
ISNULL(ic.is_not_for_replication, 0) AS [NotForReplication],
clmns.is_nullable AS [Nullable],
CAST(clmns.scale AS int) AS [NumericScale],
CAST(clmns.precision AS int) AS [NumericPrecision],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
(case when clmns.rule_object_id = 0 then N'''' else r.name end) AS [Rule],
(case when clmns.rule_object_id = 0 then N'''' else schema_name(r.schema_id) end) AS [RuleSchema],
ISNULL(baset.name, N'''') AS [SystemType],
ISNULL(xscclmns.name, N'''') AS [XmlSchemaNamespace],
ISNULL(s2clmns.name, N'''') AS [XmlSchemaNamespaceSchema],
ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
usrt.name AS [DataType]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
LEFT OUTER JOIN sys.column_encryption_keys AS ceks ON (ceks.column_encryption_key_id = clmns.column_encryption_key_id)
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.schemas AS s1clmns ON s1clmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.default_constraints as dc ON clmns.default_object_id = dc.object_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'DistCritTypeId',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'ItemAction',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'ObjectId',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'OperatorType',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=0 AND p.class=6
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
i.name AS [Name],
CAST(ISNULL(si.bounding_box_xmax,0) AS float(53)) AS [BoundingBoxXMax],
CAST(ISNULL(si.bounding_box_xmin,0) AS float(53)) AS [BoundingBoxXMin],
CAST(ISNULL(si.bounding_box_ymax,0) AS float(53)) AS [BoundingBoxYMax],
CAST(ISNULL(si.bounding_box_ymin,0) AS float(53)) AS [BoundingBoxYMin],
CAST(case when (i.type=7) then hi.bucket_count else 0 end AS int) AS [BucketCount],
CAST(ISNULL(si.cells_per_object,0) AS int) AS [CellsPerObject],
CAST(i.compression_delay AS int) AS [CompressionDelay],
~i.allow_page_locks AS [DisallowPageLocks],
~i.allow_row_locks AS [DisallowRowLocks],

        CASE WHEN ((SELECT tbli.is_memory_optimized FROM sys.tables tbli WHERE tbli.object_id = i.object_id)=1 or
        (SELECT tti.is_memory_optimized FROM sys.table_types tti WHERE tti.type_table_object_id = i.object_id)=1)
        THEN ISNULL((SELECT ds.name FROM sys.data_spaces AS ds WHERE ds.type=''FX''), N'''')
        ELSE CASE WHEN ''FG''=dsi.type THEN dsi.name ELSE N'''' END
        END
       AS [FileGroup],
CASE WHEN ''FD''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamFileGroup],
CASE WHEN ''PS''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamPartitionScheme],
i.fill_factor AS [FillFactor],
ISNULL(i.filter_definition, N'''') AS [FilterDefinition],
i.ignore_dup_key AS [IgnoreDuplicateKeys],

        ISNULL(indexedpaths.name, N'''')
       AS [IndexedXmlPathName],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
CAST(
          CASE i.type WHEN 1 THEN 0 WHEN 4 THEN 4
                      WHEN 3 THEN CASE xi.xml_index_type WHEN 0 THEN 2 WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 8 END
                      WHEN 4 THEN 4 WHEN 6 THEN 5 WHEN 7 THEN 6 WHEN 5 THEN 9 ELSE 1 END
        AS tinyint) AS [IndexType],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
i.is_disabled AS [IsDisabled],
CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
CAST(ISNULL(k.is_system_named, 0) AS bit) AS [IsSystemNamed],
CAST(OBJECTPROPERTY(i.object_id,N''IsMSShipped'') AS bit) AS [IsSystemObject],
i.is_unique AS [IsUnique],
CAST(ISNULL(si.level_1_grid,0) AS smallint) AS [Level1Grid],
CAST(ISNULL(si.level_2_grid,0) AS smallint) AS [Level2Grid],
CAST(ISNULL(si.level_3_grid,0) AS smallint) AS [Level3Grid],
CAST(ISNULL(si.level_4_grid,0) AS smallint) AS [Level4Grid],
ISNULL(s.no_recompute,0) AS [NoAutomaticRecomputation],
CAST(ISNULL(INDEXPROPERTY(i.object_id, i.name, N''IsPadIndex''), 0) AS bit) AS [PadIndex],
ISNULL(xi2.name, N'''') AS [ParentXmlIndex],
CASE WHEN ''PS''=dsi.type THEN dsi.name ELSE N'''' END AS [PartitionScheme],
case UPPER(ISNULL(xi.secondary_type,'''')) when ''P'' then 1 when ''V'' then 2 when ''R'' then 3 else 0 end AS [SecondaryXmlIndexType],
CAST(ISNULL(spi.spatial_index_type,0) AS tinyint) AS [SpatialIndexType]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
LEFT OUTER JOIN sys.spatial_index_tessellations as si ON i.object_id = si.object_id and i.index_id = si.index_id
LEFT OUTER JOIN sys.hash_indexes AS hi ON i.object_id = hi.object_id AND i.index_id = hi.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.tables AS t ON t.object_id = i.object_id
LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and (i.index_id < 2 or (i.type = 7 and i.index_id < 3))
LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id
LEFT OUTER JOIN sys.selective_xml_index_paths AS indexedpaths ON xi.object_id = indexedpaths.object_id AND xi.using_xml_index_id = indexedpaths.index_id AND xi.path_id = indexedpaths.path_id
LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON i.object_id = filetableobj.object_id
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.object_id = i.object_id
LEFT OUTER JOIN sys.xml_indexes AS xi2 ON xi2.object_id = xi.object_id AND xi2.index_id = xi.using_xml_index_id
LEFT OUTER JOIN sys.spatial_indexes AS spi ON i.object_id = spi.object_id and i.index_id = spi.index_id
WHERE
(tt.name=@_msparam_2 and SCHEMA_NAME(tt.schema_id)=@_msparam_3)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritGroupData',@_msparam_3=N'dbo'

exec sp_executesql N'SELECT
cstr.name AS [Name],
cstr.is_not_for_replication AS [NotForReplication],
~cstr.is_not_trusted AS [IsChecked],
~cstr.is_disabled AS [IsEnabled],
CAST(cstr.is_system_named AS bit) AS [IsSystemNamed],
CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
cstr.definition AS [Text]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.check_constraints AS cstr ON cstr.parent_object_id=tt.type_table_object_id
LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON filetableobj.object_id = cstr.object_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
ISNULL(s1tt.name, N'''') AS [Owner],
CAST(case when tt.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
tt.name AS [Name],
tt.type_table_object_id AS [ID],
SCHEMA_NAME(tt.schema_id) AS [Schema],
obj.create_date AS [CreateDate],
obj.modify_date AS [DateLastModified],
tt.max_length AS [MaxLength],
tt.is_nullable AS [Nullable],
ISNULL(tt.collation_name, N'''') AS [Collation],
CAST(case when tt.is_user_defined = 1 then 1 else 0 end AS bit) AS [IsUserDefined],
CAST(tt.is_memory_optimized AS bit) AS [IsMemoryOptimized]
FROM
sys.table_types AS tt
LEFT OUTER JOIN sys.database_principals AS s1tt ON s1tt.principal_id = ISNULL(tt.principal_id, (TYPEPROPERTY(QUOTENAME(SCHEMA_NAME(tt.schema_id)) + ''.'' + QUOTENAME(tt.name), ''OwnerId'')))
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
LEFT OUTER JOIN sys.objects AS obj ON obj.object_id = tt.type_table_object_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'CIX',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'ObjectId',@_msparam_3=N'CIX',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'OperatorType',@_msparam_3=N'CIX',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'PK__TT_DistC__199F41EAA68706DB',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritTypeId',@_msparam_3=N'PK__TT_DistC__199F41EAA68706DB',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'UQ__TT_DistC__199F41EAC5355FAA',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritTypeId',@_msparam_3=N'UQ__TT_DistC__199F41EAC5355FAA',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

I mean, that is just awful. And all it does is return all of those resultsets to SSMS; then there is some C# and/or SMO plumbing that actually steps through all the possible combinations and generates the script. This is hidden away in application code that is much harder to trace (especially without violating EULA), and so your ability to determine all of the issues they encountered when generating the script is pretty limited.

So I think if your goal is to have a nice, handy little piece of T-SQL code that is faster than right-clicking a table name in Object Explorer, you're going to be out of luck.

If I didn't have SSMS (or the plan was to create 60,000 table types), I would rather keep track of these creations using a DDL trigger than try to reverse engineer the metadata to generate the original statement. I wrote this tip a long time ago, but the same concepts apply today (you could maybe just filter the types of objects you keep track of):

SQL Server DDL Triggers to Track All Database Changes

Borrowing from that, you could create this table:

CREATE TABLE dbo.TableTypeCreationEvents
(
  EventDate    datetime NOT NULL DEFAULT sysutcdatetime(),
  EventDDL     nvarchar(max),
  SchemaName   nvarchar(128),
  ObjectName   nvarchar(128)
);

And then this trigger:

CREATE TRIGGER DDLCaptureTableTypeCreations ON DATABASE
  FOR CREATE_TYPE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @EventData xml = EVENTDATA();

  DECLARE
    @sch sysname = @EventData.value(N'(/EVENT_INSTANCE/SchemaName)[1]', N'nvarchar(128)'),
    @obj sysname = @EventData.value(N'(/EVENT_INSTANCE/ObjectName)[1]', N'nvarchar(128)'),
    @s nvarchar(max)
                 = @EventData.value(N'(/EVENT_INSTANCE/TSQLCommand)[1]', N'nvarchar(max)');

  IF EXISTS (SELECT 1 FROM sys.table_types
    WHERE name = @obj AND [schema_id] = SCHEMA_ID(@sch))
  BEGIN
    INSERT dbo.TableTypeCreationEvents(EventDDL,SchemaName,ObjectName)
      SELECT @s,@sch,@tab;
  END
END
GO

Now, some time has passed, and you want the script for the most recent version of the type dbo.DistCritGroupData? No problem:

SELECT TOP (1) EventDDL
  FROM dbo.TableTypeCreationEvents
  WHERE [schema_id] = SCHEMA_ID(N'dbo')
    AND name = N'DistCritGroupData'
  ORDER BY EventDate DESC;

This will return exactly the same script that you ran when you created the type, and since there is no ALTER TYPE, you don't have to worry about catching a table type up to any changes that have been made since its creation.


Error 29527: While upgrading from SP1 to SP4 in SQL Server 2005 We are trying to upgrade from SP1 to SP2 in SQL Server 2005 test box for a named instance.

Initially we got the error

  MSP Error: 1260  Windows cannot open this program because it has been prevented by a software restriction policy" and got it fixed by following the solution from forum https://social.technet.microsoft.com/Forums/windowsserver/en-US/10ea9bc2-db84-4c86-9d5e-b442e4fd9600/cannot-install-sql-server-2005-service-pack-2-unable-to-install-windows-installer-msi-file?forum=winserverGP

But now when we are trying to execute the SP2 exe again we are getting the below error from summary file.

and this is the hotfix error log:

On this box we already have one default instance running SQL Server 2005 SP4.

I tried searching on this error but no luck as such.

Any suggestions how can we proceed for this standard 64 bit VM box. thanks!

As per logs you posted

Products Detected                         Language  Level  Patch Level       Platform  Edition
Database Services (ALCSQLMFG)             ENU       SP1    2005.090.2047.00  x64       STANDARD
Database Services (MSSQLSERVER)           ENU       SP4    2005.090.5069.00  x86       STANDARD
Reporting Services (MSSQLSERVER)          ENU       SP4       9.00.5069.00  x86       STANDARD
Notification Services                     ENU       SP4       9.00.5069.00  x86       STANDARD
Integration Services                      ENU       SP4       9.00.5069.00  x86       STANDARD
SQL Server Native Client                  ENU                 9.00.2047.00  x64
Client Components                         ENU       SP4           9.4.5069  x86       STANDARD
SQLXML4                                   ENU                 9.00.5000.00  x64
Backward Compatibility                    ENU                    8.05.2312  x64
Microsoft SQL Server VSS Writer           ENU                  10.3.5500.0  x64

If you see it you have two instances installed

ALCSQMFG instance is at patch level 2047 and is 64 bit standard edition
MSSQLSERVER instance is at patch level 5069 and is 32 bit Standard edition.

Further if you go down the logs you can see message

Database Services (MSSQLSERVER)           Setup cannot update Database Services x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.
Reporting Services (MSSQLSERVER)          Setup cannot update Reporting Services x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.
Notification Services                     Setup cannot update Notification Services x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.
Integration Services                      Setup cannot update Integration Services x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.
Client Components                         Setup cannot update Client Components x86 on this computer. To proceed, run the Service Pack Setup program for the x86 platform instead.

Now the message is clear you are trying to update MSSQLSERVER which is already at SP4 level and is 32 bit. You are using 64 bit installable to update 32 bit to SP4 which is already at SP4. Seems like you are confused with instances.

What you have to do is

You need to update instance ALCSQMFG which is at SP1 level to SP4 and since because this instance is 64 bit you need to run 64 bit installable. What mistake you are doing is you are running 64 bit installable but you are selecting wrong instance you have to select ALCSQMFG instance and then proceed with upgrade


Recovering a partial PostgreSQL archive I have $PGDATA/base but nothing else.  It was taken from an installation after the postgreSQL service was shutdown, so is clean.  Is there any way to recreate the other elements in $PGDATA that allow it to be used again - pg_tblspc/, global/ ?

FWIW I am running 8.1.23.

Thanks

You've left out so much that your chances of recovering useful data are negligible.

pg_clog contains the commit/rollback logs. Without these, the system doesn't know which parts of the database files are valid and which are not. (gross oversimplification, but hey).
pg_xlog, the write-ahead logs. Without these, the database can't handle incomplete writes, so it'll see the database files in an incomplete state that might be damaged. Indexes may be corrupt, heap pages might be partially written, etc.
global/pg_control, the file that contains the system identifier, transaction wrap-around value, transaction log checkpoint position, and a whole lot more.

... and more.

It is vital to follow the instructions in the documentation on how to back up a database. Failure to follow the instructions will result in a useless, unrecoverable backup. Testing backups is also vital.

With in-depth analysis and a hacked version of the postgres server binary that was modified for data recovery I expect it might be possible to recover a corrupted version of your data, with duplicate entries in primary keys, broken foreign keys, multiple copies of data that was updated, reappearing deleted rows, etc. This would require some in-depth knowledge of the PostgreSQL server and take some significant time - so you'd be looking at a non-trivial cost and you'd only get back a mangled copy of your data.


Changing the Owner of Replication Jobs Just wondering if there is any gotchas with changing the owner of a sql server replication job.  The job owner is currently a domain account, but that person is leaving the company, so the account is soon to be disabled.  I am planning on setting the owner to SA, but wanted to check about the replication jobs first.

Changing the owner of a replication job will not impact replication.


Oracle 11g stuck at installation in oracle linux no error msg only showing | and stuck oracle not install on centos7 while installation it is stuck at 84% no error msg showing

It is very common for the 11.2 installer to throw an error on OL7/RHEL7 at the phase where the agent is being built. Based on your screenshot, that is where your installer was (Building Agent Libraries), but it was unable to display the dialog window for it. This can be fixed by editing $ORACLE_HOME/sysman/lib/ins_emagent.mk, and replacing:

$(MK_EMAGENT_NMECTL)

with:

$(MK_EMAGENT_NMECTL) -lnnz11

and finally clicking Retry. But the installer is unable to display the dialog on your system.

Operating System Requirements

CentOS is not a supported Linux distribution.
Oracle Linux and Red Hat Enterprise Linux 7 are certified starting with 11.2.0.4.

If you have access only to database version 11.2.0.1, then use Oracle Linux 6 or Red hat Enterprise Linux 6.


Can a large number of schemas be an issue? Can a large number of schemas present a problem in some ways (performance, maintenance)?
By large I mean a few thousands, and schemas will mostly contain views, fetching data from shared tables.

Since SQL Server can handle up to 2,147,483,647 objects in a database, I suspect that having thousands of schemas will only incrementally cause slowness. (Just like anything else, the more different things you have the more processing you need.)

I imagine that your biggest problem will be remaining organized with thousands of schemas to manage.  Depending on what you are trying to accomplish, there may be other approaches.  For example, row and cell level security.

You might have seen this before, but here is a 2005 paper on implementing row and cell security.  http://technet.microsoft.com/library/Cc966395

I have done row/cell security without implementing everything in this paper.  So, if that looks better to you, then be judicious in choosing the level of complexity.


Changing Database Owner Removed User Mapping I ran an ALTER AUTHORIZATION to change a database owner from an AD account to sa and it seemed to cause the user mapping for a specific database to be removed. I’m not sure why this happened.

Is this normal and expected behaviour?

The database owner is mapped to the dbo user in the database, so when you change the database owner, you change which login is mapped to the dbo user.

The previous database owner may loose access to the database.

eg

use master
drop database mydatabase
go
create database mydatabase
go
use mydatabase
go
select sid
from sys.database_principals
where principal_id = 1
go
alter authorization on database::mydatabase to sa
go
select sid
from sys.database_principals
where principal_id = 1

outputs

sid
-------------------
0x0105000000000005. . .

(1 row affected)

sid
-------------------
0x01

(1 row affected)


SQL Server Maintenance Plan Fails to Rebuild the Indexes When I run a maintenance plan to reorganize and rebuild tables indexes, it fails.
Below the last two rows that displays the fail:

 Source: Reorganize Index
 Executing query "ALTER INDEX [PK_xxTableName] O...".: 15% com...
 The package execution fa...
 The step failed.

What would be the cause?

Well, I  have found the answer to this.

I removed the tick from "Compact Large Objects" option and just included the re-organize and re-build indexes tasks and it succeeded!!! Also I will log to text file for future failure details.


Update same field from multiple transaction I have a service which makes some manipulations in DB, everything is working normally until I tried to make two requests in parallel.

Below see the sql debug log:

Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): START TRANSACTION;
Executing (f49986af-9741-4f20-94ba-0644fbafea05): START TRANSACTION;
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
Executing (f49986af-9741-4f20-94ba-0644fbafea05): INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1
Executing (f49986af-9741-4f20-94ba-0644fbafea05): UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): COMMIT;
Executing (f49986af-9741-4f20-94ba-0644fbafea05): ROLLBACK;

Deadlock found when trying to get lock; try restarting transaction

I don't understand why a transaction does not wait for another to end, but a Deadlock exception is thrown?

Thanks.

Databases are designed to support multiple concurrent users.  "Waiting for the other transaction to finish" is mutually exclusive with that requirement.  The ability to support multiple concurrent users is why you use a Database instead of flat files.

You must design your algorithm accordingly.

In your example, you want to serialize this process.  (note:  Serialization is the same as setting the configuration parameter SLOW=TRUE)

You can serialize this process by putting a Row Lock on Table_2 first.  In MySQL (and other RDBMS), you do that by using the SELECT ... FOR UPDATE syntax at the beginning of the transaction.

The process should look like this:

START TRANSACTION;
select count from test_2 where id=1 for update;
INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1;
commit;


Aborted connection (Got an error reading communication packets) MySQL on Amazon RDS I'm getting the following warning in MySQL:

  Aborted connection (Got an error reading communication packets)

I'm using Amazon RDS for MySQL hosting. Both MyISAM and InnoDB storage engines are used (I know it's a bit weird, but it's legacy code). The database is used as storage for PHP application. MySQL version is 5.5.27. PHP connects to MySQL using old mysql extension (http://www.php.net/manual/en/ref.mysql.php)

What I've tried:

Increase max_allowed_packet to 1G (as suggested on MySQL Error Reading Communication Packets)
Tried to change innodb_log_file_size and innodb_log_buffer_size (as suggested on MySQL: Error reading communication packets), but unfortunately Amazon RDS doesn't allow to change innodb_log_file_size, and when I tried to change innodb_log_buffer_size to the maximum possible value (4294967295), database instance didn't boot because of incompatible settings.
PHP application that uses this database inserts data on every request, and I thought it might cause an issue, so I removed INSERT statements that run on every request, still the same result.
It turned out that PHP application creates new MySQL connection for each query it executes. I've changed that so now it uses the same connection for all queries.
Moved my Amazon RDS instance to the same availability zone where EC2 instance with PHP application is located.
Upgraded my database instance from db.m1.small to db.m1.medium.
Changed max_connections setting from 128 to 256.

All these steps didn't help me to solve the problem.

Also, I noticed that this error always happens every 15 or every 30 minutes. From error log:

140528 16:30:00 [Warning] Aborted connection 8709 to db: 'multisite5' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:30:00 [Warning] Aborted connection 8706 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:30:00 [Warning] Aborted connection 8708 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:45:00 [Warning] Aborted connection 11675 to db: 'multisite5' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:45:00 [Warning] Aborted connection 11674 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

I was under impression that the error is triggered by some cron script. I disabled all cron scripts to test the theory, but the error still happens.

Perhaps it's not possible to solve this problem on the server side because of RDS limitations, but I wonder which type of SQL queries can trigger this error? For example I've read that someone might want to increase max_allowed_packet if SQL queries use large BLOB fields, or when you insert many rows in one statement. So the question is - what can I change to make MySQL server happy with current values of innodb_log_file_size and innodb_log_buffer_size? Perhaps it's slow queries, or too many concurrent connections, or something else? Unfortunately moving my database from RDS to EC2 is the last option I would choose, because as I said I'm not experienced in database tuning so I'll have hard time trying to setup backups, replication etc.

Please let me know if I need to provide some additional details, I'm not database expert so I can miss something important.

Thanks in advance!

Since you tried the above steps, your sticking point seems to be the one you can't alter.

I wrote a post back in August 2012 (Local database vs Amazon RDS), mentioning how the number of DB connections and the Buffer Size are fixed and immutable for each in RDS server model. I also mentioned that the Transactions Logs are fixed at 128M, no matter what RDS server model you use.

SUGGESTION: Be kind to yourself. Migrate the DB from RDS to EC2. Then, you have the necessary flexibility to change the InnoDB Infrastructure. Consequently, you will have to write your own backups and setup replication by hand.

I know you said going to EC2 is your last resort. Since you cannot tune InnoDB effectively in an RDS environment, EC2 is the next logical step.


Performance Impact between databases I have a database with a fairly large table. We want to run grafana queries against this data, and are thinking about dumping the data each day into a separate table/database to minimize performance impact on our main DB.  Ideally, we would have some daily replication dump data as such:

database1.table --> database2.table_hist

So my question is, would it be better to create a new schema (on the same server) in order to further segregate the datasets? Would we see performance impact on database1 if we're running long queries against database2.table_hist?

The same MySQL process will use the same CPU cores to process and move data between the same disk and the same memory, whether your reporting table is in the same MySQL database (schema) or in a different schema. Even if you have a separate MySQL instance on that machine, it will still compete with its sibling for hardware resources. For proper isolation you need a separate server.

A typical scenario includes a master-slave replicated pair, with reporting workload hitting the slave.


Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For example:

SELECT CAST(GETDATE() AS VARCHAR)
-- May  8 2015  9:13AM

Is there a way to change the default style used? For example, I would like to use style 126 so that the results look more like this:

SELECT CAST(GETDATE() AS VARCHAR)
-- 2015-05-08T09:14:19.437

If possible, I would like to only change the default style for the current process or statement; however, I am also willing to change it at the database or schema level.

I should note: I am aware of CONVERT and the ability to manually specify the style. However, in this particular situation, I am unable to use CONVERT. I am CASTing a SQL_VARIANT parameter to a scalar-valued function as VARCHAR.

CAST simply doesn't support the ability to do anything other than a generic conversion, without any flexibility other than inherent ones (e.g. language or dateformat settings, which have to do with how a date string is interpreted, not how much precision it has). Generic conversions of datetime -> string loses precision, regardless of the method. None of these will have seconds:

PRINT GETDATE();
SELECT CAST(GETDATE() AS VARCHAR(23));
SELECT CONVERT(VARCHAR(23), GETDATE());

The answer is to use CONVERT with an appropriate length and a style number (and there is no reason to use VARCHAR for this conversion in isolation).

SELECT CONVERT(CHAR(23), GETDATE(), 126);

Sorry, but there is no server-, database-, or schema-level setting that will make this happen automatically (or change the way CAST works).

Regarding the specific problem you think you have (you can't use CONVERT because of errors with SQL_VARIANT arguments), you can cast as DATETIME within a CASE expression to avoid getting errors on direct conversion of non-convertible values. Either of these forms will work:

DECLARE @x TABLE(y SQL_VARIANT);
INSERT @x(y) VALUES(GETDATE());
INSERT @x(y) VALUES('foo');

SELECT CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1
  THEN CONVERT(CHAR(23), CONVERT(DATETIME, y), 126)
  ELSE y END
  FROM @x;

SELECT CONVERT(VARCHAR(4000),
  CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1
  THEN CONVERT(DATETIME, y) ELSE y END, 126)
  FROM @x;

And before you say that must be inefficient, you're using SQL_VARIANT and scalar user-defined functions. These extra converts of singleton values will be a mere drop in the bucket.

A disclaimer: you could still end up with errors using this approach, since the ISDATE() test is only checking the first 23 characters of what could be a valid datetime string concatenated with garbage. For example:

INSERT @x(y) VALUES('2000-01-01T00:00:00.000 garbage');

Using the same code as above will lead to:

  Msg 241, Level 16, State 1Conversion failed when converting date and/or time from character string.

So a slightly safer approach would be that suggested by Paul below, using the metadata that SQL_VARIANT stores to ensure you only try to convert data with a date/time base type. This allows you to ignore string lengths etc. as well (except for on output).

DECLARE @x TABLE(y SQL_VARIANT);
INSERT @x(y) VALUES(GETDATE());
INSERT @x(y) VALUES('foo');
INSERT @x(y) VALUES('2000-01-01T00:00:00.000 garbage');

SELECT CASE
  WHEN CONVERT(SYSNAME, SQL_VARIANT_PROPERTY(y, 'BaseType')) LIKE N'%date%'
  THEN CONVERT(CHAR(23), CONVERT(DATETIME, y), 126)
  ELSE y END
  FROM @x;

In the future, rather than just vaguely saying "I am unable to use CONVERT," show us what you're actually doing and why you actually think you can't do something. Some pretty smart folks here that can grok and analyze without having to pull teeth or make assumptions.


How can I disable the @ symbol in SQL scripts for sqlplus? In Oracle's sqlplus tool, when the script contains an @ symbol, it loads an SQL script with that name and executes it. I'm inserting a VARCHAR value which contains some CSS code, so when it encounters a @media query or @keyframes it tries to execute media.sql, which is not the desired effect. This does not happen when I execute the script in the Oracle SQL Developer window.

A small proof-of-concept script:

SET DEFINE OFF
SET SQLBLANKLINES ON
SET SQLTERMINATOR OFF
SET SQLPREFIX '%'
SET SCAN OFF

DECLARE
    loginId NUMBER;
    ncount NUMBER;
    css VARCHAR2(32767) DEFAULT '
@media only screen and (min-width: 980px) {
    .footer-column-copy {
        margin: 0;
        padding-top: 0;
        padding-bottom: 80px;
        background-size: 55px;
        background-position: top right;
        float: left;
        width: 260px;
    }
}
';

This triggers the following error:

$ echo @poc.sql | sqlplus username/password@localhost

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 17 08:53:16 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SP2-0310: unable to open file "LOGIN.SQL"
SQL> SP2-0310: unable to open file "media.sql"
 16
SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

In the same way that SET SQLPREFIX can disable the parsing of the # symbol, how can I disable the parsing of the @ symbol (or change it to something else like the SET SQLPREFIX command does)?

The problem really only occurs because you placed the @media directive at the beginning of a new line. If you change your code like this:

css VARCHAR2(32767) DEFAULT '@media only screen and (min-width: 980px) {

or like this:

css VARCHAR2(32767) DEFAULT
'@media only screen and (min-width: 980px) {

then it works fine.

I know it does not answer your specific question about ignoring the @ character, but it does solve your problem ...

More generally, all you need is to avoid having any @xxxx at the beginning of a line you pass to sqlplus. Just place it at the back of the previous line.

Note that just adding white space at the beginning of the line won't work.

Another possibility is to add a comment in front of each @xxxx clause, no matter where it is located. Something like this:

css VARCHAR2(32767) DEFAULT '
/* */ @media only screen and (min-width: 980px) {

sqlplus will handle that as regular text. And your CSS parser should ignore it as a comment.


Upgrade from MariaDB 10.3 to MariaDB 10.4 with Galera Cluster - mysql.global_priv table I have 3-node MariaDB 10.3 Galera Cluster (Ubuntu 14.04) and I want to upgrade it to MariaDB 10.4 Galera Cluster (Ubuntu 18.04).

According to https://mariadb.com/kb/en/library/upgrading-from-mariadb-103-to-mariadb-104-with-galera-cluster/ rolling upgrade is possible (obviously, I need as little downtime as possible).

I did rolling upgrade on dev cluster - I added new server (MariaDB 10.4) to cluster and removed one old server (MariaDB 10.3). Repeated until whole cluster was running MariaDB 10.4. Everything works and looks just fine.

The thing is, in MariaDB 10.4 new authentication mysql table was introduced. According to https://mariadb.com/kb/en/library/authentication-from-mariadb-104/ users should now be stored in mysql.global_priv. Because I did rolling upgrade, myql database was replicated from old nodes to new nodes, so now I dont have mysql.global_priv table in cluster. Users are still in mysql.users table.

Another issue is, when I did new standalone installation of MariaDB 10.4, there was OS admin user mysql@localhost with these privileges

GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION

This user is not present in upgraded cluster (obviously same issue, mysql table was replicated from old nodes), but I cannot create this admin user to get rid of debian-sys-maint@localhost

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION;
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

So my question is, how can I make clean rolling upgrade of MariaDB Galera Cluster from MariaDB 10.3. to MariaDB 10.4 with all new features introduces in MariaDB 10.4? Any help would be appreciated.

Need to run mysql_upgrade in any upgrade.


Bulk Update Based Off Values In A Table In Sql Server 2014 I need to run an Update statement that will update NULL values with values found previously in the table.  To better illustrate, here is a very small subset of my data

Create Table #Booberry
(
  id int
  ,customer varchar(500)
  ,os varchar(500)
  ,ms varchar(500)
  ,st varchar (500)
)

Insert Into #Booberry Values
('1', 'Two', 'Win10', 'M1', 'Stop')
,('2', 'Four', 'Step', 'T23', 'Go')
,('3', 'Two', NULL, NULL, NULL)

(again very small subset for illustration purposes only).  What I am needing to accomplish is for each row where OS is null, scan the table and determine if that customer has a previous value.  I think, how this would take place to update the missing data for customer Two would be something like:

MAX([os]) As MaxOS = Win10
MAX([MS]) As MaxMS = M1
MAX([ST]) As MaxST = Stop

Find the MAX() value for the data points to update then use those in an actual update statement.  Meaning that any other customer in my table with a value of Two should have the fields [os],[ms],[st] equal the values above.

How would I write an update query to accomplish this?

If further explanation is needed, I will be happy to provide.

If you are confident that there can only ever be at most two rows for any given customer, then you can do this:

;WITH x AS
(
 SELECT id,customer,os,ms,st,
  previous_os = LAG(os,1,NULL) OVER (PARTITION BY customer ORDER BY id),
  previous_ms = LAG(ms,1,NULL) OVER (PARTITION BY customer ORDER BY id),
  previous_st = LAG(st,1,NULL) OVER (PARTITION BY customer ORDER BY id)
 FROM #Booberry
)
UPDATE x SET
  os = COALESCE(os,previous_os),
  ms = COALESCE(ms,previous_ms),
  st = COALESCE(st,previous_st);

But I don't know how you can be so sure. This won't work very well if you have a case where there are three rows for a customer, the most recent row is all NULLs, the one before that has NULL for st, and the one before that has NULL for os. I think we need better requirements and how you want the results to look in the event of edge cases.


Source Database of a restore stuck Restoring I have copied a database vn_Budget_2021_BofDOct29 to the database vn_Budget_2021_Rig2. I did this through Tasks->Restore Database shown in the second image.
I saw this similar issue except the database I've restored isn't the one hanging. Its my source DB that doesn't require any restoring in iteslf.
SQL server databases stuck in restoring state
How do I get it stop hanging? I don't need to restore this database to the same name. The last backup is a few days old and theres nothing wrong with the data.

Method of restoring. No logs were taken which may be the issue?

This can happen when you don't uncheck in the GUI to take a tail-log backup.
It is a nasty behavior by the GUI. You type a new database name in the restore GUI, the one to be created by the RESTORE command. But the GUI still does a tail-log backup of the source database, and it does it by default using the NORECOVERY option (which s precicely what puts the source db in restoring state).
The image you posted doesn't reflect this, so I can deduce that it wasn't the actual screen from your restore. You would have a note in the upper left of the dialog.
So, in the future, go to the Options page and uncheck the tail log backup when you restore into a new database name.
To handle the immediate situation, you just execute below (obviously substituting the database name):
RESTORE DATABASE dbname WITH RECOVERY

No backup files needed.


Indexed view - time of update A 3rd part app is using 'our' database views. Basically flow looks like this:

New Row is inserted to Results table;
Insert new row to Event table (with information about new result).

The 3rd part is saying that sometimes they receive an event (also indexed view) but when they check indexed view for result it returns null. Asked programmers if that situation is possible from code side, they said it is not possible because the program wait with inserting row to Event table until result is finally saved (committing transaction).

From timestamps I see it takes ms from Result to Event. Like Result is saved at .320 and Event is saved at .350.

So my question is: Is it possible that it take so much time for indexed views to be actualized or maybe they are being actualized during insert statement?

No, an indexed view is maintained immediately as the base table is changed - there is no delay possible (unless the reader is using nolock and the original transaction hasn't committed yet). There must be something else going on, but you'll need to provide more details than "they said."


Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error we are using a Sybase SQL Anywhere 12 db.

In the db there are indices, which are unique, but shouldn't be unique.

Therefore I search for a quick way to list all tables with unique primary keys.

I tried

SELECT z.name
FROM   sysobjects z
JOIN   sysindexes
ON     (z.id = i.id) WHERE type = 'U'

The result was an error message:

  Anweisung konnte nicht ausgeführt werden.
  Tabellenname 'sysindexes' ist mehrdeutig
  SQLCODE=-852, ODBC 3-Status="42S13"
  Zeile 1, Spalte 1

Roughly translated:

  sysindex table name is ambiguous.

I found on internet the query:

select 'Table name' = object_name(id),
       'column_name' = index_col(object_name(id),indid,1),
       'index_description' = convert(varchar(210),
        case when (status & 16)<>0 then 'clustered' else 'nonclustered' end
        + case when (status & 1)<>0 then ', '
        + 'ignore duplicate keys' else '' end
        + case when (status & 2)<>0 then ', '
        + 'unique' else '' end
        + case when (status & 4)<>0 then ', '
        + 'ignore duplicate rows' else '' end
        + case when (status & 64)<>0 then ', '
        + 'statistics' else case when (status & 32)<>0 then ', '
        + 'hypothetical' else '' end end
        + case when (status & 2048)<>0 then ', '
        + 'primary key' else '' end
        + case when (status & 4096)<>0 then ', '
        + 'unique key' else '' end
        + case when (status & 8388608)<>0 then ', '
        + 'auto create' else '' end
        + case when (status & 16777216)<>0 then ', '
        + 'stats no recompute' else '' end),
        'index_name' = name
from    sysindexes
where   (status & 64) = 0
order by id

Which looked what i wanted. But there was still the same result of ambiguous sysindexes.

What dows ambigious indexes mean in this context? Will/Can this cause any error in future?

As workaround I used sybase central (which by the way opens always on first monitor, not on the one where it was closed - ugly behaviour), and found that a item indices showed what i searched for.

But I still want to know how a programmatically solution looks like.

There are two system views called sysindexes - one owned by SYS and one owned by DBO. You likely want the the one owned by SYS:

SELECT z.name FROM sys.sysobjects z JOIN sys.sysindexes ON (z.id = i.id) WHERE type = 'U'


How do I tell which versions support a given DMV column? Each version of SQL Server potentially adds new columns to the DMVs. These new columns are available even if you change the compatibility level of the database to a previous version.

Is there a list somewhere that shows which columns are available for a given version of SQL Server?

Each version of SQL Server potentially adds new columns to the DMVs.

Correct, and since each CU is technically a new version this can happen inside of major version releases.

  Is there a list somewhere that shows which columns are available for a given version of SQL Server?

While this may not be in the spirit of the question asked, there technically is an answer which is to use the system tables. For example, what columns does sys.availability_groups currently have in this version (14.0.3037):

select name FROM sys.all_columns where object_id = object_id('sys.availability_groups') order by column_id asc

Will output:

╔═════════════════════════════════════════════╗
║                    name                     ║
╠═════════════════════════════════════════════╣
║ group_id                                    ║
║ name                                        ║
║ resource_id                                 ║
║ resource_group_id                           ║
║ failure_condition_level                     ║
║ health_check_timeout                        ║
║ automated_backup_preference                 ║
║ automated_backup_preference_desc            ║
║ version                                     ║
║ basic_features                              ║
║ dtc_support                                 ║
║ db_failover                                 ║
║ is_distributed                              ║
║ cluster_type                                ║
║ cluster_type_desc                           ║
║ required_synchronized_secondaries_to_commit ║
║ sequence_number                             ║
╚═════════════════════════════════════════════╝

More in the spirit of the question, Docs is your best source for columns based on version outside of your own queries.


Multiple WHERE IN on multiple columns I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns.

More specifically, i'm doing the following:

SELECT a, b
FROM links
WHERE a IN (90696, 110083, ... 147455)
  OR b IN (90696, 110083, ... 147455)

Since both IN-lists are identical and can contain several hundreds of items, the above query just doesn't feel very right to me.

If it helps (but I don't think so, actually):

the following condition is always met: row[x].a < row[x].b for all rows x in Table links.
Im using MySQL InnoDB. Yet, I'm open to different database solutions if would significantly improve performance.
Currently, the items of the WHERE-IN clause are computed. However, I could also acquire them by an additional query (which would actually be the union of two queries).

Edit:

The IN-list could be constructed by

SELECT i_id as l FROM t_i WHERE id=x
UNION ALL
SELECT o_id as l FROM t_o WHERE id=x

As I understand you want to check each combination of a/b and i_id/o_id

then simple rewrite to join is like this:

SELECT a, b
  FROM links
  JOIN t_i ON (a = i_id OR b = i_id)
  WHERE id = x
UNION
SELECT a, b
  FROM links
  JOIN t_o ON (a = o_id OR b = o_id)
  WHERE id = x

But for good index usage I suggest splitting it once more. It is even longer query but can be really fast even on bigger data with indexes on t_i(id,i_id), t_o(id,o_id) links(a) and links(b)

SELECT a, b
  FROM links
  JOIN t_i ON (a = i_id)
  WHERE id = x
UNION
SELECT a, b
  FROM links
  JOIN t_i ON (b = i_id)
  WHERE id = x
UNION
SELECT a, b
  FROM links
  JOIN t_o ON (a = o_id)
  WHERE id = x
UNION
SELECT a, b
  FROM links
  JOIN t_o ON (b = o_id)
  WHERE id = x


Why slower using multiple mongos? When I use a single mongos like 1.1.1.1:30000, the driver will return in 20ms to find a batch of documents.

When I use 1.1.1.1:30000,1.1.1.2:30000,1.1.1.3:30000,it costs 60ms,three times more to return.(to find the same batch)

And when I use 5 mongos instances, 5 times more.

It's better to use mongos locally, perhaps. But why?

Sorry for my nescience about mongo. What I know is that mongo client is always described some sort of like this

  mongoc_client_t is an opaque type that provides access to a MongoDB server, replica set or sharded cluster.

Which can't explain what happened and why mongo driver costs more time as using more mongos node in uri like this:

mongocxx::client client_;
client_.reset(new mongocxx::client{mongocxx::uri{"1.1.1.1:30000,1.1.1.2:30000,1.1.1.3:30000"}});
...
mongocxx::read_preference read_preference;
read_preference.mode(mongocxx::read_preference::read_mode::k_secondary_preferred);
find_options.read_preference(read_preference);
auto collection = (*client_)["db"]["col"];
auto cursor = collection.find(filter_builder.view(), find_options);
...

PS:
  All mongos refer to the same cluster.

I'm now trying to debug the latest driver.

My drivers in this case:

mongo-c-driver:
  #define MONGOC_VERSION_S "1.5.1"

mongo-cxx-driver:
  #define MONGOCXX_VERSION_STRING "3.1.1"

network & latency time:

1.1.1.1,1.1.1.2,1.1.1.3 all in the same room

latency:
  ping localhost 0.03ms
  ping 1.1.1.2(from 1.1.1.1) 0.2ms

batch size:
  about 100
  find{"uid":{"$in":[1,2,3...,100]}} uid is an ensured index.

I have been troubled in this problem so long,and sorry to say that this does not recur all the time.

First, according to official documents, connections are usually established and authorized in the initiation.Then I traced the program to ensure it. Only one random server id is needed in an query, if no error.So liner ascending latency shall never happen as server number increase.

I wrote a test this week, and the result is that no matter how many mongos server are listed in the uri, the latency of the query are randomly distributed in between 3ms to 30ms. Extreme cases happened when network got worse.

This is totally different to the case when I asked this question.So I have to blame it on these changes:

1. More mongos server are available now ,and queries are separated.
2. Most clients are using single mongos server now, so less tcp connections exist and network is better.
3. We improved our program and queries from clients are less and lighter.

Actually , except from the liner ascending latency, some queries returned in above 16 second two weeks ago.

I guess that liner increased latency is just a form of performance degradation.


String manipulation in Recursive CTE I am trying to make something like the following work:
WITH results(n, string ) AS (
            SELECT 1,'lol'
            UNION ALL
            SELECT n+1, string + ' lol'
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

But SQL doesn't seem to recognize the string concatenation in the second column and returns the error:

Types don't match between the anchor and the recursive part in column "string" of recursive query "results".

My desired output would be something like
1, lol
2, lol lol
3, lol lol lol
and so on

You can cast it to varchar(2000) or varchar(max) depending on your needs
so both should have the same data type and size
for value 1, it using an integer/int data type
From BOL related to CTE ,

The data type of a column in the recursive member must be the same as    the data type of the corresponding column in the anchor member.

WITH results(n, string ) AS (
            SELECT 1,CAST('lol' as varchar(2000))
            UNION ALL
            SELECT n+1, CAST(string + ' lol' as varchar(2000))
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

you can see the data type by using SQL_VARIANT_PROPERTY
WITH results(n, string, expressionType1, expressionType2) AS (
            SELECT 1,cast('lol' as varchar(2000)),
                    SQL_VARIANT_PROPERTY(1, 'BaseType'),
                    SQL_VARIANT_PROPERTY(cast('lol' as varchar(2000)), 'MaxLength')
            UNION ALL
            SELECT n+1, cast(string + ' lol' as varchar(2000)) ,
                SQL_VARIANT_PROPERTY(n+1, 'BaseType'),
                SQL_VARIANT_PROPERTY(cast(string + ' lol' as varchar(2000)), 'MaxLength')
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

output:
n           string               expressionType1 expressionType2
1           lol                  int             2000
2           lol lol              int             2000
3           lol lol lol          int             2000
4           lol lol lol lol      int             2000
5           lol lol lol lol lol  int             2000
6           lol lol lol lol lol  int             2000

Data type for value/expression 1 can be checked by:
select SQL_VARIANT_PROPERTY(1,'basetype')
int


quickly jump to an object in Object Explorer in SSMS When expanding a tree with a large number of objects such as the Tables tree in SSMS Object Explorer, is there a method of quickly navigating to the table I am looking for, if I know the exact name or the first few letters of the name? I know that by typing the table name, the explorer will navigate to the respective table, but with table names like dbo.Table1, I want to avoid typing the 'dbo' portion of the name and just start typing Table1. I am not looking to use Filtering as I find this is cumbersome when navigating dozens of times throughout the day. I am using SMSS V11.0.3128.0.

Try using the Object Explorer Details window.

It's the second option under the View menu or F7 to open it.  Once there you can just select the category in Object Explorer you want to look at (tables for example) and then in the OE Details window you can just type the name of the table.  As an added bonus you can sort by name, schema, create date and a number of other really useful columns.


What are version vectors? Folks, I am currently learning about distributed data systems via the book "Designing Data-Intensive Applications".
I think I have a pretty strong understanding about how version numbers in a single replica system allow the server to detect concurrent writes*. The author starts with this example because once you understand the single replica system, expanding that understanding to a multi-leader or leaderless replicated system is supposed to be obvious, but it is not obvious to me at all.
How do version number in a system where multiple replicas can handle write requests work? In other words, what are version vectors?
* In a single replica system, each write is accompanied by a version number. This version number is the version of the data that the write is based off of. If a write is based on Version 1 of the data for that key, and Version 2 already exists, we know that the incoming write is concurrent with Version 2. The incoming write can only overwrite data that was in Version 1, since it does not know about the data in Version 2. For example, Version 1 is [eggs], Version 2 is [eggs] and [milk]. The incoming write wants to update this key to [eggs, bacon]. Version 3 of this key will become [eggs, bacon] and [milk]. The incoming write cannot overwrite [milk] since it didn't even know that [milk] was a value in the key.

Version vectors are a way of each node in a cluster communicating its local version number to all other nodes in a cluster. They are also known as vector clocks.
In essence, when a node A passes a message to another node B, A includes in that message what it knows about the version number of all nodes in the cluster. Because it contains values for all other nodes it is an array, or vector, of version numbers. Node B uses this information to update what it knows about the version numbers across the cluster. It can use this information to work out a global ordering of events from across the whole cluster.
In turn node B includes this updated information in messages it sends to other nodes, including to node A.
Search for vector clock. There are a great many explanations, both academic and practical.


How to modify the precision of a datetimeoffset column? In a sql-server database with data I have several columns with type datetimeoffset(7). Now I would like to change the precision part of the type to datetimeoffset(0).

Is this possible without creating a new column of the desired type/precision and renaming the column?

I take solutions using SQL Server Management Studio as well as code (or also a no it's not possible).

This should be a straightforward

alter table tab
alter column x datetimeoffset(0);