SSMS "Intellisense" behaviour is driving me demented
This behaviour is doing my head in and I'm hoping that there is a simple way to change it?Just to give you an example, I am starting to write a little query to check the status of FullText Indexes on database objects. This is for illustration purposes only so don't tell me what "better" alternatives there are for doing this, I just want to illustrate the behaviour that's bugging me.So the query I would like to run is:SELECT [SO].[name], [FI].[is_enabled] FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idThe behaviour that annoys me can be illustrated as follows. In SSMS, type this:SELECT * FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idThen go back to the *, remove it and start typing [SO] in order to get intellisense to show you column names you can choose from. So you have typed [SO] and your cursor is right behind the closing bracket:SELECT [SO]<cursor here> FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_id Type the dot The statement changes to:SELECT [SOUNDEX]. FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idI didn't want "soundex". I wanted intellisense to show me a list of column names in sys.objects, aliased to "[SO]" by me. It does that once I hit Ctrl+Z which removes the auto-inserted [SOUNDEX] and then when I hit the dot again it shows me the list of columns. So I pick [name] and start adding the next column by typing , [FI]. And here it goes again:SELECT [SO].[name], [FILE_ID]. FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idI didn't want [FILE_ID]. I wanted [FI]. and a popup showing the the column names in sys.fulltext_indexes I can choose from.Sure, this is one heck of a "first world problem" but as a touch typist this is driving me around the bend. If there's a way to change this behaviour (without losing Intellisense altogether), please tell me how.SSMS Object Explorer doesn't show any of my databases
I'm a very green amateur sysadmin of a SQL Server installation that only I will use. I've gotten myself in trouble, and I hope someone can help me get out of it! I installed an updated version of SQL Server without understanding how a connection's server name determines which version is used. I ended up with the new version installed, but the old version still executing queries. I eventually found a clear explanation of how server names work, and I fixed the problem, but I did a lot of thrashing around first, and now I find that none of my databases are visible to any of the defined server names in SSMS's Object Explorer. The databases are still present in the file system, but I can't get at them. I have never created a user (another concept I initially didn't understand), so every defined server name must be connecting as the default user created by the installer, with role 'sysadmin'. (I have confirmed that the user is a sysadmin with a query.) Sysadmin users are supposed to have access to all databases. I understand that it's not even possible to deny a sysadmin access to a database with REVOKE or DENY, or, I assume, by any similar means. Given that, how could my databases have disappeared from Object Explorer? How can I get them back?
SSMS 22 - Not seeing the Database name information in the Status Bar and Title bar is gone.
I installed SSMS 22 yesterday and am trying to get used to the features. I am not a fan of the new layout. I might be in the future, but at the moment, not so much. Status Bar settings are all set to [True] But nothing is showing in the Status Bar. There is no Title Bar anymore. WHY Would they take away the Title Bar? That is insane. So, my question to the Forum is this. How do we get the Database Name information in the Status Bar? As stated, everything is set to TRUE in settings. Also, will the Status Bar show the title of the Editor file you are working on? (I miss the old design, to be honest. )
How does GitHub Copilot in SSMS 22 handle database context collection before generating a response?
Hello, I am trying to better understand the internal workflow of GitHub Copilot in SSMS 22, especially for database-specific questions. From the product descriptions, it seems that Copilot can use the context of the currently connected database, such as schema, tables, columns, and possibly other metadata, when answering questions or generating T-SQL. However, I could not find clear official documentation about the actual sequence of operations. My main questions are: Before generating a response, does Copilot first collect database context/metadata from the active connection and then send that context to the LLM as grounding information? Or does it first use the LLM to interpret the user’s request, decide what information is needed, and then retrieve database metadata before generating the final answer? In some explanations, I have seen the phrase "Core SQL Copilot Infrastructure", but I cannot find any official documentation for that term. Is this an official component name? If so, what does it specifically refer to in the SSMS Copilot architecture? When Copilot answers schema-related or data-related questions, what information is retrieved automatically from the connected database, and is any SQL executed as part of that process? Is there any official architectural documentation that explains: context collection, prompt grounding, LLM invocation order, and whether query execution can occur before the final response is generated? I am asking because I want to understand the feature from both an architecture and data governance/security perspective. Any clarification from the product team or documentation links would be greatly appreciated. Thank you.Unable to install SQL Server 2022 Express (installer glitch + SSMS error)
Hi, I recently purchased a new Lenovo laptop, and I am trying to install Microsoft SQL Server 2022 Express along with SSMS. SSMS installed successfully, but SQL Server installation fails, and sometimes the installer UI glitches or does not load properly. Because of this, I am getting connection errors in SSMS like "server not found" and "error 40". I am not very familiar with technical troubleshooting. Can someone guide me step-by-step in a simple way to install SQL Server correctly? Thank you.
Feature Proposal: Ability to Exclude a column/subset of Columns in Select.
Summary I would like to propose a new T-SQL feature that allows developers to select all columns from a table while explicitly excluding a small subset of columns. Currently, when a table contains many columns and only one or two need to be omitted, developers are forced to mention every remaining column manually in the "Select" SQL. This leads to verbose queries, reduced maintainability, and a higher chance of mistakes when the schema evolves. Motivation Consider a table with 20 or more columns. Current approach, SELECT EmployeeId, FirstName, LastName, Department, Designation, Email, PhoneNumber, DateOfBirth, Address, City, State, Country, PostalCode, ManagerId, JoiningDate, LastModifiedDate, Status, IsActive, CreatedDate FROM Employees; If the intention is simply to exclude a single sensitive column such as Salary, the query becomes unnecessarily long. A more concise alternative could be: SELECT * FROM Employees EXCLUDE (Salary); The engine would expand * internally and remove the specified columns before execution. Benefits 1. Reduces boilerplate code. 2. Improves readability for wide tables. 3. Makes queries easier to maintain as schemas evolve. 4. Reduces the likelihood of accidentally omitting newly added columns. 5. Makes it simpler to exclude sensitive or internal-use columns from result sets. Expected Behavior Single column exclusion SELECT * FROM Employees EXCLUDE (Salary); Returns all columns except Salary. Multiple column exclusion SELECT * FROM Employees EXCLUDE (Salary, PasswordHash); Returns all columns except Salary and PasswordHash. Suggested Validation Rules 1. Every excluded column must exist in the projected result set. If an excluded column does not exist, compilation should fail with an appropriate error. 2. Duplicate column names in the exclusion list should either: be ignored, or produce a validation error. 3. If the exclusion list removes every projected column, the statement should fail. Example: SELECT * FROM Employees EXCLUDE (Employee, Name, Salary); If these are the only columns in the table, an error could be raised such as: The EXCLUDE clause cannot eliminate all columns from the SELECT list. Returning a zero-column result set would likely be confusing and less useful. Additional Considerations This syntax could also be valuable when selecting from joins, views, or derived tables, where developers frequently want "everything except a few fields." Closing Thoughts I believe this would be a practical quality-of-life enhancement for T-SQL that addresses a common developer pain point while remaining simple to understand and implement. It would reduce repetitive code and improve maintainability without affecting existing queries.
Synchronize SQL database between two servers
Questions (to make right the synchronize SQL database between two servers) 1. Is required to have SQL Enterprise in both servers, for to be able the synchronization? Or will be enough to have SQL Enterprise in server1. And in server2 with only SQL Express, please? 2. Which requirements are required to fulfil or prepare for to be able synchronize SQL database between two servers, please? 3. Which possibilities (when are more possible solutions) have existed for synchronize SQL database (totally awaiting between four servers), please? 4. Exist a manual for reading an synchronization of SQL server - settings and steps? Thanks in advance.SQL Server AG Failover - Automatic Failover
Hello, I am looking for a straight and definitive answer that I was hoping someone could answer for me. I want to trust what Copilot says, but I really need to hear it from Microsoft and I can't find any documentation from Microsoft confirming my question. My Environment: 2 replicas in datacenter 1 1 replica in datacenter 2 All three (3) replicas are set to synchronous-commit mode with automatic failover. I tested the failover manually between all three (3) replicas without issue. When I test the automatic failover - I take down both replicas in datacenter 1 at the same time to simulate a datacenter outage. I look at the replica in datacenter 2 and it is just says (Resolving...) next to the replica name. The replica does not come online and the DB is not moved. When I was searching I couldn't find out why. So I turned to Copilot not solve the issue, but to see if it could point me in the right direction. I tell Copilot my setup and what happened. Copilot responded stating that by design from Microsoft you cannot have more than two (2) replicas set to synchronous-commit mode with automatic failover in a SQL Server AG instance. That if more than two (2) are set for automatic failover. The SQL Server AG will use the first two (2) replicas it sees in its metadata and ignore the rest. Copilot went into detail about why this is designed this way, but the amount of information would make this post longer than it already is. If this is true - then when I took down both replicas in datacenter 1, SQL Server AG only saw those two (2) replicas in datacenter 1 as the available replicas to use for an automatic failover and thus why the replica in datacenter 2 did not come online and the DB not being moved So let's do a test. I brought back up the two (2) replicas in datacenter 1. Then I made a change in the AG proprieties. I set the 2nd replica in datacenter 1 to manual. So 1 replica is set to automatic failover and 1 replica is set to manual failover in datacenter 1. The replica in datacenter 2 is set to automatic failover I then take down both replicas in datacenter 1 again to simulate the "outage" and the replica in datacenter 2 comes online and the DB is moved. So is Copilot right? Can there only be two (2) replicas allowed to have/use automatic failover? I cannot find a definitive answer confirming this. Or is my configuration wrong/missing something and if it is, could you please point me in the right direction on how to get this resolved?SQL Server 2005 (compatibility level 90)
Hello, I’m testing the behavior described in the SQL Server documentation for **compatibility level 90** regarding the special attributes `xsi:nil` and `xsi:type`: > “The special attributes `xsi:nil` and `xsi:type` can't be queried or modified by data manipulation language statements. This means that `/e/@xsi:nil` fails while `/e/@*` ignores the `xsi:nil` and `xsi:type` attributes. However, `/e` returns the `xsi:nil` and `xsi:type` attributes for consistency with `SELECT xmlCol`, even if `xsi:nil = "false"`. ” But on **SQL Server 2005**, I can successfully query `@xsi:nil` and it returns the expected value. I’m trying to reproduce the documented “`/e/@xsi:nil` fails” behavior, but I can’t. ### Environment - Product: **Microsoft SQL Server 2005** - Database compatibility level: **90** --- ## ✅ Repro script ```sql IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MyTestSchema') DROP XML SCHEMA COLLECTION MyTestSchema; GO CREATE XML SCHEMA COLLECTION MyTestSchema AS N' <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence> <xsd:element name="element" nillable="true" type="xsd:string" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>'; GO DECLARE @xmlData XML(MyTestSchema) = N' <root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <element xsi:nil="true" /> </root>'; ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as xsi) SELECT @xmlData.query('<result> { /root/element/@xsi:nil } </result>') AS Typed_Result; ``` ### Actual result `Typed_Result` contains `xsi:nil="true"` under `<result>...`. ### Expected result (based on docs) I expected `/root/element/@xsi:nil` to fail, or not return `xsi:nil`. --- ## Questions 1. In the documentation, does “data manipulation language statements” mean only **XML DML** (i.e., `.modify()`), not XQuery used in `SELECT` with `.query()` / `.value()`? 2. Does the “`/e/@xsi:nil` fails” behavior apply only when the XML is stored in a **table column**, not when using an **XML variable**? 3. Is the behavior different between **typed XML** (with an XML schema collection) vs **untyped XML**? 4. Can someone provide a minimal reproduction in SQL Server 2005 where `/e/@xsi:nil` fails as described? Thank you. ---Compat level 90: XML string-to-datetime UDF
Hello, I’m testing a behavior described in SQL Server documentation for **database compatibility level 90**. The docs state that a user-defined function that converts an XML constant string value to a SQL Server date/time type is marked as **deterministic**. On **SQL Server 2005**, I’m seeing the opposite: the function is marked as **non-deterministic** (`IsDeterministic = 0`). I’m trying to understand whether I’m missing a requirement/constraint or whether this is a doc mismatch / version-specific behavior. ### Environment - Product: **Microsoft SQL Server 2005** - Database compatibility level: **90** --- ## ✅ Repro script ```sql IF OBJECT_ID('dbo.fn_ParamXmlToDatetime', 'FN') IS NOT NULL DROP FUNCTION dbo.fn_ParamXmlToDatetime; GO CREATE FUNCTION dbo.fn_ParamXmlToDatetime (@xml XML) RETURNS DATETIME WITH SCHEMABINDING AS BEGIN DECLARE @y DATETIME; -- Convert an XML value to DATETIME SET @y = CONVERT(DATETIME, @xml.value('(/r)[1]', 'datetime')); RETURN @y; END GO SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fn_ParamXmlToDatetime'), 'IsDeterministic') AS IsDeterministic, OBJECTPROPERTY(OBJECT_ID('dbo.fn_ParamXmlToDatetime'), 'IsPrecise') AS IsPrecise; GO ``` ### Actual result `IsDeterministic = 0` (non-deterministic) ### Expected result (based on docs) `IsDeterministic = 1` (deterministic) for this pattern under compat level 90. --- ## Questions 1. Are there additional conditions required for SQL Server to mark this UDF as deterministic (for example, specific XQuery usage, avoiding `CONVERT`, using `CAST`, using `datetime2` doesn’t exist in 2005, etc.)? 2. Does the determinism rule apply only when converting from an **XML literal constant** inside the function, rather than an XML parameter value? 3. Is this behavior different for **typed XML** (XML schema collections) vs **untyped XML**? 4. Is this a known difference/bug in SQL Server 2005 where the UDF is functionally deterministic but still reported as non-deterministic by `OBJECTPROPERTY`? Thank you for any clarification. ---