|
|
|
|
|
|
|
Question of the Day |
Today's question (by Thomas Franz): | |
Database containment and collation | |
Lets start with a simple, new database with Latin1_General_CI_AS_KS as collation:
USE [master] go CREATE DATABASE [test_cont] CONTAINMENT = NONE ON PRIMARY ( NAME = N'test_cont', FILENAME = N'c:temptest_cont.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'test_cont_log', FILENAME = N'c:temptest_cont_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO ALTER DATABASE [test_cont] COLLATE Latin1_General_CI_AS_KSWhen we run the following query, we see, that the collation of the name column in the view sys.objects is Latin1_General_CI_AS_KS (as expected) and Latin1_General_CI_AS_KS_WS for type / type_desc. USE test_cont GO SELECT c.name, c.collation_name FROM sys.all_objects AS ao INNER JOIN sys.all_columns AS c ON c.object_id = ao.object_id -- will be -385 per default for sys.objects WHERE ao.name = 'objects' AND ao.schema_id = SCHEMA_ID('sys') AND c.collation_name IS NOT NULL; GONow we change the database to partially containment (allowing us to create user, that exists only in this database but not as login on the server level): -- don't do it on production without exactly knowing, what you do!!! EXEC sys.sp_configure @configname = 'contained database authentication', @configvalue = 1; RECONFIGURE ALTER DATABASE [test_cont] SET CONTAINMENT = NONE WITH NO_WAITQuestion: What happens, when we execute this code? USE test_cont GO SELECT c.name, c.collation_name FROM sys.all_objects AS ao INNER JOIN sys.all_columns AS c ON c.object_id = ao.object_id -- will be -385 per default for sys.objects WHERE ao.name = 'objects' AND ao.schema_id = SCHEMA_ID('sys') AND c.collation_name IS NOT NULL; GOPS: tested on SQL 2016 SP2 Enterprise | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
Ordering by Alias I have a table with some order data that I need to build a query for. I gave a sample data set that looks like this: INSERT dbo.SalesHeader ( OrderKey, CustomerID, ShipTo, OrderDate, OrderTotal, StatusKey) VALUES ( 1, 1, 'Office', '2017-09-01', 500, 0 ), ( 2, 1, 'Home', '2016-07-05', 500, 0 ), ( 3, 2, 'Office', '2018-11-06', 500, 0 ), ( 4, 3, 'Office', '2019-04-07', 500, 0 ), ( 5, 3, 'Office', '2018-06-08', 500, 0 ), ( 6, 3, 'Office', '2015-02-09', 500, 0 ), ( 7, 4, 'Home', '2014-12-11', 500, 0 ) GO I query this table like this to test things: SELECT OrderKey, CustomerID, OrderDate = CONVERT(VARCHAR(10), sh.OrderDate, 101), sh.OrderTotal FROM dbo.SalesHeader AS sh ORDER BY OrderDate; Which OrderKey is returned first? Answer: 6 Explanation: Orderkey 6 is returned first as the conversion to varchar() will format this in a dd/mm/yyyy format. The ORDER BY clause will refer to the formatted data, not the column value, as it is not qualified. Ref:
|
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
Managing Mirroring failover in a WSFC cluster - Hi all, hope you can help. I'm not a DBA so have been googling as I go, so forgive my idiocy: I've got a SQL server standard instance with a number of databases. The setup is as follows: Servers 1 and 2 are in a SQL cluster with shared storage. Server 3 is a standalone […] |
SQL profiler - Is SQL profile being deprecated and extended events should be used instead? Also, has anyone heard that there is more of a concern around Statistics rather than fragmentation ? |
Extended Events- how to log to an SQL table? - I want to enable an Extended events session. However, I don't want it to go to a file on the HDD, I 'd really like to send it all to an SQL table so I can do grouping, analysis, reporting, etc... How can I get that data into an SQL table for longer term storage […] |
SQL Server 2017 - Development |
Most frequently used text in column - I would like to update a field (yearclass) for each row with the most common text in payclass for each grower, block, section and year combination. In the case where there are equal amounts of payclass, weight should be used to determine yearclass. Highest sum of weight wins. I have tried many queries of which […] |
Insert into OPENDATASOURCE with Microsoft.ACE.OLEDB.16.0 provider - failed - Hi all, we export data into Excel file from SQL Server 2017 with a simple command (a part of a stored procedure): The command looks like: INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0', 'Excel8.0;HDR=YES;IMEX=1;Database=\\some_folder\excel1.xls')...Sheet1 SELECT * FROM table1 It works fine. But only if just one export is processing at the same time. If more exports are […] |
Removing a double space in the middle of a name - Suppose I had a list of names and one of the names was Kathy Harrison. Notice the double space after the first name. In Excel there is a function called TRIM which could remove extraneous spaces. I am trying to find something like that in SQL. As far as I know, in SQL, TRIM is […] |
SQL Server 2016 - Administration |
SQL Server security after deleting AD group - I currently have a scenario where an AD group that was defined as a SQL login with particular permissions got deleted from AD but not from SQL. So: domaina\groupb - no longer exists as an AD group but still exists as a SQL login It seems that members of the deleted group still have SQL […] |
Very large t-log with backups - I have a database that's 125GB, but the t-log is 190GB. I do full backups each day and t-log backups every 15 minutes. I've verified the log backups have been successful for the past week. Generally when I see a large log like this it is because backups are failing. The log isn't growing out […] |
SQL Server 2016 - Development and T-SQL |
Exporting very large Data to an XML file from SQL Server - I have a query that creates an xml file but I'm getting errors because the XML is too large. I've already set the Results To Grid Property XML Data to unlimited but I'm still getting the error. |
Administration - SQL Server 2014 |
Always on - huge set of inserts and updates - Hi everyone, I need some advice please on how best to do something. We have a database which is 1TB in size, its part of a SQL 2014 Always On AG (synchronise sync). We have to do a very large data change, initial estimates are from testing it will take around 15 hours. Optimisation has […] |
Development - SQL Server 2014 |
NOOB question with Case statement - hi here i am again with a noob question again: i'm trying to do this: case when columnA <> 0 then columnB = 1 else columnB end but it return error. i used case statment before but im the same column, is it possible to use in multiple column with the argument based on one […] |
SQL 2012 - General |
service pack Question - Hi, I updated our SQL ser5ver from Sp2 to SP 4 today, and it says it was a success. However when I run say: I get Sp 2 So did I get the update or not if not what can I do? Thank you SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') |
SQL Server 2012 - T-SQL |
join with GEOMETRY data type - I want to perform something like.... SELECT * FROM a JOIN b ON a.geometryField = b.geometryField OR SELECT * FROM a JOIN b ON a.geometryField. STEquals(b.geometryField ) OR SELECT geometryField FROM a WHERE geometryField IN (SELECT geometryField FROM b ) You get the gist. None of the above work. I know I need […] |
Need help query for current row and previous row - Hye, I've table and data as following, CREATE TABLE [dbo].[WeightStory]( [Idx] [int] IDENTITY(1,1) NOT NULL, [WeightDte] [datetime] NULL, [WhatWeight] [decimal](18, 3) NULL, CONSTRAINT [PK_WeightStory] PRIMARY KEY CLUSTERED ( [Idx] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[WeightStory] […] |
Reporting Services |
Failed Executions - Curious what everyone else is doing... I've always had a daily report showing me report subscriptions that failed. I've had a task to better monitor all report interactions (subscriptions, cache refresh and interactive) sitting in my queue, but I put it off because of other higher priority tasks. Right now I'm in the process of […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |